“Holy ‘My Boss wants Oracle TPS metrics’, DBAtman!”
Don’t worry young Robin, we can get it for you…
Sometimes this information has some ‘myth atmosphere’… Maybe because of that Oracle doesn’t have this information that 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.
Na, na, na, na, na, na, na, na, na, na, na, na… DBAtman!