undo_retention的优化

在生产环境中，尽量减少ORA-1555的出现， 应该将数据库的undo_retention调整到一个合适的值
优化的依据：
You can choose to allocate a specific size for the UNDO tablespace and then set the UNDO_RETENTION parameter to an optimal value according to the UNDO size and the database activity. If your disk space is limited and you do not want to allocate more space than necessary to the UNDO tablespace, this is the way to proceed. The following query will help you to optimize the UNDO_RETENTION parameter:

OPTIMAL UNDO RETENTION=ACTUAL_UNDO_SIZE/(DB_BLOCK_SIZE*UNDO_BLOCK_PER_SEC)

Because these following queries use the V$UNDOSTAT statistics, run the queries only after the database has been running with UNDO for a significant and representative time!

SQL> SELECT SUM(a.bytes) "UNDO_SIZE"
 2 FROM v$datafile a,
 3 v$tablespace b,
 4 dba_tablespaces c
 5 WHERE c.contents = 'UNDO'
 6 AND c.status = 'ONLINE'
 7 AND b.name = c.tablespace_name
 8 AND a.ts# = b.ts#;

UNDO_SIZE
----------
560988160

SQL> SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) "UNDO_BLOCK_PER_SEC" FROM v$undostat;

UNDO_BLOCK_PER_SEC
------------------
    .116666667

SQL> SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]" FROM v$parameter WHERE name = 'db_block_size';

DB_BLOCK_SIZE [KByte]
---------------------
        8192

在磁盘空间紧张的情况下，可以调整undo_retention 
查询undo大小，undo_retention实际大小和优化大小： 

SQL> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
 2 SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
 3 ROUND((d.undo_size / (to_number(f.value) *
 4 g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
 5 FROM (
 6 SELECT SUM(a.bytes) undo_size
 7 FROM v$datafile a,
 8 v$tablespace b,
 9 dba_tablespaces c
10 WHERE c.contents = 'UNDO'
11 AND c.status = 'ONLINE'
12 AND b.name = c.tablespace_name
13 AND a.ts# = b.ts#
14 ) d,
15 v$parameter e,
16 v$parameter f,
17 (
18 SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
19 undo_block_per_sec
20 FROM v$undostat
21 ) g
22 WHERE e.name = 'undo_retention'
23 AND f.name = 'db_block_size'
24 /

ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec]                OPTIMAL UNDO RETENTION [Sec]
------------------------ -------------------------------------------------- ----------------------------
          535 900                                   586971

在磁盘空间空闲的范围内，保证undo_retention不变，调整undo_size

SQL> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
 2 SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
 3 (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
 4 g.undo_block_per_sec) / (1024*1024)
 5 "NEEDED UNDO SIZE [MByte]"
 6 FROM (
 7 SELECT SUM(a.bytes) undo_size
 8 FROM v$datafile a,
 9 v$tablespace b,
10 dba_tablespaces c
11 WHERE c.contents = 'UNDO'
12 AND c.status = 'ONLINE'
13 AND b.name = c.tablespace_name
14 AND a.ts# = b.ts#
15 ) d,
16 v$parameter e,
17 v$parameter f,
18 (
19 SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
20 undo_block_per_sec
21 FROM v$undostat
22 ) g
23 WHERE e.name = 'undo_retention'
24 AND f.name = 'db_block_size'
25 /

ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec]                NEEDED UNDO SIZE [MByte]
------------------------ -------------------------------------------------- ------------------------
          535 900                                .8203125