Saturday, August 25, 2012

Create a SQL Plan Baseline based on the good execution plan in AWR

We can create SQL plan baseline for a SQL to ensure the SQL execute with a good execution plan in 11g. The desired good execution plan sometimes can be obtained from AWR.

Today, a colleague sent team a note about the steps he adopted to create a SQL plan baseline for fixing a problem reporting query as follows:

1) -- create a SQL tuning set


2) -- load the SQL tuning set with the good execution plan from AWR

baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
open baseline_ref_cursor for
select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(70837, 71044,
'sql_id=' || CHR(39)||'8w0vxcj017b0m'||CHR(39)'  and plan_hash_value=2753253816',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;

DBMS_SQLTUNE.LOAD_SQLSET('test', baseline_ref_cursor);

3) -- Verify the good executon plan

SELECT * FROM table (

4) -- Verify the sql text in the sql tuing set

select *
from dba_sqlset_statements
where sqlset_name = 'test'
order by sql_id;

5) -- create the sql plan baseline from sql tuning set

set serveroutput on
my_integer pls_integer;

my_integer := dbms_spm.load_plans_from_sqlset (
sqlset_name => 'test',
sqlset_owner => 'Vxxxxx',
fixed => 'NO',
enabled => 'YES');

6) -- verify

select * from dba_sql_plan_baselines

update Aug. 26, 2013 -

In the latest sqlt tool utl directory, there is a script called  coe_load_sql_baseline.sql
, which can be used to create sql plan baseline for  a sql based on sqlid and plan hash value conveniently.