How To Size UNDO Tablespace For Automatic Undo Management

Applies to:

Oracle Server Enterprise Edition - Version: 9.2.0.1 to 11.1.0.7
Information in this document applies to any platform.
Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 10.2.0.3

Goal

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

Solution

Sizing an UNDO tablespace requires three pieces of data. 

(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. )

(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)

UndoSpace = [UR * (UPS * DBS)] + (DBS * 24)

Two can be obtained from the initialization file: UNDO_RETENTION and DB_BLOCK_SIZE.
The third piece of the formula requires a query against the database. The number of undo blocks
generated per second can be acquired from V$UNDOSTAT.


The following formula calculates the total number of blocks generated and divides
it by the amount of time monitored, in seconds:

SQL>SELECT (SUM(undoblks))/ SUM ((end_time - begin_time) * 86400)
FROM v$undostat;

Column END_TIME and BEGIN_TIME are DATE data types. When DATE data types are subtracted,
the result is in days. To convert days to seconds, you multiply by 86400, the number of seconds in a day.

The result of the query returns the number of undo blocks per second. This value needs
to be multiplied by the size of an undo block, which is the same size as the database block
defined in DB_BLOCK_SIZE.


The following query calculates the number of bytes needed:

SQL> SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes"
FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),
(SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS FROM v$undostat),
(select block_size as DBS from dba_tablespaces where tablespace_name=
(select upper(value) from v$parameter where name = 'undo_tablespace'));

References

NOTE:461480.1 - FAQ ¿ Automatic Undo Management (AUM) / System Managed Undo (SMU)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11134237/viewspace-627212/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11134237/viewspace-627212/