How To Stabilize the Execution plan using SQL PLAN Baseline

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.