Transactions per second ( in a RAC DB setup)

  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

/

Note: the input will be instance number, like 1, 2 etc