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.
#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 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)