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.

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,288 other followers

%d bloggers like this: