Increasing size of Rollback segments
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Increasing size of Rollback segments

Hybrid View

  1. #1
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    Hi Friends,

    I have 25 RBS
    looking in DBA_Segments, the are all different sizes and all have a different high water mark, they are not set to an optimal size yet and all have the same storage parameters(Initial, next, min, max)

    When the subject of increasing rollback segments is discussed, what does this actually mean.
    I always thought that rollback segments were dynamic and would grow and shrink dependant on their storage parameters, only limited by the size of the tablespace they live in or their max extents.

    Does increasing the size of RBS mean giving them a larger optimal size, or larger amount of min extents ?

    Also..

    What is the advantage of issuing :-
    Set transaction use rollback segment XXXXX. -
    I always thought that this would allow a session to maybe use a large RBS for a large transaction, but as all RBS's are dynamic an would grow/shrink depending upon the transaction and optimal size, would it not be equally as proficient to use any rollback segment /

    Someone Please enlighten me.

    The reason I am asking is because I have been asked to increase RBS size by a pseudo DBA, who is my senior and I need to be armed with a more clearer understading of them before I tackle this subject.

    Many Thanks

    Suresh

  2. #2
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    First of all, you have a lot of RBS : 25 !!!
    How many simultaneous connections do you have (at max) ???
    Well, apart from this point, let's answer your questions :
    increasing the size of a rollback segment often means turning a rbs offline, dropping it, and recreating it using a greater value for the initial extent.
    In your architecture, there are 2 bad things :

    1) you do not have optimal values set. this is horrible since Oracle spends a lot of time allocating and deallocating extents for your rbs, while an optimal prevents this for small transactions (since you stay under the optimal value, there is no need to increase or decrease the size of the rbs)

    2) it does not appear to be very wise to use the same storage parameters for all your rbs since you have big ones and small ones.

    In my opinion, you should first think about the number of rbs, then design some of these rbs as big ones (let's say 2 for example), and the others as small ones.
    Big ones would specifically be used for big transactions, using : set transaction use rollback segment ...

    hope this helps, and ask for anything else

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by Sureshy
    Hi Friends,

    I have 25 RBS
    looking in DBA_Segments, the are all different sizes and all have a different high water mark, they are not set to an optimal size yet and all have the same storage parameters(Initial, next, min, max)

    When the subject of increasing rollback segments is discussed, what does this actually mean.

    I always thought that rollback segments were dynamic and would grow and shrink dependant on their storage parameters, only limited by the size of the tablespace they live in or their max extents.
    Sort of yes, sort of no. Typically, RBS grow with larger DML statements. Unless you have an OPTIMAL setting, they will not shrink. Extends and shrinks should be limited in rollback segments to optimize performance.

    Remember, RBS are also used for SELECT statements so you get a consistent view of the data and will NOT expand the RBS. If this is the case, you will receive an ORA-1555 error and you should adjust the OPTIMAL parameter of your RBS.


    Does increasing the size of RBS mean giving them a larger optimal size, or larger amount of min extents ?
    In my mind, I think that means that the OPTIMAL size of the rollback segment needs to be adjusted.


    Also..

    What is the advantage of issuing :-
    Set transaction use rollback segment XXXXX. -
    I always thought that this would allow a session to maybe use a large RBS for a large transaction, but as all RBS's are dynamic an would grow/shrink depending upon the transaction and optimal size, would it not be equally as proficient to use any rollback segment /
    IMHO, If you have properly sized your rollback segments, there is no advantage. RBS are assigned on a round-robin basis and your transaction should be able to use any available RBS.



    Someone Please enlighten me.

    The reason I am asking is because I have been asked to increase RBS size by a pseudo DBA, who is my senior and I need to be armed with a more clearer understading of them before I tackle this subject.

    Many Thanks

    Suresh
    I'm sure he/she is experiencing a problem. Ask them why they think the RBS need to be increased.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by pipo
    First of all, you have a lot of RBS : 25 !!!
    This statement is opinion and not fact. The number of RBS is loosely proportional to the peak number of concurrent transactions. This may be fine for your environment.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    Hi Friends..

    Many Thanks for your replies..

    The reason I have 25 rollback segements is based upon the calculation : Transactions/Max Trans per rollback Segment = 125/5.

    After speaking to this 'senior???' he told me that he wanted them increased due to Dr.Watson Errors when executing a large batch transaction ???. I told him to get in touch with the application vendor as this was more an MS memmory management issue, which by the way is not fixed in W2k and probably not fixed in WindowsXP.

    I told him that surely Oracle would omit an ORA-XXXX error message if rollback storage parameters/space was being reached and there was nothing in the alert.log to say that there was problem and no such message appeared on screen, but he did not believe me. I therefore proved it to him by putting a trace on the session running the transaction and showed him the trace file. I still don't think he is convinced.. I think he needs a dba course.

    At least now I know I need to set an optimal setting, can this be done with the following command :

    ALTER ROLBACK SEGMENT R01 STORAGE (OPTIMAL 20MB)

    Oracle 8.1.6

    Thanks

    Suresh




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