Sometimes this information has some ‘myth atmosphere’… Maybe because of that Oracle doesn’t have this information very clear and it’s not the most useful metric.
But for comparison to another systems and also to performance/’throughput’ with different infrastructure/database configuration, it can be useful.
It can be seen by AWR on “Report Summary” section, on “Load Profile”, “Transactions” item:
But if you want to calculate it through SQL query?
And if you want to have a historic from this metric?
I found a reference for this calculation here, using v$sysstat.
It’s the only reference I found, and it on 10g documentation… It refers this metric as:
Number of Transactions = (DeltaCommits+DeltaRollbacks)/Time
It also refers as DeltaCommits and DeltaRollbacks, respectively, “user commits” and user “rollbacks”.
Where it goes a possible SQL to do that:
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;
I like to use PL/SQL Developer to see this kind of data. And it regards us to make very good charts very quickly. I try it in a small database here, just for example:
Jedi Master Jonathan Lewis wrote a good post about Transactions and this kind of AWR metric here.
See ya!
Matheus.
