Upgrade to 10gR2 and Undo Retention Changes

Jun 19, 2007 / By Alex Gorbachev

Tags:

Alex Fatkulin has already mentioned this on our blog a while ago and reminded me once again yesterday. I think some other bloggers have pointed it out too, but I want to emphasize it here again, since Oracle 10.2 release is getting more and more popular in production settings. It’s especially useful to look on it vis-a-vis upgrades. In addition, there is a small gotcha that might cause performance problems in certain extreme cases.

After an upgrade to 10gR2, you might observe that an Oracle instance uses more UNDO space. Apparently, this is the result of a change in behavior when AUTOEXTEND for UNDO datafiles is disabled. For some, this change caused a bit of confusion — was this a bug with non-autoextensible UNDO tablespaces or some kind of special optimization for autoextensible UNDO tablespaces?

In fact, this confusion is cleared up simply by looking into Oracle’s documentation:

UNDO_RETENTION specifies (in seconds) the low threshold value of undo retention. For AUTOEXTEND undo tablespaces, the system retains undo for at least the time specified in this parameter, and automatically tunes the undo retention period to satisfy the undo requirements of the queries. For fixed- size undo tablespaces, the system automatically tunes for the maximum possible undo retention period, based on undo tablespace size and usage history, and ignores UNDO_RETENTION unless retention guarantee is enabled.

A simple example. You had the UNDO_RETENTION parameter set to 900 seconds by default, and upgraded to 10.2 from 10.1 or 9i. Before the upgrade, UNDO space consumption was at most 1 GB of the 5 GB that is allocated to the UNDO tablespace. After it, if your UNDO tablespace is not autoextensible, you would observe that the whole UNDO tablespaces is used. This is a legitimate behavior because Oracle will basically ignore the undo_retention parameter and keep UNDO as long as possible.

Since the UNDO tablespace might now become full unexpectedly, you might consider excluding it from monitoring, as this change in behavior can confuse your monitoring tools. One smart workaround: enable AUTOEXTEND for the UNDO tablespace, and set MAXSIZE to the current size. This way, Oracle will stick to undo_retention parameter again. There’s very little need for this, though, since you might well want to use all the space allocated and have the benefits of a longer retention period, such as the ability to do flashback queries for a longer period.

However, there is at least one exception when you might want Oracle to use the pre-defined undo_retention instead of all the available space in the UNDO tablespace. Slightly more than a year ago, I encountered one related performance problem. At some time during peak hours, database performance started to degrade with a lot of time spent on enqueue wait. The “guilty” enqueue was US — Undo Segment Serialization. After some investigation, I found that at the time of the problem, UNDO tablespace was full.

In this case Oracle, has to age out older UNDO entries so that it can reuse that space. I don’t know the exact algorithm by which Oracle finds the oldest UNDO slots and releases them, but it seems to have some serialization point. Under a heavy OLTP load, it caused troubles, as many concurrent processes are trying to expire and reuse UNDO space. Perhaps this has improved in 10g, so if someone has more details — please let us know.

4 Responses to “Upgrade to 10gR2 and Undo Retention Changes”

  • Shervin says:

    Let me add some comments from 10g Admin doc :
    Under certain circumstances, you must set the undo retention period by setting the
    UNDO_RETENTION initialization parameter.
    The only time you must set this parameter is when:
    â–  The undo tablespace has the AUTOEXTEND option enabled
    â–  You want to set undo retention for LOBs
    â–  You want retention guarantee
    In all other cases, this parameter is ignored, and the database automatically tunes for maximum undo retention

  • Comment from 10g admin doc :

    Under certain circumstances, you must set the undo retention period by setting the
    UNDO_RETENTION initialization parameter. The only time you must set this parameter
    is when:
    â–  The undo tablespace has the AUTOEXTEND option enabled
    â–  You want to set undo retention for LOBs
    â–  You want retention guarantee

    In all other cases, this parameter is ignored, and the database automatically tunes for maximum undo retention.

  • Venkat.S says:

    Hi,
    In our cutomer production box the undo tablespace size is 200GB and it is now only having 6 GB free space. There are no active transactions and and the UNDO_RETENTION period is 8 hours. But the problem is the UNDO space is not geting freed up after 2 days also. 2 days before the used space is 193 GB and still it is in the same position. The UNDO_RETENTION period is 8 hours and the EXTENT_MANAGEMENT is Manual. Please suggest how to free up the space ?

  • Venkat, I suggest you re-read the post once again. You’d find there the answer to your question.

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>