Query is picking a bad execution plan , fix it using Oracle baselines ?
4 min readThe goal of SQL plan baselines is to preserve the performance of corresponding SQL statements, regardless of changes in the database. Examples of changes include:
- New optimizer version
- Changes to optimizer statistics and optimizer parameters
- Changes to schema and metadata definitions
- Changes to system settings
- SQL profile creation
SQL plan baselines cannot help in cases where an event has caused irreversible execution plan changes, such as dropping an index.
FIRST SELECT THE BEST PLAN FROM OEM - SQL SEARCH WITH SQLID
(or)
script sql.sql
set lines 180 pages 500
col sql_id format a14
col sql_plan_baseline format a30
col plan_hash_value format 999999999999999
col exact_matching_signature format 99999999999999999999
col sql_text format a50
select sql_id,
plan_hash_value,
sql_plan_baseline,
executions,
elapsed_time,
exact_matching_signature,
substr(sql_text,0,50) sql_text
from v$sql
where parsing_schema_name != 'SYS'
and sql_text like '%_ACCESS%' ;
Drop SQL Tuning Set (STS)
BEGIN
DBMS_SQLTUNE.DROP_SQLSET(
sqlset_name => '0md2pnra5rrj4q_sts01');
END;
/
List distinct plan hash value (PHV) for the sql_id
select distinct plan_hash_value,sql_id from dba_hist_sqlstat where sql_id='0md2pnra5rrj4q';
Create STS(SQL Tuning Set)
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => '0md2pnra5rrj4q_sts01',
description => 'SQL Tuning Set for loading plan into SQL Plan Baseline for sql_id 0md2pnra5rrj4q');
END;
/
List STS
set linesize 300
col owner for a15
col name for a30
select owner,name,created,statement_count from dba_sqlset where name='0md2pnra5rrj4q_sts01';
--Check SQL Monitor is enable for SQL stmts
select DBMS_SQLTUNE.REPORT_SQL_MONITOR(type => 'TEXT', report_level => 'ALL' , sql_id => '0md2pnra5rrj4q') as report from dual;
Populate STS from Awr Snapshots
Retrieve the begin Snap ID from the same session described in the GRID Contol above or by
----Get snapshot Ids
set linesize 300
col BEGIN_INTERVAL_TIME for a30
col END_INTERVAL_TIME for a30
select min(SNAP_ID) from dba_hist_snapshot;
select max(SNAP_ID) from dba_hist_snapshot;
SELECT SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME FROM dba_hist_snapshot ORDER BY END_INTERVAL_TIME DESC;
--Populate
DECLARE
cur sys_refcursor;
BEGIN
OPEN cur FOR SELECT VALUE(P) FROM TABLE(dbms_sqltune.select_workload_repository(begin_snap=>1477, end_snap=>1478,basic_filter=>'sql_id = ''9n82zq1gkpg2t''',attribute_list=>'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> '0md2pnra5rrj4q_sts01', populate_cursor=>cur);
CLOSE cur;
END;
/
List out SQL Tuning Set contents to check we got what we wanted(query sts to verify contents)
SELECT first_load_time,executions as execs,parsing_schema_name,elapsed_time / 1000000 as elapsed_time_secs,cpu_time / 1000000 as cpu_time_secs,buffer_gets,disk_reads,direct_writes,rows_processed,fetches,optimizer_cost,sql_plan,plan_hash_value,sql_id,sql_text
FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => '0md2pnra5rrj4q_sts01'));
List current baselines
select * from dba_sql_plan_baselines;
Load the desired plan from STS as BASELINES(Finally create the baseline from the STS:)
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
sqlset_name => '0md2pnra5rrj4q_sts01',
basic_filter=>'plan_hash_value = ''1117073691'''
);
END;
/
Verify the baseline got created and enable plan
select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines;
declare
sts pls_integer;
begin
sts := dbms_spm.ALTER_SQL_PLAN_BASELINE (sql_handle =>'SQL_ab2ab5c194ee0fc8',plan_name => 'SQL_PLAN_aqapps6afw3y81722054c', attribute_name=> 'FIXED', attribute_value => 'YES')
end;
/
List current baselines again
select signature,sql_handle,sql_text,plan_name,creator origin,parsing_schema_name,version,created from dba_sql_plan_baselines order by created;
select sql_handle,plan_name,origin,enabled,accepted,fixed,module from dba_sql_baselines;
select sql_id,plan_hash_value,to_char(exact_matching_signature),sql_plan_baseline from v$sql where sql_text like 'select distinct to_char(PARTY_%);
select sql_text,optimizer_cost,accepted from dba_sql_plan_baselines where to_char(sql_text) like 'select distinct to_char(PARTY_%);
FLUSH CURRENT BAD SQL Plan
set linesize 300
select address, hash_value,old_hash_value,plan_hash_value from v$sqlarea where sql_id='&sql_id';
/
exec dbms_shared_pool.purge('&address,&hash_value','C');
NOW WE CAN SEE THE NEW HASH VALUE SET FROM PLAN BASELINES
set linesize 300
select address, hash_value,old_hash_value,plan_hash_value from v$sqlarea where sql_id='&sql_id';
