1. Find out the SQL ID and Number of SQL Plan exist in AWR History.
SELECT SQL_ID, COUNT (*)
FROM (SELECT DISTINCT SQL_ID, PLAN_HASH_VALUE FROM DBA_HIST_SQL_PLAN
WHERE SQL_ID='&SQL_ID')
GROUP BY SQL_ID
ORDER BY 2 DESC;
SELECT SQL_ID, COUNT (*)
FROM (SELECT DISTINCT SQL_ID, PLAN_HASH_VALUE FROM DBA_HIST_SQL_PLAN
WHERE SQL_ID='&SQL_ID')
GROUP BY SQL_ID
ORDER BY 2 DESC;
2. Find out the SQL Plan Hash Value using SQL ID.
SELECT DISTINCT PLAN_HASH_VALUE,SQL_ID FROM DBA_HIST_SQLSTAT
WHERE SQL_ID='&SQL_ID';
3. Check the SQL Execution Plan Cost and choose the best one. Note that you have to select Hash Vale and SNAP_ID for which you want to load into your Plan Baseline.
SELECT SS.SNAP_ID,
SS.INSTANCE_NUMBER,
BEGIN_INTERVAL_TIME,
SQL_ID,
PLAN_HASH_VALUE,OPTIMIZER_COST,
DISK_READS_TOTAL,
BUFFER_GETS_TOTAL,
ROWS_PROCESSED_TOTAL,
CPU_TIME_TOTAL,
ELAPSED_TIME_TOTAL,
IOWAIT_TOTAL,
NVL (EXECUTIONS_DELTA, 0) EXECS,
( ELAPSED_TIME_DELTA
/ DECODE (NVL (EXECUTIONS_DELTA, 0), 0, 1, EXECUTIONS_DELTA))
/ 1000000
AVG_ETIME,
( BUFFER_GETS_DELTA
/ DECODE (NVL (BUFFER_GETS_DELTA, 0), 0, 1, EXECUTIONS_DELTA))
AVG_LIO
FROM DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
WHERE SQL_ID = '&SQL_ID'
AND SS.SNAP_ID = S.SNAP_ID
AND SS.INSTANCE_NUMBER = S.INSTANCE_NUMBER
AND EXECUTIONS_DELTA > 0
ORDER BY 1, 2, 3;
4. Check that STS has already existed or not in history if it is exist chose a different name of drop the existing one.
SELECT * FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => 'STS_f9948h4y2aa22'));
BEGIN
DBMS_SQLTUNE.DROP_SQLSET (sqlset_name => 'STS_f9948h4y2aa22');
END;
5. Create STS.
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'STS_f9948h4y2aa22',
description => 'SQL Tuning Set for loading plan into SQL Plan Baseline');
END;
6. Load STS
DECLARE
cur sys_refcursor;
BEGIN
OPEN cur FOR
SELECT VALUE(P)
FROM TABLE(
dbms_sqltune.select_workload_repository(begin_snap=>6450, end_snap=>6451,basic_filter=>'sql_id = ''f9948h4y2aa22''',attribute_list=>'ALL')
) p;
DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'STS_f9948h4y2aa22', populate_cursor=>cur);
CLOSE cur;
END;
7. Check the loaded Plan details.
SELECT
first_load_time ,
executions as execs ,
parsing_schema_name ,
elapsed_time / 1000000 as elapsed_time_secs ,
cpu_time / 1000000 as cpu_time_secs ,
buffer_gets ,
disk_reads ,
direct_writes ,
rows_processed ,
fetches ,
optimizer_cost ,
sql_plan ,
plan_hash_value ,
sql_id ,
sql_text
FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => 'STS_f9948h4y2aa22')
);
8. In final Stage Load SQL Plan from STS (AWR History) using plan Hash Value.
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
sqlset_name => 'STS_f9948h4y2aa22',
basic_filter=>'plan_hash_value = ''3166698470'''
);
END;
9. Check your loaded plan from baseline.
SELECT * FROM dba_sql_plan_baselines ;
