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

select     
 sql_text,
 parsing_schema_name as parsed,
 elapsed_time_delta/1000/1000 as elapsed_sec,
 stat.snap_id,
 to_char(snap.end_interval_time,'dd.mm hh24:mi:ss') as snaptime,
 txt.sql_id
from     
 dba_hist_sqlstat stat,
 dba_hist_sqltext txt,
 dba_hist_snapshot snap
where     
 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 (
 topnsql=>'MAXIMUM');
end;
/

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.

About these ads

, ,

  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.

    Thanks
    Kumar

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

    Kumar,
    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) :-)

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

Follow

Get every new post delivered to your Inbox.

Join 2,747 other followers

%d bloggers like this: