DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Calculating Rollback Segments

  1. #1
    Join Date
    Feb 2001
    HI DBA's
    I am creating a new Database.Can anyone of you tell me how many rollback segments I should have for optimum performance.
    I tried rule of four but that does not work.
    Thanx in Advance

  2. #2
    Join Date
    Jun 2000
    Madrid, Spain
    what do you mean that doesnt work...? You start from there then you can tune if you face contention

  3. #3
    Join Date
    Feb 2001
    Agrred.Updations are taking a lot of time.I have heard that Kevin Loney suggets a Mathematical method for creating Rollback segments.Can you help me out on this?

  4. #4
    Join Date
    Nov 2000
    I have no idea if I can publish this here, but anyway (I think this supports practical&scientific approach. Purely scientific approach usually assumes availability of some data that is hard to have and is build for particular version of software):

    e-mail reply to oaugnet-dba@lists.ksinet.com
    Author: Mogens Nørgaard

    Probably someone has already replied. Anyway, here are my suggestions:

    3. Regarding number of rollback segments, etc.: It's like asking "How many bags should I bring to the supermarket?". Well, it depends on the things you want to buy at the
    supermarket...and it's the same with rollback segments. What are the users going to do? I've studied this topic for many years, and unfortunately there IS no answer - only
    guesswork, first attempts, observations for problems, re-sizing.

    There are three schools of thought that I've encountered in my life when it comes to number and size of rollback segments:

    A. The rule of thumb school: In the first books (V6) they said: Four transactions per rollback segment". In later materials, including the 7.3 tuning class from Oracle World Wide
    Education, Anthea West suggested 10 transactions per rollback segments. I asked her why, and her reply was something similar to "I had to write something, because people expect us
    to give advise on this". True, but 4, 10, 17 or 42 can be equally good numbers. It depends (more about that later) on contention in the segment header block.

    B. The benchmark school: One small (10K or so) rollback segment per user on the system. Small enough to be cached in the buffer cache, never any contention, etc. Not for use in
    real systems, of course. And probably just a waste of time anyway.

    C. The bitter, old men school of though: This train of thought was introduced to me by Andre Bakker, who left Oracle a year or two ago, but was one of the real titans in the
    Oracle community. As a VP for Support in EMEA he still held Oracle Internals classes - respect! He suggested that most systems could use the following formula: Start out with one
    big rollback segment. Monitor it for contention in the segment header block (more about that in a minute). If there indeed is contention or 1555-errors (snapshot too old), well,
    then create another big rollback segment .

    So how many? Actually, Andre (in my opinion) had a very important point: If there's no contention for the segment header block in your rollback segments then you don't need more
    of them. The segment header block contains a transaction table, which contains information about each active transaction in the rollback segment. When too many processes are
    updating and querying this transaction table, they have to wait - you get contention. That's how you decide if you have too few or too many rollback segments. Check for instance
    v$waitstat for number of waits (and wait time) on undo segment header blocks - this will tell you whether there has been any waiting for header blocks. If not, you can relax about
    number of rollback segments.

    There's no significant limit to the number of concurrent users that can use a rollback segment (I think it's something like 77 or so if you have 2K blocks, and so on, depending on
    your block size). The three problems you can have with rollback segments are:

    Contention in the segment header block - this will quickly show up as special waits (share mode 6? I cannot remember) for enqueues in the wait-interface, which you of course use
    for monitoring performance in your system.

    IO-problems - this will show up in the wait-interface and a look at readtim/phyrds in v$filestat will show you if your rollback tablespace files have expensive IO's.

    Ora-1555/snapshot too old: Depends what the reason for it is. Usually you need bigger rollback segments (this is a combination of number of extents and size of each extent of

    I don't think I've ever encountered more than one system in my 14 year Oracle career that had too few rollback segments. The default, for instance, on Oracle7.3 for NT, was 17 -
    seventeen! - rollback segments. They were defined as public, too. Most systems have way too many rollback segments, but it doesn't hurt to have too many, so it's fine with me.

    Snapshot too old - that is an old friend. It should be much easier to avoid it in 9i when rollback segments go away and are replaced by socalled undo tablespaces. There will be
    other things to consider then, however: How long will your longest-running transaction be, for instance? Again: It depends .

    I'll look forward to some feedback on this long and winding story .

  5. #5
    Join Date
    Nov 2000
    That long reply is really good !

    I've been through the rollback problems a couple of times and I use the following scripts to check contention and wraps/extends. It seems like you have to make enough of them to fight contention and then try to size them correctly to avoid wraps/extends and shrinks. Bit of a pain in the ass but it keeps Oracle on the high paid geek fear level :-)

    select name, OptSize, shrinks, AveShrink, Wraps, Extends
    from v$rollstat, v$rollname
    where v$rollstat.usn=v$rollname.usn;

    and this script from timonions.com:


    REM Script created by Tim Onions, June 1998
    REM You are free to use this script (although no guarentees are made or liability accepted from the author)
    REM on one condition - use it to improve the performance of your database!

    prompt Rollback Segment Section
    prompt if any count below is > 1% of the total number of requests for data
    prompt then more rollback segments are needed

    --column count format 999,999,999
    select class, count
    from v$waitstat
    where class in ('free list','system undo header','system undo block',
    'undo header','undo block')
    group by class,count;

    column "Tot # of Requests for Data" format 999,999,999
    select sum(value) "Tot # of Requests for Data" from v$sysstat where
    name in ('db block gets', 'consistent gets');
    prompt =========================
    prompt =========================
    prompt If any ratio is > .01 then more rollback segments are needed

    column "Ratio" format 09.99999
    select name, waits, gets, waits/gets "Ratio"
    from v$rollstat a, v$rollname b
    where a.usn = b.usn;

    Prompt Overall gets .v. waits...

    select 'GETS - # of gets on the rollback segment header: '||sum(gets)||CHR(10)||
    'WAITS - # of waits for the rollback segment header: '||sum(waits)||
    CHR(10)||'The ratio of Rollback waits/gets is '||
    round((sum(waits) / (sum(gets) + .00000001)) * 100,2)||'%'||CHR(10)||CHR(10)||
    'NB If ratio is more than 1%, create more rollback segments'
    from v$rollstat;


    Oracle 8i "dbassist" java db creator seems to make generous rollbacks based on the "connection" levels that you specify. If you use "dbassist" remember to save the result as a script and run it manually- CHECK the scripts first to see what your getting.


  6. #6
    Join Date
    Feb 2001
    HI LND and RogerF,
    I am thoroughly impressed by your detail replies.But I want to have more discussions with you regarding this.Can you give me your e mail ids so that I can discuss you some more things.

  7. #7
    Join Date
    Nov 2000
    LND is lnd@hnit.is

  8. #8
    Join Date
    Feb 2001
    Thanks LND.I will be in touch with u

  9. #9
    Join Date
    Nov 2000

    You can contact me about the Rollbacks- but I think if you read the 2 responces, read a chapter in an Oracle book(Oracle Bible=good) and test a few times, you'll know as much as anybody "wants" to know about them. I think that Oracle 8i gives a heathy set of Rollbacks with the dbassist install.



  10. #10
    Join Date
    Feb 2001
    Thanks Roger for your unalloyed support

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