Example on how to pin SQL using SQLPlan Baselines
1) Create a table
create table test1 as select * from dba_objects;
Table created.
2) Add index to table
create index test1_i on test1(object_id);
3) review explain plan
set autotrace on
select /*+ full(test1) */ * from test1 where object_id=2;
set autotrace off
Rerun SQL several times so it becomes eligible for AWR capture
4) get SQLID
select prev_sql_id from v$session where sid=sys_context('userenv','sid');
PREV_SQL_ID is cqx5uak6fvf06
select sql_text from v$sql where sql_id='cqx5uak6fvf06';
5) run AWR Snap
EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;
6) check for SQL in AWR
col BEGIN_INTERVAL_TIME for a32
set lines 200
set pages 1000
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = '&sql_id'
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
/
7) Create STS
exec dbms_sqltune.create_sqlset(sqlset_name => 'cqx5uak6fvf06_baseline1',description => 'sqlset for cqx5uak6fvf06');
8) Load STS
DECLARE
baseline_ref_cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
open baseline_ref_cur for
select VALUE(p) from table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(197, 198,'sql_id='||CHR(39)||'cqx5uak6fvf06'||CHR(39)||' and plan_hash_value=4122059633',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET('cqx5uak6fvf06_baseline1', baseline_ref_cur);
END;
/
9) Add Baseline
DECLARE
my_int pls_integer;
BEGIN
my_int := dbms_spm.load_plans_from_sqlset (sqlset_name => 'cqx5uak6fvf06_baseline1',
basic_filter => 'sql_id=''cqx5uak6fvf06''',
sqlset_owner => 'PSAMMY',
fixed => 'YES',
enabled => 'YES');
END;
/
Owner above is PSAMMY but will be different depending on user used to create sqlset
10) check baseline exists
select plan_name, ENABLED, ACCEPTED, FIXED from sys.dba_sql_plan_baselines
where CREATED >(sysdate-1);
11) purge plan from shared pool as sys
select sql_text||chr(10)|| 'exec dbms_shared_pool.purge ('||''''||address||','||hash_value||''''||','||''''||'C'||''''||');'
from v$sqlarea
where sql_id='cqx5uak6fvf06'
/
Run output as user SYS
12) Drop STS
exec dbms_sqltune.drop_sqlset(sqlset_name =>'cqx5uak6fvf06_baseline1');
13) Test baseline
select /*+ full(test1) */ * from test1 where object_id=2;
select sql_id, PLAN_HASH_VALUE, SQL_PLAN_BASELINE from v$sql where sql_id='cqx5uak6fvf06';
14) Disable baseline
select SQL_HANDLE, plan_name, ENABLED, ACCEPTED, FIXED from sys.dba_sql_plan_baselines where CREATED >(sysdate-1);
declare
myplan pls_integer;
begin
myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle => 'SQL_400c99a2a981ea81',plan_name => 'SQL_PLAN_4034tnans3un1fe026eff',attribute_name => 'ENABLED', attribute_value => 'NO');
end;
/
select SQL_HANDLE, plan_name, ENABLED, ACCEPTED, FIXED from sys.dba_sql_plan_baselines where CREATED >(sysdate-1);
15) drop baseline
select SQL_HANDLE, plan_name, ENABLED, ACCEPTED, FIXED from sys.dba_sql_plan_baselines where CREATED >(sysdate-1);
declare
drop_baseline pls_integer;
begin
drop_baseline := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
sql_handle => 'SQL_400c99a2a981ea81',
plan_name => 'SQL_PLAN_4034tnans3un1fe026eff');
dbms_output.put_line(drop_baseline);
end;
/
select SQL_HANDLE, plan_name, ENABLED, ACCEPTED, FIXED from sys.dba_sql_plan_baselines where CREATED >(sysdate-1);