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:

 cur sys_refcursor;
 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;
 dbms_sqltune.load_sqlset('MD_STS', cur);
 close cur;

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

 my_plans PLS_INTEGER;
 sqlset_name => 'MD_STS',
 basic_filter => 'sql_id="b25h7qc53gowp"',
 fixed => 'YES');


Share this:

One thought on “How to select statements from AWR?

  1. Pingback: Gather SQL Statements from AWR and Cursor Cache without need for Diag and Tuning Packs | Upgrade your Database - NOW!

Leave a Reply

Your email address will not be published. Required fields are marked *

* Checkbox to comply with GDPR is required


I agree

This site uses Akismet to reduce spam. Learn how your comment data is processed.