How to select statements from AWR?

Simple question – and more a reminder to myself as I’d assume that many people have their examples somewhere already. But in upgrade and migration projects it’s sometime useful to simply select SQL statements directly from between two AWR snapshots into a SQL Tuning Set (STS). This will give you the possibility to later put a failing plan directly into a SQL Plan Baseline, part of the free Enterprise Edition’s SQL Plan Management feature.

Please find a scripted example for download here:

You’ll need to create a SQL Tuning Set and simply fill it with staments from an AWR snapshot interval:

exec DBMS_SQLTUNE.CREATE_SQLSET('MD_STS','SYS')
/
DECLARE
 cur sys_refcursor;
 BEGIN
 open cur for
 select value(p) from table(dbms_sqltune.select_workload_repository(
       begin_snap => 4711,
 end_snap => 4788,
 basic_filter => 'parsing_schema_name not in
 (''DBSNMP'',''SYS'',''ORACLE_OCM'')',
 ranking_measure1 => 'elapsed_time',
 result_limit => 250)) p;
 dbms_sqltune.load_sqlset('MD_STS', cur);
 close cur;
 END;
 /

And fixing a failing plan and overwriting it with the plan from before the upgrade would work that way:

DECLARE
 my_plans PLS_INTEGER;
BEGIN
 my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
 sqlset_name => 'MD_STS',
 basic_filter => 'sql_id="b25h7qc53gowp"',
 fixed => 'YES');
END;
/

–Mike

Share this: