IBM Support

Why does an Oracle AWR report show such a high rollback % number for an Sterling MCF schema?  

Question & Answer


Question

Why does an Oracle AWR report show such a high rollback % number for an Sterling MCF schema?

Answer

With version 8.x, an MCF system using Oracle, showing a rollback % greater than 50%, is expected, and does not really mean that 1 out of 2 transactions is failing, and being rolled back.

Oracle reports, via the AWR, two different figures: User Commits, which is the number of times that Commit is called from user processes, and User Rollbacks, which is the corresponding number of calls to Rollback. The reported rollback percentage is simply = rollback / (rollback + commit)

The MCF system uses db connection pooling as part of its processing. When an API is called, the code:
- gets a connection from the pool
- begins a transaction
- performs one or more updates to the database
- ends the transaction with either a commit or rollback, as needed, based on business logic
- releases the connection back to the pool
One function of the connection pooling is, when a connection is released, to automatically perform a rollback; this is in case the coding logic accidentally didn’t either commit or rollback. So, there will be a rollback for every connection used, independent of whether the code called commit or rollback, to this will prevent uncommitted transactions from causing downstream locking problems.
Because of this, the normal sequence will be:
- begin transaction
- commit
- end transaction
- rollback (from connection pooling)
This means Oracle’s simplified stats will show a minimum of 50% rollback percentage, even if every pooled transaction is successfully committed.
There are some additional AWR numbers that back this up. Two numbers reported are:
- db block changes
- rollback changes: undo records applied
If you look at this two numbers, you should see that for every undo record applied, there is a much higher number of db block changes made. This shows that there are many more updates being committed, as compared to the number of real rollbacks happening.

[{"Product":{"code":"SS6QYM","label":"Sterling Selling and Fulfillment Suite"},"Business Unit":{"code":"BU055","label":"Cognitive Applications"},"Component":"Not Applicable","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All","Edition":"","Line of Business":{"code":"LOB31","label":"WCE Watson Marketing and Commerce"}}]

Historical Number

FAQ2905

Document Information

Modified date:
16 June 2018

UID

swg21518197