Rollback Segment Usage 100%

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

Check for rbs contention first.

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

Hi

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

Set the optsize for all the rollback segment.

HTH

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

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.

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

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

Do a select hwmsize from v$rollstat

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

Any suggestions as to implementing the initial=next extent sizes.

Thanks
Anurag

Based on previous discussion, I would suppose the following
storage parameters for rollback segments in that system:

INITIAL 512K
NEXT 512K
OPTIMAL 20M
MINEXTENTS 20
MAXEXTENTS 1000 (or what you like)

I would replace old rollback segments with new one by one, like this:

ALTER ROLLBACK SEGMENT rbs01 OFFLINE;
DROP ROLLBACK SEGMENT rbs01;
CREATE ROLLBACK SEGMENT rbs01 TABLESPACE rbs
STORAGE (INITIAL 512K NEXT 512K OPTIMAL 20M MINEXTENTS 20 MAXEXTENTS 1000);

(Of course, you should not touch the SYSTEM rollback segment).

the docs of Admin Guide said that MINEXTENTS(n)=10 TO 20 is optimal and the rollback segment size(T) = max(table) * 10%, the INITIAL = T / n. so I think inosov's parameter will be appropriate.

Hi,

You can manually try to shrink the rollback segment by issuing

SVRMGRL>alter rollback segment < Your rollback seg name> shrink to 20M;

then

SVRMGRL> alter rollback segment storage(optimal 20M);

try this out.

rgds,

I appreciate INOSOV soutions.

Going by the size 0f 512k of extent size i think it should be large transactions used.

I would also suggest that the MINEXTENTS parameter
be set to 40 since you have mentioned on an average 20M.

By doing so dynamic extensions of extents can be avoided.

Cheers

Padmam

Mine is a Production Database. Is it safe to take one of the rollback segments offline them drop and recreate them.

Cant I create news rollback segments and drop the existing ones. Also what care should be taken to take the rollback segments offline. I mean do I need to check whether there are any active transactions or not.

Thanks
Anurag

Any suggestions please.

Sorry to bother again and again with the same question.

Thanks a lot for all the help.
Anurag

The safest time to rebuild rollback segments is when no active transactions.

If you take offline rollback segment before the transaction completing,
it may fail with ORA-1634.

Also, long-running queries may fail with "snapshot too old" error.

Thanks inosov,
How do I find whether any active transactions are there in the rollback segments.

Also to prevent ORA-1634 and snapshot too old errors shouldnt I first create additional rollback segments with your's and others suggestions in this thread and then drop the old ones.

Please suggest.

Thanks
Anurag

select s.sid, s.username, r.name
from v$session s, v$transaction t, v$rollname r
where s.taddr=t.addr
and t.xidusn = r.usn;

This will show all active transactions, users, and rollback segments.

If you receive "no rows selected", it's safe to rebuild rollback segments.

and what about creating additional rollback segments and dropping the older ones.

Thanks
Anurag

Again, I'd prefer to drop and create rollback segments one by one
in order to use the same names for new rollback segments,
otherwise don't forget to correct "rollback segments" line in init.ora if exists.

Inosov right, one more reason:

if application has "set transaction use rollback segment " then rollback segment names has to be fixed in database. Some applications are smart enough not to hard code those values but to have init params, but those has to be changed anyway.

In general, DBA's should understand that application may rely on a lot of things in a database (e.g. index names)