Creating sql baseline to fix query with better execution plan

Hello Friends,

In this post we will discuss creating sql baseline and force query to use better execution plan (plan hash value).

steps to create and fix bad query by creating sql baseline are as below .

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: 5qw0w762nh6sq


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
---------- ---------- ---------- ------------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ---------- ----------
2020/04/30 09:00           12125 5qw0w762nh6sq 1822515825          0          0           0          0          0          0          0          0
2020/04/30 10:00           12126 5qw0w762nh6sq  876532320     831.34          5      166.27 37133923.6  143674.97    8865.25    21041.4          0
2020/04/30 10:00           12126 5qw0w762nh6sq 1112518784        .15          6         .02       37.5         14          0          0          6
2020/04/30 10:00           12126 5qw0w762nh6sq  876532320     642.13          3      214.04 44853062.7  180393.99   22285.81      43926          0
2020/04/30 11:00           12127 5qw0w762nh6sq 1112518784        .06          2         .03       66.5      31.17          0          0          2
2020/04/30 11:00           12127 5qw0w762nh6sq  876532320    1202.85          3      400.95  122548961  383634.29      26.69      32.33          1
2020/04/30 12:00           12128 5qw0w762nh6sq  876532320    6748.33         11      613.48  165143208  567928.28         54      54.55          9
2020/04/30 12:00           12128 5qw0w762nh6sq  876532320     900.09          1      900.09  139583053  668689.63  148167.04     376190          0
2020/04/30 12:00           12128 5qw0w762nh6sq 1112518784        .06         86           0      22.76        .37          0          0         85
2020/04/30 12:00           12128 5qw0w762nh6sq 1112518784        .08         56           0      25.86        1.2          0          0         55
2020/04/30 19:00           12135 5qw0w762nh6sq 2835273630        .01          1         .01        184       4.93          0          0          1

/*In this scenario sql_id=5qw0w762nh6sq and plan_hash_value for good plan that we want to force is 1112518784.*/

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_5qw0w762nh6sq');
END;

STEP 3: CREATE SQL TUNING SET

1112518784

BEGIN
  DBMS_SQLTUNE.create_sqlset (
    sqlset_name  => 'SQL_FOR_5qw0w762nh6sq',
    description  => 'SQL tuning set for 5qw0w762nh6sq');
END;
/

/* Populate STS from AWR by specifying snapshot for desired plan which we found using above query.

In this scenario snap id's are 12128 and 12135 and change plan_hash_value accordingly.*/


declare
baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cursor for
select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(12128,12135 ,'sql_id='||CHR(39)||'5qw0w762nh6sq'||CHR(39)||' and plan_hash_value=1112518784',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET('SQL_FOR_5qw0w762nh6sq', baseline_ref_cursor);
end;
/				  

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

STEP 4: CHECK SQL SET DETAILS


SQL> 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_5qw0w762nh6sq';SQL>   2

SQLSET_NAME
--------------------------------------------------------------------------------------------------------------------------------
SQLSET_OWNER                                                                                                                      SQLSET_ID SQL_ID      TEXT                 ELAPSED_TIME BUFFER_GETS
-------------------------------------------------------------------------------------------------------------------------------- ---------- ------------- -------------------- ------------ -----------
PARSING_SCHEMA_NAME                                                                                                              PLAN_HASH_VALUE
-------------------------------------------------------------------------------------------------------------------------------- ---------------
BIND_DATA
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_FOR_5qw0w762nh6sq
SYS                                                                                                                                   10 5qw0w762nh6sq select om.order_move             0           0
GLODDBA                                                                                                                               1112518784
BEDA0C1002005EAAE52E000201F0018003671655504D2E434E545F32383036333637315F312D303032

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


SQL> DECLARE
  L_PLANS_LOADED  PLS_INTEGER;
BEGIN
  L_PLANS_LOADED := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
    SQLSET_NAME => 'SQL_FOR_5qw0w762nh6sq');
END;  2    3    4    5    6
  7  /

PL/SQL procedure successfully completed.

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='cv6zspbpkzzka')
order by accepted,enabled;

STEP 7: ENABLE FIXED=YES


SQL> var pbsts varchar2(30);
exec :pbsts := dbms_spm.alter_sql_plan_baseline('SQL_4d584d664a2534a4','SQL_PLAN_4uq2dct52ad541a576a2a','FIXED','YES');

PL/SQL procedure successfully completed.

STEP 8: PURGE OLD EXECUTION PLAN FROM SHARED POOL

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


SQL> select address||','||hash_value from v$sqlarea where sql_id = '5qw0w762nh6sq';

ADDRESS||','||HASH_VALUE
----------------------------------------------------------------------------------------
000000112B1AC2A8,2236095254

Now use below command to purge sql from shared pool.


exec sys.dbms_shared_pool.purge('0000001102EFFF18,2236095254','S',1);
exec sys.dbms_shared_pool.purge('0000001102EFFF18,2236095254','C',1);

Hope This Helps

Regards

Sultan Khan

Previous
Next Post »

1 comments:

Click here for comments
kumud gupta
admin
January 5, 2021 at 10:58 PM ×

Good steps and easy steps to follow

Congrats bro kumud gupta you got PERTAMAX...! hehehehe...
Reply
avatar