Rollback segments waits

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

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

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.

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

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.

Quote:

---

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

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

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

Quote:

---

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" :-)

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.

Thanks For your help folks.
I will not be adding just yet.

Suresh

80 in XACTS means active transactions but if there are no waits why would we have to add more RBS? Plus you said if you see 1 in XACTS you have to add more RBS (but now you changed your mind and said 80?)

One More thing.

Checking the wraps column, I have about 20 - 25 per segment. my understaning of wraps is the amount of extents used in the rolback segments (when the segment extends from one extent to another). I would have thought that the only cost of this would be recursive sql.

Is 20 -25 extents a 'safe' number ?

If I were to have LMT RBS would this mean that there would be no cost of wraps ?

Thanks

Suresh

A wrap is when the transaction moves from one extent of the RBS to another.

You cannot say if many or few wraps is a good or a bad thing. If SHRINKS is high then you may want to increase OPTIMAL.

Create you RBSs with 20 extents and use OPTIMAL (= 20*size_of _an_extent)

hi,
If wraps are too high..you may also consider sizing your extents bigger.

Cheers

hi pando,
I quote that 80 from you but I re-read your post and I was wrong.
If you remember earlier I always mention IN ALL OF YOUR RBS and
MOST OF THE TIME.
In other words all of your rbs is busy most of the time with more than
1 active transaction each rbs.

If this happens do you think your gets/waits ratio will not be affected?
Let's say you have 20 rbs and only 1 tablespace/datafile source in
one disk do you think there is no I/O contention?

I have problem with my AIX netscape browser... always delay in posting.
: )

Quote:

---

Originally posted by vinit
hi,
If wraps are too high..you may also consider sizing your extents bigger.

Cheers

---

You cannot tune or give optimal numbers for wraps!

Say, you have large extents (5M) and very small transactions, you will probably never see a wrap, but else you may have many.

well according to you I should create one rbs per transaction

even my RBS are always busy, if there are not waits why should I create more? It´s like if I have a restaurant, I have 10 waiters, my restaurant is always full but my waiters dont make my customer wait why should I hire more waiters??

I dont understand why you emphasize one active transaction per rbs all the time, a RBS holds more than one tramsaction, if I have 20 extents per RBS I can perfectly have several transactions in one single RBS without much contention

[Edited by pando on 10-04-2001 at 09:47 AM]

Sure one rbs can handle more than one transaction.
But what I emphasize, if this happens most of the
time(numerous transaction one after the other)
in all rbs, then I say your gets/waits ratio goes down
and that you need to add another rbs.