Oracle Database Discussions

I do work with query to get optimal Undo retention

http://myoracledb.com/Documents/Undo_retention.txt

or read below :

Undo Tablespace Size and Retention Time
Undo data is stored in a logical database structure called an undo tablespace. The undo tablespace is of finite size, so records might be overwritten as transactions occur.

Oracle saves undo data at least until the transaction has been committed. Until this time, the undo data is in the active state. Therefore, the amount of space available in the undo tablespace should be at least large enough to hold the active undo data generated by current transactions. Otherwise, some of these transactions might fail. When active undo data is stored in the undo tablespace, Oracle automatically ensures that it is never overwritten until the corresponding transaction has been committed.

Even after the transaction has been committed, the undo data still cannot be overwritten immediately to ensure the success of Flashback functionality, and for read consistency for long running transactions. For example, if your longest query takes 15 minutes, then the undo tablespace should be large enough to hold 15 minutes worth of undo data.

To control the retention of undo records, Oracle maintains an undo retention period. This period indicates the amount of time that must pass before Oracle overwrites undo data. The undo retention period affects the size of the undo tablespace; the longer the retention period, the more space is needed.

The undo retention period should be at least as long as your longest-running query. By default, Oracle automatically extends the undo tablespace to accommodate the longest-running query based on your ongoing system activity. Nevertheless, you might need to manually increase the size of your undo tablespace in the following circumstances:

When your undo tablespace is set to a fixed size (auto-extend disabled) and long running queries are failing with snapshot too old errors.

When you plan to use Flashback features to recover from user errors such as unintentional changes. In this case, the undo retention should be set equal to the period between the present and the earliest point in time to which you want to return. For more details on Flashback features, see Oracle Database Administrator's Guide.

Hi,

That's very nice explanation given. I think would be much clear about the Concetps. :-)
Addeding Further information.
@OP,

I think, you calcualte based on the size allocated to the Undo tablespace.

Total Undo space / (DB Block size + Undo Block per second)

See, Basially, calcualte how many undo Blocks generates per second, and take the consideration the Block size of persent DB.

You can get details about undo Block per sec from "v$undostat" and rest from v$views

- Pavan Kumar N

Hi,

Khurram also gives a better approach.

855866

Regards!

aps,

in 10g the UNDO_RETENTION is tuned automatically. You can find out the current setting by checking the V$UNDOSTAT.TUNED_UNDORETENTION column.

In 10g if you don't use RETENTION GUARANTEE and have a fixed size UNDO tablespace the setting of UNDO_RETENTION is ignored and the retention is tuned to the maximum setting possible given the workload of your database and the size of your undo tablespace. This can be greater than the defined UNDO_RETENTION but it could also be less, depending on the size and the workload.

If you're using an auto-extensible UNDO tablespace the retention is attempted to be tuned to be larger than the longest running-query encountered in a recent period, provided that there is sufficient space, which could be much larger than the defined UNDO_RETENTION. It's also attempted to to be kept greater or equal than the defined UNDO_RETENTION.

For more information, refer to the documentation which explains this very well:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/logical.htm#sthref454

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/undo.htm#ADMIN10180

10g has a built-in Undo Advisor that you can use to get an advisory regarding the recommended size of your Undo tablespace.

You can use the OEM GUI: http://download.oracle.com/docs/cd/B19306_01/server.102/b14196/storage003.htm#sthref369

or use the PL/SQL interface: http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/undo.htm#sthref1491

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/

In undo Retention you decide how many seconds will the unexpired undo be still be stored..
Unexpired is the data which is held in undo even after commit..

Now to properly set the undo_retention parameter
in 9i you use the following command
select max(maxquerylen) from v$undostat
but in 10g release 2 the
undo_retention is auto tuned.
and ..it also depends on how ur undo tablespace is extendible wether it is auto extensible or not..


You can also use dbms_advisor .. or oem undo advisor after initial load to set the proper undo_retention parameter
and increase the size of undo tablespace..

To avoid snapshot error.. one more thing which can be done is .. set the undo tablespace with guranteed option
so in that scenario the transaction will fail but it gurantees the query to be executed fully ..fullfilling the undo_retention parameter..means the undo of inactive extents .. ie extents which are commited are not over written since it is required by the query..