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
1 comments:
Click here for commentsGood steps and easy steps to follow
ConversionConversion EmoticonEmoticon