Re: high Rollback per transaction %: 96%

You can read the text of the statspack package and reports in the varous sp*.sql in $ORACLE_HOME/rdbms/admin. I can't remember which file at the moment, but one of them shows you that

    'transactions' = "user commits" + "user rollbacks".

I wrote a short note some time ago describing the observations you've made:

    http://www.jlcomp.demon.co.uk/statspack_02.html

What the note doesn't mention is 'transaction rollbacks' - and the fact that (a) transaction rollbacks can be system (or recursive), and (b) they don't always seem to update the statistic "rollback changes - undo records applied" properly when they are recursive.

This means you can't interpret the metric "properly" - your only option is to compare all four figures plus the "db block changes" and check that the work done in "real" rollbacks is sufficiently small compared to the work done in forward changes. (And, just to make it harder:

    db block changes = forward changes plus backward changes which means

    "real" forward change" = db block changes - "rollback changes - undo records applied"
)

There is an oddity with your stats, by the way:

    transaction rollbacks = 43,418,224
    rollback changes - undo records applied = 11984

In principle, a "real" transaction rollback ought to find at least one undo record
to apply. (The only case I can think of that might not would relate to incoming distributed queries which take an undo segment header slot, but don't generate undo - and I'd have to test that to find out what happened).

So for 43M transaction rollbacks, where have all the undo records gone ?

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

From: fmhabash <fmhabash_at_xxxxxxxxx>
To: oracle-l_at_xxxxxxxxxxxxx
Date: Wed, 29 Aug 2007 16:20:45 -0400

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

--
http://www.freelists.org/webpage/oracle-l