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

exec DBMS_SQLTUNE.CREATE_SQLSET('test');

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

declare
baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
begin
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);
end;
/



3) -- Verify the good executon plan

SELECT * FROM table (
DBMS_XPLAN.DISPLAY_SQLSET(
'test','8w0vxcj017b0m'));


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
declare
my_integer pls_integer;

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



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.

Ref: SQLT (SQLTXPLAIN) - Tool that helps to diagnose a SQL statement performing poorly or one that produces wrong results (Doc ID 215187.1)