Re: DBA_HIST_SQLSTAT

From: David Fitzjarrell <oratune_at_yahoo.com>
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) 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" <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-l
Received on Tue Nov 20 2012 - 18:24:07 CET

Original text of this message