shrinking roll back segments
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: shrinking roll back segments

  1. #1
    Join Date
    Oct 2000
    Posts
    90

    Thumbs up

    When will you consider the best time to shrink roll back segments? I mean what signs do you have to see to consider shrinking your roll back segments?

    dorothy

  2. #2
    Join Date
    Jan 2001
    Posts
    126
    Hi,

    Set optimal and let ORACLE do it for you. It can also be done when the segment is not in use.

    Baliga

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Only time I had to shrink manually is to resize my datafiles, normally if you set optimal Oracle will do it automatically, anyway during transaction intensive times roll back segment shouldnt be shrink and extended all the time to avoid perfomance issues

  4. #4
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Best bet is setting up optimal parameter, it will grow per transaction requirement ans shrink to optimal once the transaction is done.

  5. #5
    Join Date
    Oct 2000
    Posts
    90
    Thanks for the responses. But my question has not been answered yet. The question is why should a roll back segment be shrunk(either manually or setting the optimal parameter). I mean what are the advantages?


    thanks,
    dorothy

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you asked when are the time to do it and I think most of us tried to answered that question, now you wish to know the advantages, there arent any. Setting optimal is simply to avoid potential errors

    For example you have 4 RBS of 5MB each with minextents set to 5, maxextents set to unlimited, initial extent 1MB and next extent to 1MB, and the datafile that composes tablespace RBS is 100MB:

    for whatever reason, a huge transaction for example,

    RBS1 extends from 5 extents to 40 extents, 40MB in size
    RBS2 extends from 5 extents to 40 extents as well, 40MB in size
    RBS3 and RBS4 stayed at 5MB size

    So basically now 90MB of datafile is filled, if there is another transaction coming in which is pretty large and you have not started the transaction by using

    set transaction using rollback segment XXX command

    then the transaction could be assigned by Oracle to use RBS3, RBS4 or RBS1 or RBS2 depending on number of concurrent transactions in each RBS, imagine that this transaction is assigned to RBS3.
    Since the transaction is big and RBS3 is relatively small you will get errors before this transaction finishes because the datafile can not extend (of course if you have not set autoextend on in datafile) and you might think "Oh this cant be true, I have 100MB assigned for RBS purposes, this transaction cant be so large"
    Right this is true, RBS1 and RBS2 have more than enough spaces to accomodate this transaction however the time you started the transaction there might be a few small transactions going in RBS1, RBS2 and RBS4, and RBS3 none and that's why Oracle assigned RBS3 to your transaction. Imagine if you set optimal in RBS, once those big transactions have finished in RBS1 and RBS2 they would be shrink back to 5MB and in that case you would not get errors as above since they would be space in the datafile for RBS3 to extend.

    Does this help?



    [Edited by pando on 02-04-2001 at 04:14 PM]

  7. #7
    Join Date
    Oct 2000
    Posts
    90
    thanks pando. Very clear now

  8. #8
    Join Date
    Nov 2000
    Posts
    212
    Ok, but what about forcing Oracle to shrink and getting ora-01550: snapshot to old? I mean the case when Oracle shrinks a large transaction which has query on it.
    Or optimal has nothing to do with it?


  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well that comes in the tuning of your Rollback Segments no?

  10. #10
    Join Date
    Nov 2000
    Posts
    212
    may be during tuning, but then it looks like 'set transaction use rollback segment' is quite important and probably the only way to have some control of large transactions.

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