How To Size UNDO Tablespace For Automatic Undo Management (Doc ID 262066.1)

 

How To Size UNDO Tablespace For Automatic Undo Management (Doc ID 262066.1)To Bottom How To Size UNDO Tablespace For Automatic Undo Management (Doc ID 262066.1)

How To Size UNDO Tablespace For Automatic Undo Management (Doc ID 262066.1)

In this Document

  Goal
  Solution
  References

 

APPLIES TO:

Oracle Database - Enterprise Edition - Version 9.2.0.1 to 11.2.0.4 [Release 9.2 to 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 9.2.0.1 to current release
***Checked for relevance on 04-Feb-2016***



GOAL


To assist Database Administrators in sizing an UNDO Tablespace for automatic undo management.

SOLUTION

Sizing an UNDO tablespace requires three pieces of data.

(UR) UNDO_RETENTION in seconds
(UPS) Number of undo data blocks generated per second
(DBS) Overhead varies based on extent and file size (db_block_size)

The undo space needed is calculated as:

UndoSpace = UR * (UPS * DBS)


Two of the pieces of information can be obtained from the instance configuration: UNDO_RETENTION and DB_BLOCK_SIZE. The third piece of the formula requires a query being run against the database. The maximum number of undo blocks generated per second can be acquired from V$UNDOSTAT.

Note: Overall consideration for peak/heavy vs. normal system activity should be taken into account when peforming the calculations. Autoextend OFF vs. ON will change the behavior for UNDO_RETENTION growth and use of UNEXPIRED extents. See Note 461480.1 for more information.

The following formula calculates the peak undo blocks generated per second:

SQL> SELECT undoblks/((end_time-begin_time)*86400) "Peak Undo Block Generation" FROM v$undostat WHERE undoblks=(SELECT MAX(undoblks) FROM v$undostat);

Column END_TIME and BEGIN_TIME are DATE data types. When DATE data types are subtracted, the resulting value is the # of days between both dates. To convert days to seconds, you multiply by 86400, the number of seconds in a day (24 hours * 60 minutes * 60 seconds).

The following query calculates the number of bytes needed to handle a peak undo activity:

SQL> SELECT (UR * (UPS * DBS)) AS "Bytes"
     FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),
          (SELECT undoblks/((end_time-begin_time)*86400) AS UPS
           FROM v$undostat
           WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat)),
          (SELECT block_size AS DBS
           FROM dba_tablespaces
           WHERE tablespace_name = (SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace'));

For 10g and Higher Versions where Tuned undo retention is being used,please use below query:

SQL>SELECT (UR * (UPS * DBS)) AS "Bytes"

FROM (select max(tuned_undoretention) AS UR from v$undostat),
(SELECT undoblks/((end_time-begin_time)*86400) AS UPS
FROM v$undostat
WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat)),
(SELECT block_size AS DBS
FROM dba_tablespaces
WHERE tablespace_name = (SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace'));

上一篇:redo和undo


下一篇:111111