Impact of Increasing undo_retention

[aarroyob]

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

[KenEwald]

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).

[aarroyob]

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

[pando]

Check the code before increasing. May be they are doing fetch across commits? Or modifying the table of the cursor?

[aarroyob]

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

[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!

[abhaysk]

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

[pando]

The other impact can be, THE DEVELOPERS will never know how to use a proper way to avoid it!

[aarroyob]

:-D

Thanks

Angel

[simply_dba]

And Mr. Hanky Will need to clean his guns