Rollback per Transaction, as the name implies, is the percentage of transactions that rolled back. Based on the definition, it is quite evident that this ratio should be very low as we all know that Rolling back is extremely expensive. This percentage is visible in an AWR / Statspack Snapshot, just below the Load Profile section.
I have come across many databases where this ratio is actually very high and based on the information and description of this Statistic, the application and the dba team strive hard to get this ratio to a very less %age.
Let us dive into the details of this ratio and the way it is calculated. Based on this information, you should be able to judge whether this is alarming on your database or should be just left at the current %age.
"Rollback per Transaction" %age is calculated based on two Statistics : "user commits" and "user rollbacks" and the sum of these two actually constitute the total number of Transactions. For example, assuming an AWR or Statspack Snapshot of 30 Minutes duration, and the value of user commits and user rollbacks are as under :
user commits is 158321
user rollbacks is 423
then, Transactions will be (158321+423) = 158744. Under Load Profile section, transaction per second (TPS) is also computed and for the subject report it would be Total Transactions / Elapsed Time in Seconds i.e. (158744/(30*60)), since it is in Minutes. The value of TPS will be 88.19.
Rollback Per Transaction, in this case, would be calculated as (100*user rollbacks / total transactions) and this would be : (100*423/158744) = 0.27.
Now, since we have the formulas in place, it can be used to get these for a real production system. These are normally not required for you as these are automatically done and printed in a report, but as we go further we can see a flaw in the calculation of "Rollback per Transaction".
What is the flaw ?
I have a production system and on this system, "rollback per transaction" ratio is always between 3 to 4 % and this is normal (should be not considered alarming). Just for demonstration, I generated a snapshot of 3.75 Minutes duration and another of 1.3 Minutes Duration.
During the second snap of 1.3 minutes duration, I connected to the database using a SQLPlus session and without initiating any transactions, issued around 10000 rollbacks in a loop. This was a plain rollback using following small anonymous block :
begin
for i in 1..10000
loop
rollback;
end loop;
end;
/
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
It can be seen that the block ha snot initiated any transactions, hence, the rollback in this case will not be resource intensive. But these 10000 rollbacks are good enough to increase the number of transactions and user rollbacks statistics and yes, the AWR report shows the "Rollback per Transaction" ratio very high enough to raise an alarm. This seems to be a flaw in the calculation which says that even if a user issues plain rollback (which is normally not the case), this ratio could go fictitiously high.
The two AWR reports are pasted below after the completion of this blog.
It seems to me that there is a BUG with JDBC that for each connection or disconnection, it issues a rollback and this is enough to raise this "rollback per transaction" ratio.
There is another cause that can raise this ratio to a higher %age and should be considered as alarming is Enqueue Waits. I have discussed this with an example in one of my blogs posted on 15th March 2007.
To actually measure or verify whether "Rollback per Transaction" ratio is alarming is to compare the values of "db block changes" and "rollback changes : undo records applied". If the value of "rollback changes: undo records applied" is high, it means, transactions are actually getting rolledback and these changes are applied from the Undo Segments.
To summarize, 'Rollback per Transaction" ratio is not always alarming as the way this is calculated does not necessarily mean that Transactions are getting rolledback. Check for other statistics, like, "db block changes" and "rollback changes: undo records applied" to measure the impact of this ratio.
DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
IPNEW 887487882 IPNEW 1 10.2.0.3.0 NO hlrpb
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 6973 29-May-08 11:32:35 536 151.5
End Snap: 6974 29-May-08 11:36:19 537 159.6
Elapsed: 3.75 (mins)
DB Time: 69.24 (mins)
## Some part removed to make this small
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
## Some part removed to make this small
Executes: 17,857.33 312.87
Transactions: 57.08
% Blocks changed per Read: 7.01 Recursive Call %: 85.72
Rollback per transaction %: 3.48 Rows per Sort: 123.03
Statistic Total per Second per Trans
-------------------------------- ------------------ -------------- -------------
user commits 12,391 55.1 1.0
user rollbacks 447 2.0 0.0
TPS = (12391+447)/(3.75*60) = 57.06 (Matches approx.) or
Roll/Tran = (100*447/(12391+447)) = 3.48
## This AWR after executing a anonymous pl/sql block with rollback;
DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
IPNEW 887487882 IPNEW 1 10.2.0.3.0 NO hlrpb
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 6972 29-May-08 11:31:13 531 149.5
End Snap: 6973 29-May-08 11:32:35 536 151.5
Elapsed: 1.35 (mins)
DB Time: 29.67 (mins)
## Some part removed to make this small
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
## Some part removed to make this small
Executes: 24,540.91 175.09
Transactions: 140.16
% Blocks changed per Read: 13.30 Recursive Call %: 86.52
Rollback per transaction %: 89.55 Rows per Sort: 111.79
Statistic Total per Second per Trans
-------------------------------- ------------------ -------------- -------------
user commits 1,188 14.6 0.1
user rollbacks 10,185 125.5 0.9
TPS = (1188+10185)/(1.35*60) = 140.41 (Matches approx.) or Use the values from "per Second"
TPS = (14.6+125.5) = 140.1
Roll/Tran = (100*10185/(1188+10185)) = 89.55
3 comments:
Good work !!!
Make sure you're not hitting an ASSM index bug with inserts. Use some session level v$sesstat snapshot tool (like Snapper) to see if you experience only "transaction rollbacks" or also "user rollbacks". if it's only transaction rollbacks then it's likely a bug with ASSM rolling back its recursive space allocation transactions!
The last time I troubleshooted this, turns that it was due weblogic which was executing a script every few seconds to check the state of the persistent connections. "select * from dual" followed by a "rollback" was the statement, and this was configurable. I forget if the script had the rollback, or it was weblogic appending the rollback, since it had no idea of you are passing an update/insert/delete as the SQL.
Post a Comment