ROLLBACK SEGMENTS
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: ROLLBACK SEGMENTS

  1. #1
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    372
    Hi all
    We have rollback contention determined after queyring v$rollname and v$rollstat.
    We are showing rollback segments as following:
    RBS01 INITIAL =524288
    NEXT =524288
    MIN EXTENTS= 8
    MAX EXTENT=32765
    AND no OPTIMAL specified.

    I would like to increase the size of the INITIAL & NEXT and set MINEXTET to 20.
    Our db_block_size is 8196.

    Can I increase initial and next by just multiplying their respective value to 2 to have large rollback segments?
    EX= INITIAL =(524288 x2)=1048576
    NEXT =(524288x2)= 1048576
    MINEXTENT = 20
    MAXEXTENT =??????? (Considering the figure what value may give to maxextent and Optimal?
    OPTIMAL =???????
    Total rollback segment online 34.
    After creation of all the big rollbacks I will bring them all online and take all the little offline and drop then right?

    Please advise
    Arsene Lupain
    The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    You can increase the size of initial and next but you will have to drop and recreate the rollbacksegments.
    OPTIMAL depend on, to what size you want to maintain your rollback segments to avaiod dynamic allolcation of extents.
    Keep in mind..
    INITIAL=NEXT and OPTIMAL > MINEXTENTS*INITIAL

    Sanjay


  3. #3
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    372

    ROLLBACK SEGMENT

    Sanjay
    Thank you for responding so promptly.
    Considering the following
    INITIAL=NEXT and OPTIMAL > MINEXTENTS*INITIAL

    My new rollback segment size will be as follow.
    INITIAL= 1048576
    NEXT = 1048576
    MINEXTENTS = 20
    MAXEXTENTS =? (what's the new value?)0ld value=32765

    OPTIMAL= 26214400 (ie 1048576*20*1.25)

    The value of the orignal maxextent on the small rollback segment is 32765.

    IS THERE ANY GUIDELINE TO INCREASE THE SIZE OF MAXEXTENTS?

    And once I create the new rollback segments I will first bring them online, then take the old offline and drop them.

    Can you create a new rollback segment rbs1, if you already have rbs1? or you first offline rbs1, drop it and then recreate rbs1.

    Please advise.

    Arsene Lupain
    The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.

  4. #4
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    I'd keep your MAXEXTENTS to a nominal size depending on the size of your extents, tablespace size and transaction size. Set your MAXEXTENTS from 200 - 2000 if you'd like. In your case it depends on how large you transaction are likely to grow.

    The only thing I'd advise you on is to HAVE a MAXEXTENT set, in order to STOP a rouge transactions from occupying the entire RBS TABLESPACE.

    Also Setting your OPTIMAL to small (which I'm not saying you have done, but again depends on you average transaction size) can cause "Snapshot too old", because a shrink can occur on your rollback segment and cause another process using the segment to lose it's consistent view. Very frustrating. In my opinion, the ROLLBACK SHOULD NOT shrirk until an active transaction using it for read consistency is completed. (But I'm not developing the oracle engine). Unfortunalty, Oracle9i didn't fix this issue either, istead of setting an OPTIMAL size, you set a RETENTION_TIME. This will cause the same problem.

    Sorry I got a bit off track...
    OCP 8i, 9i DBA
    Brisbane Australia

  5. #5
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    I would avoid the use of OPTIMAL storage parameter as this will lead to dynamic allocation and deallocation of the extents which will lead to low performance.Instead of setting optimal storage parameter,set proper values for INITIAL & NEXT storage.
    You can get to know how much of undo information is generated for you transaction by checking the
    'USED_UBLK' column of v$transaction after executing the transaction.
    This will give u the no of undo blocks used by the transaction.
    Multiply this by ur block size and round it to the next multiple of power 2.
    Set the result got as the value for the INITIAL and NEXT storage parameter and set ur minextents to 20.

    HOPE THIS HELPS.

    REGARDS
    ANANDKL
    anandkl

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