Tuesday 16 May 2017

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