Statspack Examples
People frequently ask questions about interpreting statspack outputs. So I’ve started to collect references to examples of statspack output that have been published on the Internet with intelligent comments about interpretation.
There are bound to be more out there than I currently know of, so if you know of any good examples, let me know by email.
First on the list:
Connie Dialeris Green: Diagnosing Performance Bottlenecks using Statspack and the Oracle Performance Method.
Doug Burns: “comparing current with recent history”.
Jonathan Lewis: Caution with “Timed Events”
Jonathan Lewis: Comments on “Instance Efficiency Ratios”
Hi Jonathan,
One of my Database reports Rollback per transaction %age to 57%.
Rollback per transaction %: 57.51
Now, the values, as mentioned in your “Comments on Instance Efficiency Ratios” are as under :
data blocks consistent reads - un : 13,041,487
db block changes : 22,165,566
rollback changes - undo records a : 134,139
I am not able to derive the %age that is calculated in the Statspack. Based on these figures, do you feel that this %age i.e.57% is alarming ?
Regards
Comment by Viveshar — August 13, 2007 @ 2:41 pm UTC Aug 13,2007
Viveshar,
The referenced article says: “To see if these rollbacks really are doing work rolling back, you need to check the statistics: rollback changes - undo records applied and compare that with the statistic db block changes to see how many of your db block changes are forward changes, and how many of them are due to rolling back.”
You make about 22M data block changes, and reverse out about 134,000 of them. Your 57% figure is probably not a serious threat.
From memory I believe that this particular percentage is defined as “user rollbacks”/(“user commits” + “user rollbacks”). You might like to check that against your stats.
Comment by Jonathan Lewis — August 13, 2007 @ 8:45 pm UTC Aug 13,2007
Hi Jonathan,
You were correct, the percentage matches i.e user_rollbacks/(user_commits+user_rollbacks).
Though, the undo records applied value is less, then what triggers “user rollbacks” ? I checked, it seems that a simple ROLLBACK also increments “user rollbacks” even if that rollback was a simple without any transaction associated to it.
Regards
Comment by viveshar — August 14, 2007 @ 9:09 am UTC Aug 14,2007
Viveshar,
Your observation is correct, a “rollback;” command is counted as a “user rollback”. That was the point made by the comments at the end of the article “Instance Efficiency Ratios”.
Comment by Jonathan Lewis — August 15, 2007 @ 9:16 pm UTC Aug 15,2007
Hey Jonathan:
I have Very high “rollback per transaction %”. It is around 90-95%. With following sql, I found MGAGENT (which is a Messaging Gateway agent) is continuously increasing value for “user rollback” statistics.
column name format a15
column username format a10
column program format a10
set line 200
column machine format a10
select * from (
select ses.USERNAME ,ses.sid,ses.SERIAL#, s.VALUE value ,n.NAME name, ses.SQL_ADDRESS , round(ses.LAST_CALL_ET/60,2) LAST_CALL_ET
From v$sesstat s, v$statname n, v$session ses
where s.STATISTIC# = n.STATISTIC#
and ses.SID = s.SID
–and p.ADDR = ses.PADDR
and n.NAME like ‘%user rollbacks%’
and value 0
and ses.username is not null
order by value desc ) x
where rownum <10;
OUtPUT
—-
MGWAGENT 116 16014 886876 user rollbacks 000000038BDB2D60 .05
After 3 mintues
MGWAGENT 116 16014 887159 user rollbacks 000000038D3ABE50 0
I can see that it’s increasing 100 per minute User rollback for MGWAGENT.
Oracle Support is asking about Unod State and usage of Undo block by that session.
What relates undo blocks and user rollbacks ? Would you please explain how they co-relate with each other.
thanks
~Keyur
Comment by Keyur — August 16, 2007 @ 4:20 pm UTC Aug 16,2007
Keyur,
I think Oracle may be asking about “undo records applied”.
Every time you issue a “rollback;” your session increments the statistic “user rollbacks” - even if you have done no other work at all.
If you change any data, you will have generated some “undo records” which start to fill “undo blocks”, and when you issue the “rollback;” your session will count how many undo “records” had to be used to make that “rollback;” happen. This count is recorded in the statisic “rollback changes - undo records applied”.
Each “record” basically represents a single change to a single block. So if you insert a row into a table with two indexes, you generate three changes, which means you generate three undo records, which means that if you “rollback;” you will see that statistic go up by three.
If a session’s “user rollbacks” is large, but its “rollback changes - undo records applied” is small (and those numbers are relative to your system) then most of the rollbacks are doing nothing.
Comment by Jonathan Lewis — August 16, 2007 @ 5:35 pm UTC Aug 16,2007
hi friends
i am sending statspack report for analysis purpose plz send me details that what this statspack reporty saying
STATSPACK report for …
[Unformatted statspack report deleted - JPL]
Comment by vijaykumar — September 13, 2007 @ 6:03 am UTC Sep 13,2007
Vijay Kumar,
This isn’t a forum for sending in your problems for other people to solve. If you want to post statspack reports, you can try the official Oracle Forum.
Comment by Jonathan Lewis — September 13, 2007 @ 6:37 pm UTC Sep 13,2007