Rollback Segments
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Rollback Segments

  1. #1
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727

    Question Rollback Segments

    Hi All

    Creating the rollback segments


    In my database, got 41 rollback segments with 480 users and value of transactions_per_rollback_segments is 5. The nature of the database is OLTP (Banking). Oracle version 7.3 in Sun Solaris 2.8. Now I need to have more rollback segments. What is the best method

    1. Create more rollback segments or
    2. increase the value of transactions_per_rollback_segments

    Thanks


    Thomas
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  2. #2
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327
    I prefer more rollback segments to eliminate contentions.
    Oracle Certified Master - September, 2003, the Second OCM in China
    *** LOOKING for PART TIME JOB***
    Data Warehouse & Business Intelligence Expert
    MCSE, CCNA, SCJP, SCSA from 1998

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    dp you see any contention at all?

  4. #4
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327
    As Oracle recommend to configure one rollback segments for each 4 concurrent transactions. Query V$UNDOSTAT and V$ROLLSTAT to see how well it perform.
    Oracle Certified Master - September, 2003, the Second OCM in China
    *** LOOKING for PART TIME JOB***
    Data Warehouse & Business Intelligence Expert
    MCSE, CCNA, SCJP, SCSA from 1998

  5. #5
    Join Date
    Jul 2000
    Posts
    119
    I think I'll ask same question as pando did, if there is any contention at all ?
    and based on what facts have you decided to increase rollback segments ??
    Obviously, once you are sure of increasing anything and you have proved that increase in rollback segments is really going to help( on development box), go for more rollback segments ( rather than transactions_per_rollback_segments ).
    OCP 8.0, 8i, 9i

  6. #6
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    If you've got header and undo block contention add more Rollback.

    select CLASS, COUNT, TIME
    from v$waitstat
    where CLASS like 'undo%';

    You'll need to monitor the undo rate before making the decision.

    Cheers,
    OCP 8i, 9i DBA
    Brisbane Australia

  7. #7
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727

    Contention, yes there is

    Hi All

    Thanks for your time!!

    Yes, I found the contention on the undo block is gradully incresing. My question is not just based on the number of users(sorry i didn't mention the contention!! ). Also I am experiencing waits in the rollback segments. Also, every week 20-25 new users adding to the system and may go up to 1500. One important thing is, if you are not setting the (oracle 7.3... May be I am the only one DBA struggling with 7.3 in the earth!!) parameter transactions_per_rollback_segment is found ( default value) 72. Don't understand, what is the logic setting the value as 72.

    Any how my ultimate plan to create more rollback segments as compared with the 'Concurrent Transactions' and contention on the RBS blocks.

    Thanks everybody for your replies..


    Thomas
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    Re: Contention, yes there is

    Originally posted by Thomasps
    One important thing is, if you are not setting the (oracle 7.3... May be I am the only one DBA struggling with 7.3 in the earth!!) parameter transactions_per_rollback_segment is found ( default value) 72. Don't understand, what is the logic setting the value as 72.
    That's strange - with every release I've worked so far, the default value for this parameter was 5. Also, I hope you are aware that this setting has absolutely nothing to do with the actual number of concurent transactions each rollback can handle. This parameter is only relevant at startup to help Oracle decide how many of the available rollback segments to acquire and put online.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Jun 2001
    Posts
    40

    Re: Contention, yes there is

    Originally posted by Thomasps


    May be I am the only one DBA struggling with 7.3 in the earth!!

    You're not alone. We have prod databases running 7.1 and 7.3!

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