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”
Jonathan Lewis: Analysing Statspack – part 1
…
Jonathan Lewis: Analysing Statspack – part 11
Footnote: Any advice about reading statspack reports is almost always relevant when reading AWR reports.
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 |
[...] is very convenient, given my current jottings on using Statspack, and constitutes the first of a list of examples I’ve set up as a separate [...]
Pingback by Saving Statistics « Oracle Scratchpad — October 18, 2008 @ 4:48 pm UTC Oct 18,2008 |
Hi jonathan,
I´m new with tuning and i have a little problem, i read a lot of blogs and articles about awr and stackspack. Here is part of my awr report.
Server – DELL 2850 – Windows 2003 SP2 x86 – 4GB RAM (/3GB) 2CPU.
Snap Id Snap Time Sessions Curs/Sess
——— ——————- ——– ———
Begin Snap: 11592 20-May-09 09:00:49 110 50.3
End Snap: 11761 27-May-09 10:01:00 115 45.3
Elapsed: 10,140.18 (mins)
DB Time: 19,151.07 (mins)
{cut by JPL}
I´m a bit lost… i need to look for hot blocks, sql statments that are consuming a lot of cpu, set pct_free of hot_blocks to a high value¿?
I need some advices.
Comment by Roberto — June 22, 2009 @ 9:38 am UTC Jun 22,2009 |
Roberto,
I’ve deleted most of you post. The sample of statspack you posted didn’t really have any value. Unless you promise me that the work done on the database every hour of every day of the week is always the same unvarying workload, then a report spanning seven days is not sensible. Pick an hour when the system seems to be loaded, and run a one-hour report across that interval – repeat as necessary.
In passing, though –
a) none of the stuff you posted suggested you had a problem with hot blocks.
b) if you are worried about CPU, then investigate the first few statements in SQL ordered by CPU, and SQL ordered by Gets
Have you read all 11 of the posts I have made about statspack analysis ?
Comment by Jonathan Lewis — June 24, 2009 @ 7:42 am UTC Jun 24,2009 |
Hi jonathan,
Sorry for my bad english… Thank you for your response. Yes, I read all of them… In other situations match with my issues with other databases and help a lot. I would like that exist more blogs as yours about stackspack.
The database is a logistic store and more or less has the same workload all days with little peaks (24×7). I´ll do your recommendation and look for other results.
Best Regards,
Roberto.
Comment by Roberto — June 24, 2009 @ 3:49 pm UTC Jun 24,2009 |
[...] the possible problems as fast and as right as he does. To cover more you need to start with this blog post of him and read all 11 of his series. One good advice I caught, do not create index in last [...]
Pingback by UKOUG DBMS SIG July 2009 « Coskan’s Approach to Oracle — July 2, 2009 @ 12:06 pm UTC Jul 2,2009 |
Hello again,
[...] Because my memory is like “doris (Nemo film)” in some occasions. I read again the 11 posts. For the other hand, I followed Jonathan advices and i monitored the work of the database more close. I saw that the Oracle advice of the size of redo log online told me that i have to change their size to 6GB; then, i reviewed the hourly archival historical for one day, next for the week and to finished, for the month.
Surprise, surprise…!!! I found that between 20:00 and 22:00 the database have done the half of archiver of all day. This sums with 76% of user rollbacks ways to me to 1 manteinance PLSQL package of the application.
This 76% of user rollbacks:
Statistic (of the 7 days) Total per Second per Trans
——————————– —————— ————– ————-
db block changes 1,012,928,251 1,664.9 57.7
rollback changes – undo records 177,118,776 291.1 10.1
Other snapshot 20:00 – 22:00:
Statistic (2 hours) Total per Second per Trans
——————————– —————— ————– ————-
db block changes 79,491,041 10,994.09 535.72
rollback changes – undo records applied 16,135,335 2,231.61 108.74
Now we are investigate with the developer team of the company of the application what does this package because its wrapped for us.
I hope that this will be the problem.
Best regards,
Roberto.
Comment by Roberto — July 6, 2009 @ 7:43 am UTC Jul 6,2009 |
Roberto,
A couple of things to look for: does the figure for “user rollbacks” go up in the same way for that interval – if not the extra rolling back may be an internal rollback. (See, for example, Tom Kyte’s articles on “write consistency”).
You could also check if “transaction rollbacks” goes up more that “user rollbacks” – if that’s the case then this tends to confirm that the rolling back could be internal. Check also for an increase in “leaf node splits”, as you can run into bad luck and generate a huge amount of undo and redo if you run into an index catastrophe. This typically only happens after a large-scale delete that leaves lots of empty blocks in an index, though.
Comment by Jonathan Lewis — July 9, 2009 @ 6:32 pm UTC Jul 9,2009 |
Hi Jonathan,
The responsable of application is on holidays. From now on, i´ll beard in mind your advise. But all points to a PL/SQL maintenance package that generates a lot of deletes because schedule “the warehouse distribute” (I don´´t know how translate this well).
This weekend i´ll read Tom Kyte’s articles on “write consistency”. Thank you so much.
Best Regards,
Roberto.
Comment by Roberto — July 17, 2009 @ 4:53 pm UTC Jul 17,2009 |