Execution Plan - Change SQL Plan Baselines
Problem Statement :
Long running query due to execution plan change.
--check long running query hash value and purge shared pool if needed
select ADDRESS, HASH_VALUE from GV$SQLAREA where SQL_ID like '19vh97709kcbz';
exec DBMS_SHARED_POOL.PURGE ('00000000CCFE9370,3231265151','C');
ALTER SYSTEM FLUSH SHARED_POOL;
-- Drop SQL Tuning Set (STS)
BEGIN
DBMS_SQLTUNE.DROP_SQLSET(
sqlset_name => 'MySTS01');
END;
-- Create SQL Tuning Set (STS)
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'MySTS01',
description => 'SQL Tuning Set for loading plan into SQL Plan Baseline');
END;
-- change sql id
-- SELECT SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME FROM dba_hist_snapshot ORDER BY END_INTERVAL_TIME DESC;
-- (Get the snap id/ time on better plan)
DECLARE
cur sys_refcursor;
BEGIN
OPEN cur FOR
SELECT VALUE(P)
FROM TABLE(
dbms_sqltune.select_workload_repository(begin_snap=>31343, end_snap=>31344,basic_filter=>'sql_id = ''19vh97709kcbz''',attribute_list=>'ALL')
) p;
DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'MySTS01', populate_cursor=>cur);
CLOSE cur;
END;
/
-- List out the Baselines to see what's there
SELECT * FROM dba_sql_plan_baselines ;
-- Load desired plan from STS as SQL Plan Baseline (in this case, plan 2694361211 is the plan we wanted)
-- Filter explicitly for the plan_hash_value here if you want
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
sqlset_name => 'MySTS01',
basic_filter=>'plan_hash_value = ''2694361211'''
);
END;
/
-- List out the Baselines
SELECT * FROM dba_sql_plan_baselines;
-- Rerun the query/job (Kill DB Session and restart appworx job if necessary) to take effect on forced sql execution plan.