How To Stabilize the Execution plan using SQL PLAN Baseline

Each SQL associated with SQL_ID and based on the DBMS_STATS, the Optimizer chooses a execution plan identified with PLAN_HASH_VALUE. Hence, SQL_ID with multiple PLAN_HASH_VALUE often causes inconsistent performance.

How to fix this?

To start with, need to identify the optimal PLAN_HASH_VALUE for the SQL_ID.

Below Query give the history of the SQL_ID with PLAN_HASH_VALUE and execution time lines to choose the optimal PLAN_HASH_VALUE. Make a note of the instance number in case of RAC for the optimal PLAN_HASH_VALUE.

set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
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 = nvl('&sql_id','XXXXXXXXX')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
/

Before creating PLAN_BASELINE, check if the SQL_ID with optimal PLAN_HASH_VALUE is in cursor cache.
Need to query v$SQL : select inst_id ,SQL_ID,PLAN_HASH_VALUE from gv$SQL;
If the same optimal PLAN_HASH_VALUE identified from the above query, you can create Plan Baseline from the Cursor Cache using below Steps:
Loading SQL Plan Baseline from CURSOR Cache:

DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => ‘&SQL_ID’,PLAN_HASH_VALUE=>’&PLAN_HASH_VALUE’,fixed=>’YES’);
END;
/
commit;

Need to query dba_sql_plan_baselines, to check if the plan baseline created.
If the Optimal PLAN_HASH_VALUE does not exist in cursor cache, we need load it from AWR.
Loading SQL Plan Baseline from AWR

Step1: Need to create SQL tuning set name, I am choosing the name as TUNING_SET1 in this document.

exec dbms_sqltune.create_sqlset(sqlset_name => 'TUNING_SET1',description => 'sqlset descriptions');

Step2: Load the SQL from AWR to above Tuning SET name TUNING_SET1

declare
baseline_ref_cur DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cur for
select VALUE(p) from table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(&begin_snap_id, &end_snap_id,'sql_id='||CHR(39)||'&sql_id'||CHR(39)||'',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET(' TUNING_SET1', baseline_ref_cur);
end;
/
COMMIT;

Note: Input the SQL_ID and snapshotIDs identified from the first step .Run from the appropriate instance , where optimal PLAN_HASH_VALUE identified.
Ensure the SQL_SET:

SELECT NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET where name='TUNING_SET1';

Note: statement_count value in the above query should be 1
To ensure right PLAN_HASH_VALUE is there in Tuning set:

set long 2000000
select * from table(dbms_xplan.display_sqlset('TUNING_SET1','&sql_id'));

Step3 : Load SQL Plan baseline from above tuning set.

declare
my_int pls_integer;
begin
my_int := dbms_spm.load_plans_from_sqlset (
sqlset_name => 'TUNING_SET1',
sqlset_owner => 'SYS',
fixed => 'YES',
enabled => 'YES');
DBMS_OUTPUT.PUT_line(my_int);
end;
/
commit;

Query dba_sql_plan_baselines , to check if the plan baseline created.