5 Replies Latest reply on Jan 29, 2013 7:19 PM by Srini Chavali-Oracle

    Doubt regarding undo_retention

      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?

      Any explanation will be highly appreciated.

      Edited by: 918868 on Jan 29, 2013 10:41 AM
        • 1. Re: Doubt regarding undo_retention
          Srini Chavali-Oracle
          Have you looked at the documentation - http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams262.htm#REFRN10225 - I believe it is clear and concise

          • 2. Re: Doubt regarding undo_retention
            Niket Kumar
            Automatic Undo Retention

            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.

            • 3. Re: Doubt regarding undo_retention
              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.

              Kindly let me know your inputs. Thanks in advance
              • 4. Re: Doubt regarding undo_retention
                Niket Kumar
                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...

                Edited by: Niket Kumar on Jan 30, 2013 12:38 AM
                • 5. Re: Doubt regarding undo_retention
                  Srini Chavali-Oracle
                  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

                  1 person found this helpful