RE: DBA_HIST_SQLSTAT
Date: Tue, 20 Nov 2012 11:49:25 -0600
Message-ID: <F05D8DF1FB25F44085DB74CB916678E8856525F652_at_NADCWPMSGCMS10.hca.corpad.net>
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_at_yahoo.com] Sent: Tuesday, November 20, 2012 11:24 AM To: Taylor Christopher - Nashville; oracle-l_at_freelists.org 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> _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<mailto:Christopher.Taylor2_at_parallon.net>" <Christopher.Taylor2_at_parallon.net<mailto:Christopher.Taylor2_at_parallon.net>> To: oracle-l_at_freelists.org<mailto: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><mailto: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:49:25 CET