-
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
-
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).
-
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
-
Check the code before increasing. May be they are doing fetch across commits? Or modifying the table of the cursor?
-
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
-
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!
-
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"
-
The other impact can be, THE DEVELOPERS will never know how to use a proper way to avoid it!
-
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|