SNAP_ID Timestamp DB time User call recursive call Execute response time per call response time per execute
---------- -------------------- ---------- ---------- -------------- ---------- ----------------------
21869 2010-05-27 02:00:35 11.34 62962 8578 2203 .158540411 5.14842533
21870 2010-05-27 02:10:37 5.92 39674 9372 1944 .120613465 3.04300823
21871 2010-05-27 02:20:39 1.96 1577 9006 1713 .185081073 1.14344016
21872 2010-05-27 02:30:41 3.25 18444 7091 1455 .127185001 2.23207491
21873 2010-05-27 02:40:44 3 10056 9761 1771 .151221628 1.69212818
21874 2010-05-27 02:50:46 2.92 10073 9369 1663 .150439204 1.7587727
21875 2010-05-27 03:00:48 4.2 3797 9505 2950 .315791234 1.42395085
21876 2010-05-27 03:10:50 4.74 19537 9814 2099 .161380021 2.25662935
21877 2010-05-27 03:20:52 3.89 18713 9527 1970 .137674433 1.9735665
21878 2010-05-27 03:30:54 1.74 1506 5873 1358 .236378642 1.28441679
21879 2010-05-27 03:40:56 2.92 10126 9324 1773 .150375938 1.64964016
select sn.snap_id,
to_char(sn.end_interval_time,'yyyy-mm-dd hh24:mi:ss') "Timestamp",
round(v_dbtime.delta/1000000, 2) "DB time",
round(sum(case when v_call.stat_name in ('user calls')
then v_call.delta
else 0
end), 10) "User call",
round(sum(case when v_call.stat_name in ('recursive calls')
then v_call.delta
else 0
end), 10) "recursive call",
round(sum(case when v_call.stat_name = 'execute count'
then v_call.delta
else 0
end), 10) "Execute",
round((v_dbtime.delta/1000)/
sum(case when v_call.stat_name in ('user calls', 'recursive calls')
then v_call.delta
else 0
end), 10) "response time per call",
round((v_dbtime.delta/1000)/
sum(case when v_call.stat_name = 'execute count'
then v_call.delta
else 0
end), 10) "response time per execute"
from ( select snap_id,
nvl(value - lag(value) over ( partition by stat_name order by snap_id ), 0) delta
from dba_hist_sys_time_model
where stat_name = 'DB time'
and snap_id between &&snap_fr and &&snap_to
and instance_number = &&inst_no
) v_dbtime,
( select snap_id,
stat_name,
nvl(value - lag(value) over ( partition by stat_name order by snap_id ), 0) delta
from dba_hist_sysstat
where stat_name in ( 'user calls', 'recursive calls', 'execute count' )
and snap_id between &&snap_fr and &&snap_to
and instance_number = &&inst_no
) v_call,
( select snap_id,
end_interval_time
from dba_hist_snapshot
where snap_id between 1+ &&snap_fr and &&snap_to
and instance_number = &&inst_no
) sn
where v_call.snap_id = sn.snap_id
and v_dbtime.snap_id = sn.snap_id
group by sn.snap_id,
to_char(sn.end_interval_time,'yyyy-mm-dd hh24:mi:ss'),
v_dbtime.delta
order by sn.snap_id;
