12C sql baseline - loading sql plan from AWR

When a comparatively slow performance of particular query/process is observed, one of the first things to do is to check for any plan changes. Below are the steps taken when a plan change was identified and rectified by loading sql plan from AWR via SQL Tuning Set.( STS).

Check whether a plan change is impacting the performance for a given sql_id in question,

 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 = '<<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;  

Below is a scenario where Oracle is using a bad plan, with higher execution time and cost. With plan 4233095222, average execution time was ~0.02 but with new changed plan 4086530757, the time was ~4.7.

When loading a better plan using SPM (SQL Plan Management), two options are available,

1.       Load the plan from cursor cache based on availability
2.       Load the plan from AWR

In the above example plan is only available from AWR, if it was available on cursor cache it is possible to load the same using DBMS_SPM.load_plans_from_cursor_cache, when loading from AWR first it is required to create a STS and then load from the AWR.

Create STS. for this example sql_id in question is dqhsq0guy3pc0,

 BEGIN  
  DBMS_SQLTUNE.CREATE_SQLSET (  
     sqlset_name  => 'STS_dqhsq0guy3pc0',  
     description  => 'SQL Tuning Set for EOD Jun19');  
     END;  

Filter the sql_id in question for  the STS, get the AWR snap id containing the better plan from initial query used,

 DECLARE  
  cur sys_refcursor;  
 BEGIN  
  OPEN cur FOR  
   SELECT VALUE(P)  
   FROM TABLE(   dbms_sqltune.select_workload_repository(begin_snap=>5901,end_snap=>5903,basic_filter=>'sql_id = ''dqhsq0guy3pc0''',attribute_list=>'ALL')  
        ) p;  
    DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'STS_dqhsq0guy3pc0', populate_cursor=>cur);  
  CLOSE cur;  
 END;  

Query the STS to list available plans using below query,

 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_dqhsq0guy3pc0')  
   );  

Above query should list the better plan with hash 4233095222. As the final step load the better plan from STS,

 DECLARE  
 my_plans pls_integer;  
 BEGIN  
  my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(  
   sqlset_name => 'STS_dqhsq0guy3pc0',   
   basic_filter=>'plan_hash_value = ''4233095222'''  
   );  
 END;