Upgrade to 10gR2 and Undo Retention Changes

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.