Hi,
Recently, I found a query on internet that uses dba_hist_sqlstat to give details about a particular sql_id. I have slight confusion about output of this query.
Query :-
col snap_time format a12 truncate heading "Snapshot|Time"
col execs format 999,990 heading "Execs"
col lio_per_exec format 999,999,999,990.00 heading "Avg LIO|Per Exec"
col pio_per_exec format 999,999,999,990.00 heading "Avg PIO|Per Exec"
col cpu_per_exec format 999,999,999,990.00 heading "Avg|CPU (secs)|Per Exec"
col ela_per_exec format 999,999,999,990.00 heading "Avg|Elapsed (secs)|Per Exec"
ttitle center 'Summary Execution Statistics Over Time'
select to_char(s.begin_interval_time, 'DD-MON HH24:MI') snap_time,
ss.executions_delta execs,
ss.buffer_gets_delta/decode(ss.executions_delta,0,1,ss.executions_delta) lio_per_exec,
ss.disk_reads_delta/decode(ss.executions_delta,0,1,ss.executions_delta) pio_per_exec,
(ss.cpu_time_delta/1000000)/decode(ss.executions_delta,0,1,ss.executions_delta) cpu_per_exec,
(ss.elapsed_time_delta/1000000)/decode(ss.executions_delta,0,1,ss.executions_delta) ela_per_exec
from dba_hist_snapshot s,
dba_hist_sqlstat ss
where ss.dbid = s.dbid
and ss.instance_number = s.instance_number
and ss.snap_id = s.snap_id
and ss.sql_id = '931ayzut0jksx'
--and ss.executions_delta > 0
and s.begin_interval_time >= sysdate - 10
order by s.snap_id;
Output:-
Summary Execution Statistics Over Time
Avg Avg
Snapshot Avg LIO Avg PIO CPU (secs) Elapsed (secs)
Time Execs Per Exec Per Exec Per Exec Per Exec
------------ -------- ------------------- ------------------- ------------------- -------------------
21-DEC 02:30 0 24,517,109.00 368,259.00 201.17 971.96
21-DEC 03:00 1 14,642,115.00 279,558.00 77.47 657.76
21-DEC 07:00 1 39,191,323.00 624,422.00 301.33 1,282.32
21-DEC 11:30 0 29,532,694.00 556,063.00 371.56 1,358.49
21-DEC 12:00 1 9,858,710.00 132,419.00 58.23 484.95
21-DEC 21:30 0 3,194,898.00 45,536.00 29.27 102.38
21-DEC 22:00 0 28,202,105.00 618,681.00 241.34 1,764.90
21-DEC 22:30 1 7,788,657.00 81,567.00 38.17 367.88
22-DEC 04:00 0 33,311,216.00 591,547.00 301.46 1,408.44
22-DEC 04:30 1 6,204,076.00 34,907.00 30.32 107.84
22-DEC 14:00 1 39,259,789.00 729,978.00 325.08 1,558.89
22-DEC 15:00 1 39,654,065.00 649,678.00 294.30 1,378.88
23-DEC 13:00 0 21,869,281.00 212,472.00 185.72 526.84
23-DEC 13:30 1 18,265,716.00 457,801.00 118.64 817.51
23-DEC 15:00 1 21,718,770.00 23,537.00 173.45 278.89
23-DEC 15:30 0 15,397,718.00 14,549.00 122.71 151.54
23-DEC 16:00 1 6,037,666.00 13,449.00 55.30 133.48
23-DEC 16:30 2 22,389,055.00 200.50 193.48 195.00
23-DEC 17:00 1 48,635,347.00 17,064.00 457.10 577.64
23-DEC 17:30 0 37,770,826.00 263,727.00 1,398.18 2,387.82
23-DEC 18:00 1 41,941,838.00 14,448.00 744.82 1,045.79
23-DEC 18:30 0 39,315,130.00 34,885.00 1,673.89 1,764.03
23-DEC 19:00 0 20,470,334.00 76,579.00 1,525.04 1,763.29
23-DEC 19:30 0 19,010,220.00 84,968.00 1,557.04 1,763.67
23-DEC 20:00 0 18,160,868.00 54,421.00 1,573.17 1,763.67
23-DEC 20:30 0 18,733,486.00 57,310.00 1,595.05 1,763.67
23-DEC 21:00 0 17,900,032.00 44,650.00 1,633.83 1,763.67
23-DEC 21:30 0 25,941,451.00 64,859.00 1,534.22 1,763.66
23-DEC 22:00 0 16,146,214.00 18,131.00 1,601.15 1,705.08
23-DEC 22:30 1 7,173,458.00 3,573.00 403.93 427.19
24-DEC 07:30 1 38,640,398.00 151,741.00 264.61 600.08
If you see around 23-Dec 1800 hrs snapshot, this sql started and continued till 22:00 snapshot.
Here's what I dont understand...if the sql continued through 1800-2200 hrs, then, why avg_elasped_time is around 1763 secs and not 1800 secs?
I 'd really appreciate if someone can help me understand this output.
-Yogi