Rollback segments waits

[Sureshy]

Hi Friends..

I issued the following sql :

select gets,waits,waits/gets*100 wait_ratio,'%'
from v$rollstat

The results were :


GETS WAITS WAIT_RATIO '
---------- ---------- ---------- -
898 0 0 %
21800 0 0 %
20550 0 0 %
19363 0 0 %
18700 0 0 %
20153 0 0 %
18809 0 0 %
20080 0 0 %
19423 0 0 %
18899 0 0 %
21022 0 0 %

GETS WAITS WAIT_RATIO '
---------- ---------- ---------- -
20640 1 .004844961 %
19875 0 0 %
20053 0 0 %
19873 0 0 %
19588 0 0 %
20170 0 0 %
21167 1 .004724335 %
19917 0 0 %
20122 0 0 %
19503 0 0 %
19346 0 0 %

GETS WAITS WAIT_RATIO '
---------- ---------- ---------- -
19476 0 0 %
20348 0 0 %
21198 0 0 %


2 rollback segments have had a wait.
does this mean that I should consider adding another rollback segment or 2 ?

Thanks in advance
Suresh

[vinit]

hi,
The 2 waits are minimal and you could countinue with the same rollback segs. However since there are only 2 rollback segs, you can go ahead and create 1 or 2 more segs .
Have you checked the wraps column of the rollback segs ?

cheers

[reydp]

check XACTS field, this will give you an idea of how
many processes are utilizing the ROLLBACK SEGS in a
certain point in time.
If more than 1 then you might need to create new Rbs.

[vinit]

Hi Rey,
Pls note that at a time many processes/transactions can get hold of a rollback segment. This does not indicate a problem at all. As Sureshy had pointed out..only if there are more waits you may create new rollback segs.

vinit

[reydp]

hi vinit,
this is a parallel occurence anyway, one of the reason why you gets waits is when there are more than one processes utilizing the same rbs.
The point that I want to made is that If all your rbs have more than 1 XACTS most of the time then this surely means that you need to add more rbs.

[pando]

Originally posted by reydp

The point that I want to made is that If all your rbs have more than 1 XACTS most of the time then this surely means that you need to add more rbs.

you must be joking?
a rbs supports more than one transaction, actually the extents inside a RBS supports more than one transaction, XACTS means active transaction going in each RBS, if I have 40 extents in a RBS I can perfectly have more than 1, 2, 3, 40, 80 or even more transactions concurrently depending on transaction size

If we base on strategy in a system with 200 concurrent transactions we would have to create 200 RBS?

If you see values > 0 in WAITS then you may consider adding more RBS but from the statistics pasted the wait is minimal

[vinit]

hi,
IF you consider adding a rollback seg for no. of values in xacts then i wonder how many rollback segs would you create if your DB has transactions in thousands.
Secondly the wait is just 1 and not 1% of the gets , which as pando pointed out is quite minimal.

Cheers

[pando]

just want to add a note, you should rely on v$waitstat to tune your RBS not v$rollstat

[julian]

The point that I want to made is that If all your rbs have more than 1 XACTS most of the time then this surely means that you need to add more rbs.

I do suggest you look once again for the "Rule of the four" :-)

[reydp]

What did I miss....
Oh....you guys are taking a beating on me....heehhee...

So thus this means that if all of your rollback segments
lets say just 8 and not 80 XACTS most of the time you will
not consider adding RBS?
Cause I believe if this happens it will hit your GETS and WAITS ratio down.

Anyway, I just want to consider the XACTS factors aside from GETS and WAITS......

If I am wrong then CHEERS to you guys.......

Keept it cool.