DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Impact of Increasing undo_retention

  1. #1
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763

    Impact of Increasing undo_retention

    Hi,

    I know about the first thing to have in mind related to increase undo_retention (recalculate the undo tablespace size to accomodate the new state)

    Has anybody other recommendations?

    Thanks and best regards

    Angel

  2. #2
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    undo_retention should be set longer than your longest open cursor. That's probably your longest running job/sql/plsql block.

    Make the undo tablespace autoincrement, so you don't have to rerun your longest job when you get there.

    If you have one huge job and everything else is small you'll have to keep one big undo tablespace. This is my preference since disks (even the real fast ones) are relatively cheap.

    If you want to recover the space;
    1) create a new undo tablespace
    2) "alter system set undo_tablespace the_new_tablespace"
    3) drop the old (don't worry, it won't drop till all the transactions are committed).

  3. #3
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763

    Talking

    Maybe, I didn't explain enough.

    We hace actually an 8Gb undo tablespace an 3600 (yes I know is little little little (I'm just arriving to this place :-) defined for undo_retention.

    The problem is we have some queries that fire ora-1555. Developer don't want, don't know blablabla review this processes and demand us to increase undo_retention.

    select max(MAXQUERYLEN) from v$undostat;
    22584

    That it's about 7 hours of retention.

    I know this increasing affect directly to undo tablespace size , that will be increased from 8Gb to 15Gb using oracle's calculation method. Any other considerations to have in mind (I have to write an impact document :-)

    Regards

    Angel

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Check the code before increasing. May be they are doing fetch across commits? Or modifying the table of the cursor?

  5. #5
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    Sure Pando, but they didn't want to change the code (for the moment). it is for this my boss has asked me to write an impact document of increase undo_retention.

    Cheers

    Angel

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    If they are doing fetch across commit they will always get snapshot too old.

    The only impact is you need to have probably 50GB UNDO TABLESPACE!

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by pando
    If they are doing fetch across commit they will always get snapshot too old.

    The only impact is you need to have probably 50GB UNDO TABLESPACE!
    That may still not be enough
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    The other impact can be, THE DEVELOPERS will never know how to use a proper way to avoid it!

  9. #9
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763

    Talking

    :-D

    Thanks

    Angel

  10. #10
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    And Mr. Hanky Will need to clean his guns
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

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