That looks close - I'll take a look and see if I can extend it a little bit. I'll want the number of executions as well because I don't really care about sql's that execute once or twice (think data cleanup for example executed by an application user). I'm targeting SQL that runs as part of nightly batch that gets executed multiple times. Chris From: David Fitzjarrell [mailto:oratune@xxxxxxxxx] Sent: Tuesday, November 20, 2012 11:24 AM To: Taylor Christopher - Nashville; oracle-l@xxxxxxxxxxxxx Subject: Re: DBA_HIST_SQLSTAT They don't, actually. The executions_total is the total executions since the database was started, the elapsed_time_total is the total since the db was started and the deltas are for each snapshot and don't roll up. I came into a shop where a script was running to generate execution time reports for long-running queries but it didn't take into account that the deltas don't roll up. I rewrote it to provide total execution times across snapshots for a given sql_id: set linesize 200 trimspool on pagesize 60 verify off column begin_interval_time format a35 column end_interval_time format a35 break on sql_id skip 1 on instance_number column sdate new_value sdt noprint select to_char(sysdate, 'YYYYMMDDHHMI') sdate from dual; spool &sdt._elapsed_time_report.log prompt prompt Historic prompt prompt Elapsed by exec prompt select distinct x.instance_number, x.sql_id, x.time_per_exec, x.elapsed_time_total, s.begin_interval_time, s.end_interval_time from (select instance_number, sql_id, snap_id, round((elapsed_time_total/1000000)/(case when executions_total = 0 then 1 else executions_total end),4) time_per_exec, round(elapsed_time_total/1000000, 4) elapsed_time_total from dba_hist_sqlstat) x, (select snap_id, max(begin_interval_time) begin_interval_time, max(end_interval_time) end_interval_time from dba_hist_snapshot group by snap_id) s where s.snap_id = x.snap_id and x.time_per_exec > &&1 and x.time_per_exec <> x.elapsed_time_total order by 2 asc, 3 desc, 6 desc / clear breaks prompt prompt Elapsed time total prompt select inst_id, sql_id, executions, round(elapsed_time/1000000, 6) elapsed_sec, round((elapsed_time/1000000)/(case when executions = 0 then 1 else executions end), 6) elapsed_per_exec, last_active_time from gv$sqlstats where elapsed_time/1000000 > &&1 order by 4 desc / prompt prompt Elapsed per exec prompt select inst_id, sql_id, executions, round(elapsed_time/1000000, 6) elapsed_sec, round((elapsed_time/1000000)/(case when executions = 0 then 1 else executions end), 6) elapsed_per_exec, last_active_time from gv$sqlstats where elapsed_time/1000000 > &&1 order by 5 desc / spool off Save this as a .sql script and call it with a threshold time, in seconds. I saved it as query_elapsed_time.sql so I'd call it this way: SQL> @query_elapsed_time 75 Historic Elapsed by exec INSTANCE_NUMBER SQL_ID TIME_PER_EXEC ELAPSED_TIME_TOTAL BEGIN_INTERVAL_TIME END_INTERVAL_TIME --------------- ------------- ------------- ------------------ ----------------------------------- ----------------------------------- 1 17t3mszqk0f2u 277.7504 7221.5108 06-NOV-12 03.00.50.446 PM 06-NOV-12 03.15.55.717 PM 276.3577 6908.9425 06-NOV-12 02.45.44.149 PM 06-NOV-12 03.00.50.446 PM 239.702 5992.5493 06-NOV-12 02.30.37.302 PM 06-NOV-12 02.45.44.149 PM 204.5009 5112.5234 06-NOV-12 02.15.30.944 PM 06-NOV-12 02.30.37.302 PM 174.3487 4184.3696 06-NOV-12 02.00.18.907 PM 06-NOV-12 02.15.30.944 PM 136.5016 3276.0373 06-NOV-12 01.45.14.738 PM 06-NOV-12 02.00.18.907 PM 103.2582 2374.9395 06-NOV-12 01.30.06.726 PM 06-NOV-12 01.45.14.738 PM 1 3ys2pqsfytmsu 112.4575 224.915 24-OCT-12 08.44.14.000 PM 24-OCT-12 08.55.13.735 PM 1 b6usrg82hwsa3 265.065 1060.2602 21-OCT-12 10.16.34.100 AM 21-OCT-12 10.30.17.299 AM 216.1319 1080.6596 21-OCT-12 02.15.42.109 PM 21-OCT-12 02.30.42.593 PM 187.9791 1127.8743 21-OCT-12 06.15.07.062 PM 21-OCT-12 06.30.07.546 PM 179.5414 1256.79 21-OCT-12 10.15.58.750 PM 21-OCT-12 10.30.59.187 PM 12 rows selected. Elapsed time total no rows selected Elapsed per exec no rows selected SQL> Maybe this is what you're wanting. David Fitzjarrell From: "Christopher.Taylor2@xxxxxxxxxxxx<mailto:Christopher.Taylor2@xxxxxxxxxxxx>" <Christopher.Taylor2@xxxxxxxxxxxx<mailto:Christopher.Taylor2@xxxxxxxxxxxx>> To: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx> Sent: Tuesday, November 20, 2012 8:43 AM Subject: DBA_HIST_SQLSTAT Anyone done any data mining on this view? I'm trying to understand how the EXECUTIONS_TOTAL, EXECUTIONS_DELTA, ELAPSED_TIME_TOTAL, ELAPSED_TIME_DELTA roll up (if they do) from one snapshot to the other. I cannot determine if EXECUTIONS_TOTAL for a SNAP_ID are the executions during that snapshot so that I would need to SUM the EXECUTIONS over multiple snapshots, or if I should take the executions within that snapshot independently as the total amount. For example: Snaps: 22995-22996 SQL_ID: c4pc3jhzjcmc7 Plan_Hash_Value: 5300452 22995: Executions Total = 2799, Executions Delta = 194, Elapsed Time Total = 530016, Elapsed Time Delta = 40284 22996: Executions Total = 2889, Executions Delta = 90, Elapsed Time Total = 544883, Elapsed Time Delta = 14867 From here I can take either the SUM of EXECUTIONS for both periods, or I can take just the last row (snap: 22996) but it would be nice to know if the executions are a rolling total or not. Anyone know? Chris Taylor Oracle DBA Parallon IT&S christopher.taylor2@xxxxxxxxxxxx<mailto:christopher.taylor2@xxxxxxxxxxxx><mailto:christopher.taylor2@xxxxxxxxxxxx<mailto:christopher.taylor2@xxxxxxxxxxxx>> http://www.parallon.net/ -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l