Friends, suppose we are using automatic undo management. Let's say we have fixed sized undo tablespace named 'undots' , we set undo_retention = 300 i.ie undo retention period is 5 minutes but as per Oracle docs, Oracle automatically optimized undo_retention parameter value internally based on tablespace size and undo generation rate of the database for fixed sized tablespaces, so where lies the significance of 'undo_retention' parameter?
Oracle Database 10g automatically tunes a parameter called the undo retention period. The undo retention period indicates the amount of time that must pass before old undo information—that is, undo information for committed transactions—can be overwritten. The database collects usage statistics and tunes the undo retention period based on these statistics and on undo tablespace size. Provided that automatic undo management is enabled, the database automatically tunes the undo retention period as follows:
For an AUTOEXTEND undo tablespace, the database tunes the undo retention period to be slightly longer than the longest-running query, if space allows. In addition, when there is adequate free space, the tuned retention period does not go below the value of the UNDO_RETENTION initialization parameter.
For a fixed size undo tablespace, the database tunes for the maximum possible undo retention. This means always providing the longest possible retention period while avoiding out-of-space conditions and near out-of-space conditions in the undo tablespace. The UNDO_RETENTION initialization parameter is ignored unless retention guarantee is enabled.
Automatic tuning of undo retention is not supported for LOBs. The tuned retention value for LOB columns is set to the value of the UNDO_RETENTION parameter.
For fixed size and AUTOEXTEND undo tablespaces of equal size, depending on the queries that you run, the tuning method used in fixed size tablespaces tends to provide a longer retention period. This enables flashback operations to flash back farther in time, and maximizes the amount of undo data available for long-running queries.
Shrini, I read the documentation, check the relevant line from doc "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" , in my doubt case, we have fixed size undo tablespace and undo retention is not guaranteed, then as seen above oracle will ignore UNDO_RETENTION parameter, if it will ignore this parameter what is the necessity of setting up of this parameter when undo tablespace is fixed size and undo retention is not guaranteed.
it means if you have fixed undo tablespace then the undo blocks are overwritten by new undo block as per space requirement in this condition undo blocks will not wait for undo retention to free up blocks from undo tablespace...
If you have a fixed UNDO tablespace size, Oracle will do its best to retain undo for at least the amount of time specified by this parameter.
If you do not explicitly set this parameter, it defaults to 900 seconds (15 min) in 11gR2.
So if you have a small UNDO tablespace, you are likely to run into ORA-01555 (snapshot too old) errors - if you have a large UNDO tablespace, then undo retention will likely exceed this parameter value (although there are no guarantees).
Oracle needs a value to be set to act as a "barometer" - default is 15 min