Re: high Rollback per transaction %: 96%
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 1 Sep 2007 09:35:30 +0100
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@xxxxxxxxx>
To: oracle-l@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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
