Capture SQL PLAN BASELINE from AWR

Reference Metalink document :789888.1

Going through my Scripts i have found a very useful document today. thought it would be worth sharing online.

1- Create SQL Tuning Set 

exec DBMS_SQLTUNE.CREATE_SQLSET('4yjj6c21g246w_STS');

2- Load SQL Plan from AWR in STS

declare
baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cursor for
select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(46634, 46635,'sql_id='||CHR(39)||'4yjj6c21g246w'||CHR(39)||' and plan_hash_value=1171167410',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET('4yjj6c21g246w_STS', baseline_ref_cursor);
end;
/

3- the loaded plan details

Verify how many sqls got loaded in the STS.

SELECT NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET where name='4yjj6c21g246w_STS';


Verify the sql statements and its sql_id in the STS

select sql_id, substr(sql_text,1, 30) text
from dba_sqlset_statements
where sqlset_name = '4yjj6c21g246w_STS'
order by sql_id;

Verify the execution Plan of a SQL_ID in the STS for an user sql

SELECT * FROM table (DBMS_XPLAN.DISPLAY_SQLSET('4yjj6c21g246w_STS','4yjj6c21g246w'));

4- Load the the Plan in STS as a Baseline

set serveroutput on
declare
my_integer pls_integer;
begin
my_integer := dbms_spm.load_plans_from_sqlset (
sqlset_name => '4yjj6c21g246w_STS',
sqlset_owner => 'SYS',
fixed => 'NO',
enabled => 'YES',
basic_filter => 'parsing_schema_name != ''SYS''');
DBMS_OUTPUT.PUT_line(my_integer);
end;
/