I have seen a DB where SP is showing 96% for this stat. It means is Oracle is rolling back almost every transaction. However, I can not believe that is totally true. Upon further investigation I found out these stats:
- user rollbacks = 68,826,302 - transaction rollbacks = 43,418,224- db block changes = 320,162,991 - rollback changes - undo records applied = 11984 When I plotted SP data for a period of 7 days normalized by second for transactions, user rbs, and transaction rbs, it was clear that user rb were 90% of txns whereas transaction rbs were about 30%.
Furthermore, trying to understand what Oracle labels user vs. transaction rollback, I tested it in my play database and came up with this behavior:
- issuing rollback where no data change has occurred e.g. after a select will increment 'user rollback' but NOT transaction rollback. - Issuing it where data change has occurred e.g. after a delete, both user and transaction rollback will increment. - Killing a session after doing a DML (and before a commit), will increment transaction rollback but NOT user rollback.
So, based on all the mess above, can someone please address these inquires please ... - does the SP metric Rollback per transaction include user and transaction rollbacks or just transactions. Or whatever else.
- Based on this data, how can I correctly interpret the metric value.- I divided transaction rollback/transactions which came up to 30%. Does this mean 30% percent of rollbacks actually do rollback data resulting from sessions terminating and PMON cleaning up. Is this far fetched?
-- //www.freelists.org/webpage/oracle-l
