resize rollback segs
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: resize rollback segs

Hybrid View

  1. #1
    Join Date
    Aug 2000
    Posts
    163
    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.

  2. #2
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    make the BIG rollback segment --

    OFFLINE
    DROP
    CREATE new smaller segments

    - Rajeev

    Rajeev Suri

  3. #3
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    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.

  4. #4
    Join Date
    Aug 2000
    Posts
    163
    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.

  5. #5
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    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.

  6. #6
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658

    Investigate

    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



    Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width