SQL ID Baseline
======STEP 1: CREATE SQL TUNING SET=====================================
BEGIN
DBMS_SQLTUNE.create_sqlset (
sqlset_name => 'SQL_FOR_<SQL_ID>',
description => 'SQL tuning set for <SQL_ID>');
END;
/
=========STEP 2: ADD SQL_ID, PLAN_HASH_VALUE to SQLSET=================
DECLARE
l_cursor DBMS_SQLTUNE.sqlset_cursor;
BEGIN
OPEN l_cursor FOR
SELECT VALUE(p)
FROM TABLE (DBMS_SQLTUNE.select_workload_repository (
37158, -- begin_snap
37157, -- end_snap
q'<sql_id in ('<SQL_ID>') and plan_hash_value in (<PLAN_VALUE>)>', -- 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_<SQL_ID>',
populate_cursor => l_cursor);
END;
/
=========STEP 3: 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_<SQL_ID>';
=========STEP 4: 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_<SQL_ID>');
END;
========STEP 5: 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 6: ENABLE FIXED=YES=======================================
you will get sql_handle and plan_name values from STEP 6 output
var pbsts varchar2(30);
exec :pbsts := dbms_spm.alter_sql_plan_baseline('&sql_handle','&plan_name','FIXED','YES');
Purging OLD Plans:
/* Flush shared pool of existing parsed representations to force CBO to use the baseline
Run this across all instances in a RAC cluster */
select inst_id, sql_id, child_number, plan_hash_value, executions, elapsed_time/1e6, round((elapsed_time/1e6)/decode(nvl(executions,0),0,1,executions),2) avg_etime from gv$sql where sql_id = '<SQL_ID>';
select distinct inst_id, address||','||hash_value from gv$sqlarea where sql_id like '<SQL_ID>';
/* Please note that you will have to connect to the individual instances to flush the cursor from the shared pool*/
DECLARE
l_name varchar2(50);
BEGIN
select distinct address||','||hash_value
into l_name
from v$sqlarea
where sql_id like '<SQL_ID>';
sys.dbms_shared_pool.purge(l_name,'C',1);
END;
/
/* Check whether baseline has kicked in. Display_awr will not show baselines as dba_hist views do not capture this info*/
select * from table(dbms_xplan.display_cursor('<SQL_ID>',<PLAN_VALUE>));
select s.inst_id, s.sql_id, s.child_number, s.plan_hash_value, s.sql_plan_baseline, s.executions, round(s.elapsed_time/1e6,2) "Ela in secs", round(s.elapsed_time/1e6/decode(s.executions,0,1,s.executions),2) "Avg Ela/exe in Secs" from gv$sql s, dba_sql_plan_baselines b where s.sql_plan_baseline = b.plan_name and sql_id = '<SQL_ID>';
select b.sql_handle, b.plan_name, s.child_number,
s.plan_hash_value, s.executions
from gv$sql s,
dba_sql_plan_baselines b
where s.exact_matching_signature = b.signature(+)
and s.sql_plan_baseline = b.plan_name(+)
and s.sql_id='<SQL_ID>';
select * from table(dbms_xplan.display_sql_plan_baseline(plan_name=>'<check plan_name column value'));
Comments
Post a Comment