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
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21518197