Oracle SQLPLAN Baseline Example

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);