Re: undo retention vs performance

From: Tim Gorman <tim@xxxxxxxxx>
To: Mathieu.Braekeveld@xxxxxxxxxx
Date: Wed, 24 Mar 2010 12:53:44 -0600

Mathieu,

Quote: “If you have too high of undo retention parameter, you need to have the corresponding big enough space to hold all the info. If not the performance will be hampered.”

This comment was OK until the 2nd sentence. If you do not have enough space in UNDO, then performance won't be hampered, you'll get errors. You won't necessarily get out-of-space errors, but as inactive-but-unexpired undo blocks are overwritten, you might start getting ORA-01555 ("snapshot too old") errors. Whether you get out-of-space or snapshot-too-old errors, performance is not hampered, rather it is eliminated, as such errors generally cause programs to terminate.

To improve performance, tune the SQL and the application, not the undo. Configure undo to avoid undo-related errors.

Hope this helps...

Tim Gorman
consultant -> Evergreen Database Technologies, Inc.
postal     => P.O. Box 630791, Highlands Ranch CO  80163-0791
website    => http://www.EvDBT.com/
email      => Tim@xxxxxxxxx
mobile     => +1-303-885-4526
fax        => +1-303-484-3608
Lost Data? => http://www.ora600.be/ for info about DUDE...

Braekeveld, Mathieu wrote:

Hi,

Can somebody please give me a clear answer and explanation to this?

Some people say it would affect my performance, others say that it wouldn’t…

I quote: “If you have too high of undo retention parameter, you need to have the corresponding big enough space to hold all the info. If not the performance will be hampered.”

But i don't know what to believe, the people that say it wouldn't have influence on my performance say this is beacause the space will be just overwritten. But doesn’t it take time to re-allocate the space then or something??

Here is some extra information:

Db Version 9.2.0.5.0

UNDO INFO:

----------

undo_management      : AUTO

undo_tablespace      : UNDO01

undo_suppress_errors : FALSE

undo_retention       : 96000

UNDO SIZE (MB) UNDO RETENTION (SEC) OPTIMAL UNDO RET.(SEC) NEEDED UNDO SIZE (MB)

-------------- -------------------- ---------------------- ----------------------

         65535                96000                  26748              235206,25

This information is gathered by some queries i ran… (also the optimal retention/size)

You see the undo size is way too small for the undo_retention parameter (maxsize of undo tablespace is already reached).

(Ps this database really has some performance issues.)

Thanks for your help!

Best regards,

Mathieu

-----Oorspronkelijk bericht-----

Van: Gu Eileen [mailto:Eileen.Gu@xxxxxxxxxxxxxxx]

Verzonden: maandag 22 maart 2010 17:42

Aan: Braekeveld, Mathieu

Onderwerp: RE: undo retention parameter

If you have too high of undo retention parameter, you need to have the corresponding big enough space to hold all the info. If not the performance will be hampered.

-----Original Message-----

Subject: undo retention parameter

Hi,

Question: Can a too high value in the undo retention parameter cause performance problems?

And if so, why is that?

Thanks for your help!
This e-mail is personal. For our full disclaimer, please visit www.centric.eu/disclaimer.

-- //www.freelists.org/webpage/oracle-l

References:
- undo retention vs performance
  - From: Braekeveld, Mathieu

Re: undo retention vs performance

Other related posts: