I'm trying to perform an sql query in Oracle 11g to know the transactions per second of an instance, and manipulate the time it gives me the TPS average, for example find the average TPS per day or see the TPS each 15 min of the day:
00:15 ### 00:30 ### 00:45 ### .... ...
The query below shows something similar. The issue is that for each instance in oracle it shows a different range. Sometimes it shows 2 months of in the range of 15 minutes in 15 minutes and sometimes shows 3 days.
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), 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'))
SELECT datetime,
ROUND (SUM (delta_value) / 3600, 2) "Transactions/s"
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
GROUP BY datetime
ORDER BY 1 desc;