Re: DBA_HIST_SQLSTAT
Date: Tue, 20 Nov 2012 09:24:07 -0800 (PST)
Message-ID: <1353432247.32566.YahooMailNeo_at_web121602.mail.ne1.yahoo.com>
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) swhere 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> _at_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_at_parallon.net" <Christopher.Taylor2_at_parallon.net> To: oracle-l_at_freelists.org
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_at_parallon.net<mailto:christopher.taylor2_at_parallon.net>
http://www.parallon.net/
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Tue Nov 20 2012 - 18:24:07 CET