How to select statements from AWR?
By Mike Dietrich-Oracle on Jul 16, 2012
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.
You'll need to create a SQL Tuning Set and simply fill it with staments from an AWR snapshot interval:
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
ranking_measure1 => 'elapsed_time',
result_limit => 250)) p;
And fixing a failing plan and overwriting it with the plan from before the upgrade would work that way:
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
sqlset_name => 'MD_STS',
basic_filter => 'sql_id="b25h7qc53gowp"',
fixed => 'YES');