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.
Pickleball spielen 
#1 von Kumar am 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 von Uwe Hesse am 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) 🙂
#3 von Earl Shaffer am 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 von Milford am Mai 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 von Eric Hu am 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?
#6 von Daniel am Mai 15, 2019 - 15:43
Good sql to chect hash_plan and elapse time without going through OEM