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
https://www.funoracleapps.com/2021/07/how-i-create-sql-baseline-to-fix-query.html
