Retrieve SQL and Execution Plan from AWR Snapshots

A handy little script that I have used in the Oracle Database 11g Performance Tuning course this week. It queries the AWR Snapshots for SQL statements and sorts them by elapsed time:

SQL> col parsed format a6
col sql_text format a40
set lines 200
set pages 300

 parsing_schema_name as parsed,
 elapsed_time_delta/1000/1000 as elapsed_sec,
 to_char(snap.end_interval_time,' hh24:mi:ss') as snaptime,
 dba_hist_sqlstat stat,
 dba_hist_sqltext txt,
 dba_hist_snapshot snap
 stat.sql_id=txt.sql_id and
 stat.snap_id=snap.snap_id and
 snap.begin_interval_time>=sysdate-1 and
 lower(sql_text) like '%&t%' and
 parsing_schema_name not in ('SYS','SYSMAN','MDSYS','WKSYS')
order by elapsed_time_delta asc;

sysdate-1 returns SQL up to yesterday and can be modified to retrieve SQL as long as there are AWR snapshots of course. &t is a substring of the SQL, we may be interested in. With the sql_id, we can also retrieve the execution plan from the snapshots:

SQL> select plan_table_output from table (dbms_xplan.display_awr('&sqlid'));

The amount of SQL that gets collected into a snapshot can be modified with

SQL> begin
dbms_workload_repository.modify_snapshot_settings (

MAXIMUM (uppercase!) takes all SQL from the Library Cache into the snapshot – alternative, we can specify a number of Top SQL to collect. The example is from an 11g Database, but it should work the same with 10g already.

, ,

  1. #1 by Kumar on September 13, 2010 - 20:10

    Hi Uwe,
    Great postings, have read most of your blog and have enriched my knowledge.
    I had question on this posting (Retrieve SQL and Execution Plan from AWR Snapshots). In the query you have provided, what does column “elapsed_time_delta” have, as I understand it will have total elapsed time for all the executions in “execution_delta” column, for example if a query runs 100 times and execution of each run takes 2 microsecond, then I think “execution_delta” would show up as 100 and “elapsed_time_delta” would show up as 100 X 2 = 200 microseconds.
    Could you please shed some light on this.


  2. #2 by Uwe Hesse on September 20, 2010 - 10:16

    if you have a SQL statement with a short duration but with a high frequency, this will be shown as cumulated elapsed_sek (with one row for each snapshot, should the calling of the statement span multiple snapshots)
    BTW, I will change elapsed_sek to elapsed_sec (“second” is “Sekunde” in German) :-)

  3. #3 by Earl Shaffer on September 24, 2014 - 02:18

    contact me at my email address, i have a formatted ver of your script that i would like you to see

  4. #4 by Milford on May 12, 2015 - 11:29

    Thank you for some otherr informative blog. Where else could I
    am getting that kijnd of innfo written in such
    an ideal manner? I have a project that I’m simply now operating on, and I’ve been at the look out
    for ssuch info.

  5. #5 by Eric Hu on November 11, 2015 - 14:17

    Hello Uwe,
    Very informative blog. Many thanks.
    So sql plan history is based on AWR snapshot. If topnsql = 30, then only up to top 30 SQL statements and their plans are stored in ever AWR snapshots. When a SQL jump to top 30 due to switching to suboptimal plan, we would not see its plan history – the good plan which keeps the SQL off top 30 list.. Is this correct?
    Is there any other safe way to get plan history?
    To get plan history via AWR, we need to set topnsql to Maximum and keep an appropriate retention period. What are concerns to set topnsql to a value other than maximum?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Get every new post delivered to your Inbox.

Join 3,735 other followers

%d bloggers like this: