Applicate Baseline

Have you ever been in a situation in which some database queries
that used to behave well suddenly started performing poorly?
More likely than not, you traced the cause back to a change in the execution plan.
Further analysis may have revealed that the performance change was due to newly collected optimizer
statistics on the tables and indexes referred to in those queries.
One of the many method to secure the execution plan is to use a baseline
Below we will explain how to set an execution plan with a baseline having already ‘the optimal plan in memory or having
take it from AWR
Below the technical details:
ASSOCIATION BASELINE Plan valid execution and memory
The plans are valid associated with those in the v$sqlarea:
1) Check any existing baseline for sql_id under review
SELECT sql_handle, plan_name FROM dba_sql_plan_baselines WHERE signature IN ( SELECT exact_matching_signature FROM v$sql WHERE sql_id=’&sql_id’) ;
2) Check plan execution:
set lines 222
select INST_ID,sql_id,plan_hash_value,ELAPSED_TIME/executions, executions,SQL_PLAN_BASELINE,FIRST_LOAD_TIME,LAST_ACTIVE_TIME
from gv$sql where sql_id in (‘&sql_id’) order by 2,8 desc ;
3) association plan
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id => ‘&sql_id’,
PLAN_HASH_VALUE =>&pianoEsecuzione,
FIXED =>’YES’);
dbms_output.put_line(‘Value is ‘||my_plans);
END;
/
4) Check associated plan and actual use (the query must have a new execution in v $ sql)
select s.sql_id, b.plan_name, b.origin, b.accepted, b.enabled, b.fixed
from dba_sql_plan_baselines b, v$sql s
where s.exact_matching_signature = b.signature and s.sql_plan_baseline = b.plan_name and s.sql_id=’&sql_id’;
select INST_ID,sql_id,plan_hash_value,ELAPSED_TIME/executions, executions,SQL_PLAN_BASELINE,FIRST_LOAD_TIME,LAST_ACTIVE_TIME
from gv$sql where sql_id in (‘&sql_id’) order by 2,8 desc ;
ASSOCIATION BASELINE Plan running from AWR
1) Check any existing baseline for sql_id under review
SELECT sql_handle, plan_name FROM dba_sql_plan_baselines
WHERE signature IN ( SELECT exact_matching_signature FROM v$sql WHERE sql_id=’&sql_id’) ;
2) Locate the query with change of plan and mark the snap_id where is the optimal plan
set pages 500
set lines 220
select SNAP_ID,SQL_ID,PLAN_HASH_VALUE,OPTIMIZER_COST,SQL_PROFILE from DBA_HIST_SQLSTAT where sql_id = ‘&sql_id’ order by snap_id;
3) Create SQLSET
exec dbms_sqltune.create_sqlset(sqlset_name => ‘&sql_id’||’_sqlset’,description => ‘Query baseline’);
4) assignment SQLSET
declare
baseline_ref_cur DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cur for
select VALUE(p) from table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(&snap_id_inizio, &snap_id_fine,’sql_id=’||CHR(39)||’&sql_id’||CHR(39)||”,NULL,NULL,NULL,NULL,NULL,NULL,’ALL’)) p;
DBMS_SQLTUNE.LOAD_SQLSET(‘&sql_id’||’_sqlset’, baseline_ref_cur);
end;
/
5) SQLSET verification and access plan related
SELECT NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET where name=’&sql_id’||’_sqlset’;
select * from table(dbms_xplan.display_sqlset(‘&sql_id’||’_sqlset’,’&sql_id’));
6) assignment baseline
set serveroutput on
declare
my_int pls_integer;
begin
my_int := dbms_spm.load_plans_from_sqlset (
sqlset_name => ‘&sql_id’||’_sqlset’,
sqlset_owner => ‘SYS’,
fixed => ‘YES’,
enabled => ‘YES’);
DBMS_OUTPUT.PUT_line(my_int);
end;
/
7) Check associated plan and actual use (the query must have a new execution in v $ sql)
select s.sql_id, b.plan_name, b.origin, b.accepted, b.enabled, b.fixed
from dba_sql_plan_baselines b, v$sql s
where s.exact_matching_signature = b.signature and s.sql_plan_baseline = b.plan_name and s.sql_id=’&sql_id’;
select INST_ID,sql_id,plan_hash_value,ELAPSED_TIME/executions, executions,SQL_PLAN_BASELINE,FIRST_LOAD_TIME,LAST_ACTIVE_TIME
from gv$sql where sql_id in (‘&sql_id’) order by 2,8 desc ;