如何为 Automatic Undo Management 调整 UNDO Tablespace 的大小 (Doc ID 262066.1)

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

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.

帮助数据库管理员调整UNDO表空间的大小以进行自动undo管理

SOLUTION

Sizing an UNDO tablespace requires three pieces of data. 调整UNDO表空间的大小需要三段数据

(UR) UNDO_RETENTION in seconds  UNDO_RETENTION以秒为单位
(UPS) Number of undo data blocks generated per second  每秒生成的undo数据块数
(DBS) Overhead varies based on extent and file size (db_block_size)  开销根据范围和文件大小 (db_block_size) 而变化

The undo space needed is calculated as: 所需的undo空间的计算方式为

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.

可以从实例配置中获得两条信息：UNDO_RETENTION 和 DB_BLOCK_SIZE 。公式的第三部分要求针对数据库运行查询。可以从 V$UNDOSTAT 获取每秒生成的最大undo块数

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.

注意：执行计算时，应考虑高峰/重负荷与正常系统活动的总体考虑。自动扩展OFF与ON会改变 UNDO_RETENTION 增长和使用 UNEXPIRED 范围的行为。有关更多信息，请参见Note 461480.1。

The following formula calculates the peak undo blocks generated per second:  以下公式计算每秒生成的最大undo块

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

列 END_TIME 和 BEGIN_TIME 是DATE数据类型。减去DATE数据类型后，结果值为两个日期之间的天数。要将天数转换为秒数，请乘以一天中的秒数(24 hours * 60 minutes * 60 seconds) 乘以 86400

The following query calculates the number of bytes needed to handle a peak undo activity:  以下查询计算处理最大undo活动所需的字节数

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: 

对于使用已调整 undo retentio n的10g及更高版本，请在以下查询中使用

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'));