Rollback Segment Usage 100%

[anuragmin]

Hi,
From the Enterprise Manager I notoced that the usage of my rollback segment is 100%.

Does this mean that I have to increase the rollback segment size ??

Thanks
Anurag

[Halo]

Check for rbs contention first.

[mbaliga]

Hi,

Set OPTIMAL value. This will shrink the rbs after use.

Check for header waits as a % of total gets. If it is more than 1% you need more rollback segments.

Baliga

[murugappan]

Hi

If rollback contention is there, add some more rollback segment.

Set the optsize for all the rollback segment.

HTH

[anuragmin]

Hi,
I see that there is no contention for the rollback segments.But I must tell you that there are 2 rollback segments having extent sizes of 56K each and having 348 and 330 extents respectively.

I think such a high number of extents will have an impact on performance.

What should I do do fix this and also how do I determine the optimal size of the rollback segments.

I know that you have to set a low optimal value for small transactions and high for high transactions. But shouldnt I increase the rollback segment size and set the optimal to the current usage as thats the maximum it has been used.

Please suggest.

Thanks
Anurag

[Halo]

Set optimal value to avg(hwmsize). Set the initial=next for the ts at half the optimal value. Extent allocation for rbs is CPU intensive, small extent size will affect performance/response time.

[manjunathk]

Hi,
check your transaction size. And assign 4 or 5 transacions per rollback segment. Say, your transaction is 50K. For 4 transactions, you need to assign an initial of 200k, make next also 200k and normally optimal is set to twice the initial.
HTH
manjunath

[anuragmin]

Hi,
How Do I calculate my transaction size and high water mark for the rollback segments. I am using Enterprise manager version 2.2 an it shows me the Used % and not the high water mark as it used to show in the previous versions.

Thanks
Anurag

[Halo]

Do a select hwmsize from v$rollstat

[anuragmin]

Thanks everybody,

Is there any specific reason for setting the optimal size at avg(hwmsize) . My optimal value comes out to be 20 MB. This means that my initial and next should be 10 MB each as opposed to the initial=next=56KB.

what is the best approach to do this as it is not allowed to change the initial extent once it is set. Should I create 2 more rollback segments with the above values and bring them online and finally drop the older ones.

Please suggest.

Thanks
Anurag