How I Create SQL Baseline to fix query with an Execution Plan

            

I am sharing on creating SQL baseline and force query to use better execution plan (plan hash value). 
I was doing today so thought to share this scenario where the query which was running fine till yesterday now suddenly running long in my EBS instance. So very important  I check the plan for a particular sqlid. 

Note my DB version in 11.2.0.4

Query to check the multiple hash plans for sql id.

select
SQL_ID
, PLAN_HASH_VALUE
, sum(EXECUTIONS_DELTA) EXECUTIONS
, sum(ROWS_PROCESSED_DELTA) CROWS
, trunc(sum(CPU_TIME_DELTA)/1000000/60) CPU_MINS
, trunc(sum(ELAPSED_TIME_DELTA)/1000000/60)  ELA_MINS
from DBA_HIST_SQLSTAT
where SQL_ID in (
'6hxw283cyw0ub') --repalce sqlid with your sqlid
group by SQL_ID , PLAN_HASH_VALUE
order by SQL_ID, CPU_MINS

I find out the best execution plan (Plan_hash_value) and force the query to use that plan. Below are the steps I did to create and fix bad queries by creating SQL baseline.



STEP 1: GENERATE ALL PREVIOUS HISTORY RUN DETAILS OF SQL_ID FROM AWR 

break off sdate
set lines 2000
set linesize 2000
col SDATE format a10
col STIME format a10
select to_char(begin_interval_time,'YYYY/MM/DD') SDATE,to_char(begin_interval_time,'HH24:MI')  STIME,s.snap_id,
        sql_id, plan_hash_value PLAN,
        ROUND(elapsed_time_delta/1000000,2) ET_SECS,
        nvl(executions_delta,0) execs,
        ROUND((elapsed_time_delta/decode(executions_delta,null,1,0,1,executions_delta))/1000000,2) ET_PER_EXEC,
        ROUND((buffer_gets_delta/decode(executions_delta,null,1,0,1,executions_delta)), 2) avg_lio,
        ROUND((CPU_TIME_DELTA/decode(executions_delta,null,1,0,1,executions_delta))/1000, 2) avg_cpu_ms,
        ROUND((IOWAIT_DELTA/decode(executions_delta,null,1,0,1,executions_delta))/1000, 2) avg_iow_ms,
        ROUND((DISK_READS_DELTA/decode(executions_delta,null,1,0,1,executions_delta)), 2) avg_pio,
        ROWS_PROCESSED_DELTA num_rows
from DBA_HIST_SQLSTAT S,  DBA_HIST_SNAPSHOT SS
where s.sql_id = '&sql_id'
and ss.snap_id  =S.snap_id
and ss.instance_number = S.instance_number
order by sdate,stime;

Enter value for sql_id: 6hxw283cyw0ub



SDATE      STIME    SNAP_ID SQL_ID              PLAN    ET_SECS      EXECS ET_PER_EXEC    AVG_LIO AVG_CPU_MS AVG_IOW_MS    AVG_PIO   NUM_ROWS
---------- ----- ---------- ------------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ---------- ----------
2021/06/30 19:00      89668 6hxw283cyw0ub 1368916072          0          0           0          0          0          0          0          0
2021/06/30 19:00      89668 6hxw283cyw0ub   40732332     509.74          1      509.74  167517768  284054.61    4066.39     979242          0
2021/06/30 20:00      89669 6hxw283cyw0ub 1368916072          0          0           0          0          0          0          0          0
2021/06/30 20:00      89669 6hxw283cyw0ub   40732332     488.65          1      488.65  167171014  266845.92    7141.55     789809          0
2021/07/01 07:00      89680 6hxw283cyw0ub   40732332     508.51          1      508.51  165308543  265980.54    3566.88     726343          0
2021/07/01 07:00      89680 6hxw283cyw0ub 1368916072          0          0           0          0          0          0          0          0
2021/07/01 08:00      89681 6hxw283cyw0ub   40732332     936.17          2      468.09  165328375  257197.28    3183.58   726323.5          0
2021/07/01 08:00      89681 6hxw283cyw0ub 1368916072          0          0           0          0          0          0          0          0
2021/07/01 09:00      89682 6hxw283cyw0ub   40732332     558.38          1      558.38  165739035  269044.73    5430.59     733766          0
2021/07/01 09:00      89682 6hxw283cyw0ub 1368916072          0          0           0          0          0          0          0          0
2021/07/01 11:00      89684 6hxw283cyw0ub   40732332    1553.27          3      517.76  167407756  266781.05    4228.16  823571.33          0
2021/07/01 15:00      89688 6hxw283cyw0ub   95728747     120.19          1      120.19    3052872   31220.28   10460.06     969074       8738
2021/07/01 15:00      89688 6hxw283cyw0ub  587292616     1372.1          3      457.37 97773871.7  199067.35    1862.32 1019655.67          0


/*In this scenario sql_id=>6hxw283cyw0ub and plan_hash_value for good plan that we want to force is 95728747.*/
Follow below steps to create sql baseline for sql_id

STEP 2: DROP SQL TUNING SET (STS) IF EXISTS

BEGIN
  DBMS_SQLTUNE.DROP_SQLSET(
    sqlset_name > 'SQL_FOR_6hxw283cyw0ub');
END;

STEP 3: CREATE SQL TUNING SET 

BEGIN
  DBMS_SQLTUNE.create_sqlset (
    sqlset_name  > 'SQL_FOR_6hxw283cyw0ub',
    description  > 'SQL tuning set for 6hxw283cyw0ub');
END;
/

/* Populate STS from AWR by specifying snapshot for a desired plan which we found using the above query.
In this scenario snap id's are 89684 and 89688 and change plan_hash_value accordingly.*/

DECLARE
  l_cursor  DBMS_SQLTUNE.sqlset_cursor;
BEGIN
  OPEN l_cursor FOR
    SELECT VALUE(p)
    FROM   TABLE (DBMS_SQLTUNE.select_workload_repository (
                    23483,  -- begin_snap
                    23484,  -- end_snap
                    q'<sql_id in ('6hxw283cyw0ub') and plan_hash_value in (95728747)>',  -- basic_filter 
                    NULL, -- object_filter
                    NULL, -- ranking_measure1
                    NULL, -- ranking_measure2
                    NULL, -- ranking_measure3
                    NULL, -- result_percentage
                    100)   -- result_limit
                  ) p;

  DBMS_SQLTUNE.load_sqlset (
    sqlset_name     > 'SQL_FOR_6hxw283cyw0ub',
    populate_cursor > l_cursor);
END;
/

STEP 4: CHECK SQL SET DETAILS 

column text format a20
select sqlset_name, sqlset_owner, sqlset_id, sql_id,substr(sql_text,1,20) text,elapsed_time,buffer_gets,
parsing_schema_name, plan_hash_value, bind_data from dba_sqlset_statements where sqlset_name ='SQL_FOR_6hxw283cyw0ub';

STEP 5: LOAD DESIRED PLAN FROM STS AS SQL PLAN BASELINE

DECLARE
  L_PLANS_LOADED  PLS_INTEGER;
BEGIN
  L_PLANS_LOADED : DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
    SQLSET_NAME > 'SQL_FOR_6hxw283cyw0ub');
END;

STEP 6: CHECK SQL PLAN BASELINE DETAILS 

SELECT sql_handle, plan_name,enabled,accepted,fixed FROM dba_sql_plan_baselines
WHERE signature IN (SELECT exact_matching_signature FROM v$sql WHERE sql_id='&SQL_ID')
order by accepted,enabled;

STEP 7: ENABLE FIXED=YES

var pbsts varchar2(30);
exec :pbsts : dbms_spm.alter_sql_plan_baseline('SQL_64e76e773c55f1c4','SQL_PLAN_69tvffwy5bwf480a6275e','FIXED','YES');

STEP 8: PURGE OLD EXECUTION PLAN FROM SHARED POOL 

Find below two parameter which are required to purge specific sql from the shared pool.

select address||','||hash_value from gv$sqlarea where sql_id ='6hxw283cyw0ub';

ADDRESS||','||HASH_VALUE
----------------------------------------------------------------------------------------
00000001C966CDA0,3656254283

Now use below command to purge sql from shared pool.

exec sys.dbms_shared_pool.purge('00000001C966CDA0,3656254283','C',1);

Re-run query or program to test




If you like please follow and comment