Creating sql baseline to fix query with better execution plan

            

Hello everyone, hope you all doing good. It’s been too long I haven’t updated new post on my blog due to my busy schedule.

So here I will be sharing very interesting post of creating sql baseline and force query to use better execution plan (plan hash value).

Most of us have come across this scenario where query which was running fine till yesterday now suddenly running long.

So there could be many reasons why query performance has been changed suddenly, so one of the reason is change in query plan.

In such situation we need to find out best execution plan (Plan_hash_value) and force query to use that plan.

Below are the steps to create and fix bad query 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: 7hgwdax4mn20v

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

---------- ---------- ---------- ------------- ---------- ---------- ------------ ----------- -------------- ---------- ---------- ---------- ----------

2016/09/13 11:00           23468 7hgwdax4mn20v 2844841640    1421.36            1     1421.36        4,301.0  152720.78     312.25        269        336

2016/09/13 15:00           23472 7hgwdax4mn20v 2844841640    1070.12            1     1070.12        4,291.0  126987.69     348.34        270        336

2016/09/13 17:00           23474 7hgwdax4mn20v 2844841640    1211.98            1     1211.98        4,374.0  183347.13     157.32        270        336

2016/09/13 18:00           23475 7hgwdax4mn20v 2844841640     596.91            0      596.91          116.0   45123.14          0          0          0

2016/09/14 01:00           23482 7hgwdax4mn20v 2844841640    1038.41            1     1038.41        4,309.0  123455.23     413.94        272        336

2016/09/14 02:00           23483 7hgwdax4mn20v 1355798266     193.82            1      193.82        2,748.0   76799.32      28.95         16        168

2016/09/14 03:00           23484 7hgwdax4mn20v 2844841640        .04            0         .04             .0         38          0          0          0

2016/09/14 03:00           23484 7hgwdax4mn20v          0        .04            0         .04             .0      38.99          0          0          0

2016/09/14 10:00           23491 7hgwdax4mn20v 2844841640     626.06            0      626.06           64.0   54658.69          0          0          0

/*In this scenario sql_id=7hgwdax4mn20v and plan_hash_value for good plan that we want to force is 1355798266.*/

Follow the 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_7hgwdax4mn20v');

END;

STEP 3: CREATE SQL TUNING SET

BEGIN

  DBMS_SQLTUNE.create_sqlset (

    sqlset_name  => 'SQL_FOR_7hgwdax4mn20v',

    description  => 'SQL tuning set for 7hgwdax4mn20v');

END;

/

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

In this scenario snap id's are 23483 and 23484 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 ('7hgwdax4mn20v') and plan_hash_value in (1355798266)>',  -- 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_7hgwdax4mn20v',

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

STEP 5: LOAD THE 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_7hgwdax4mn20v');

END;

STEP 6: CHECK SQL PLAN BASELINE INFORMATION

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: Check the SQL baseline details

Use the plan name received from the STEP 6 output to get the baseline details

SELECT * FROM TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SQL_PLAN_69tvffwy5bwf480a6275e'));

STEP 9: PURGE OLD EXECUTION PLAN FROM SHARED POOL 

Find below two parameters required to purge specific sql from the shared pool.

select address||','||hash_value from gv$sqlarea where sql_id = '7hgwdax4mn20v';

ADDRESS||','||HASH_VALUE

----------------------------------------------------------------------------------------

000000058B4B7E40,1879818331

Now use the below command to purge sql from the shared pool.

exec sys.dbms_shared_pool.purge('000000058B4B7E40,1879818331','C',1);

                

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

Thank you,
Amit Pawar
Email: amitpawar.dba@gmail.com
WhatsApp No: +91-8454841011