URGENT: Rollback segment contention??

[mooks]

Hi

The following is the statistic from v$waitstat.

CLASS COUNT TIME
------------------ ------------------- -------------------
data block 24667428 0
sort block 0 0
save undo block 0 0
segment header 34 0
save undo header 0 0
free list 0 0
extent map 0 0
bitmap block 0 0
bitmap index block 0 0
unused 0 0
system undo header 0 0
system undo block 0 0
undo header 165633 0
undo block 38002 0


From the statistic, I noticed that the undo header figure is high. I have re-created my rollback segment to a bigger size, but the undo header figure did not drop, why is this so?


The data block figure is also very high, any ways to reduce it?

Please help!!! Many Thanks!

[Sureshy]

This is similar to what I have.

The usual remedy for 'undo' waits is to add more rollbak segments. However you should query v$rollstats to work out your wait ratio.

select gets,waits,waits/gets*100 wait_ratio,'%', wraps, extends, shrinks, extents, xacts
from v$rollstat;


For datablock waits you need to query v$session_waits for 'buffer busy wait' event.

P1, P2, and P3 in V$session_wait will tell you File, Block and Reason for wait.

data block waits usually mean there are not enough freelists per block, or initrans needs increasing.

Metalink has lots of documentation on the subject.

[mooks]

Hi

I have increased my number of rollback segments, but it doesnt seems to help when i check for the undo from the V$waitstat table. However, when I check for the waits from v$rollstat table, they are showing 0 or minimum. Therefore why is it that the number of undo are not decreasing ??

Any help please?

[clio_usa]

mooks,

To get the clear picture of the rollback segments contention, you have to look at the ratio between the total number of request for data over the same period of time and the statistics in v$waitstat, not just the numbers in v$waitstat.

Get the statistics with:

select class, count
from v$waitstat
where class like '%undo%;

Then get the number of consistent gets:

select value
from v$sysstat
where name = 'consistent gets';

The ratio of waits for any class should be less than 1% of the total number of requets. The solution is to add more rollback segments.

For rollback segments sizing you can also check:
http://www.dbaclick.com/cgi-bin/ib3/...=ST;f=20;t=279

Hope that helps,



--clio_usa
Senior Oracle DBA

[grjohnson]

Now, you know the count number won't Decrease don't you... It'll only reset to zero after instance shutdown and restart. If the undo count don't increase (or are increasing VERY slowly), you may have solved the issue.