resize rollback segs

[mary]

Hello,
I have 3 rollback segments 2 of which are 800m and one 1.5g. I know it is a better practice to have a larger number of rollback seg's but smaller in size. Please let me know how I can resize my rollback segments.
Thank you.

[rsuri]

make the BIG rollback segment --

OFFLINE
DROP
CREATE new smaller segments

- Rajeev

[Halo]

Well, it mainly depends upon the type of db. If it's OLTP, you can have many small ones, in DW/DSS you can have a few large ones. What type of db is this?? How many concurrent transactions are you having in the db at any given time. The thumb rule it to have # rbs=#concurrent transactions/4, with transactions_per_rbs=4 or 5. And # rbs should be a multiple of 4. You can resize them, no problem.

[mary]

It is not OLTP. I have about 50 users. 20 of them (max) would be logged on at the same time.
Also, another question I have is regarding the rollback tablespace itself. It is 2G, 1.2 of which is taken. What would be a right size of my tablespace?
Thank you in advance.

[Halo]

For 20 concurrent users, you might want to have atleast 4 rbs. 2 Gb should be fine. Make sure that there are no file size limitations on the OS if you have Autoextend ON for the ts. You may want to consider using optimal storage parameters for the rbs (if they are not). You may want to (re)size the rbs based on max(rssize). This is a good way to size them and monitor their behavior for future modifications.

[padmam]

Hi Mary , 5th May 2001 16:05 hrs chennai

What i would suggest you is always do a thorough Investigation(Tuning) the RBS with the avilable means in oracle.

I.Using v$ views

II.Using Oracle OEM.Tuning packs.

III.Running UTLBSTAT and UTLESTAT

===Mehod I====
The main area you have to see is the RBS header contention where all the problems lies.

A simple formula to work on is .

>SELECT SUM(WAITS)*100/SUM(GETS) "RATIO",SUM(WAITS) "WAITS",SUM(GETS) "GETS" FROM V$ROLLSTAT;

If the ratio of the sum of WAITS to Sum of GETS should be less than 5%.

If not then try to create more RBS.

====Method II===

The number of waits for any class should be less than 1% of the total number of requests.

>SELECT CLASS,COUNT FROM V$WAITSTAT WHERE CLASS LIKE '%undo%';

>SELECT SUM(VALUE) FROM V$SYSSTAT WHERE NAME='consistent gets';

Take the statistics and compare the values.As i mentioned above if the % is above 1% try to create more RBS.

===Method III====

Always during IMPORT set COMMIT=Y.Size the set of rows with BUFFER_SIZE

During Export set CONSISTENT=N

SQL LOADER set the COMMIT intervals with ROWS keyword.

Cheers

Padmam

==============

The rest as said by HALO.

Cheers

Padmam