select round(avg(a.tps)) from (
WITH hist_snaps
AS (SELECT instance_number,
snap_id,
round(begin_interval_time,'MI') datetime,
( begin_interval_time + 0 - LAG (begin_interval_time + 0)
OVER (PARTITION BY dbid, instance_number ORDER BY snap_id)) * 86400 diff_time
FROM dba_hist_snapshot where instance_number=&&1), hist_stats
AS (SELECT dbid,
instance_number,
snap_id,
stat_name,
VALUE - LAG (VALUE) OVER (PARTITION BY dbid,instance_number,stat_name ORDER BY snap_id)
delta_value
FROM dba_hist_sysstat
WHERE stat_name IN ('user commits', 'user rollbacks') and instance_number=&&1)
SELECT datetime,
ROUND (SUM (delta_value) / 3600, 2) TPS
FROM hist_snaps sn, hist_stats st
WHERE st.instance_number = sn.instance_number
AND st.snap_id = sn.snap_id
AND diff_time IS NOT NULL
and st.instance_number=&&1
GROUP BY datetime
ORDER BY 1 desc
) a
where rownum < 61
/
