关于Ora-01555这个问题，网上已经有很多讨论了，感兴趣的同学自己搜索下。
那么对于此问题的解决无外乎以下两种
-
减少查询时间
-
增加undo_tablsespace 大小
减少查询时间,优化的事情，本文不做讨论
本文想要讨论的是undo_tablsespace 设置多大合适。
Oracle提供了一个非常不错的视图 v$undostsat
v$undostat 根据官方文档 http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_3118.htm#REFRN30295 Each row in the view keeps statistics collected in the instance for a 10-minute interval.The view contains a total of 576 rows, spanning a 4 day cycle.
每十分钟生成一行，保留576行 即四天的数据
| Column | Datatype | Description |
|---|---|---|
| BEGIN_TIME | DATE | 定义时间段起始 |
| END_TIME | DATE | 定义时间段结束 |
| UNDOTSN | NUMBER | 时间段内最后活动的undo tablespace的编号 |
| UNDOBLKS | NUMBER | undo block 消耗总数 |
| TXNCOUNT | NUMBER | 时段内发生多少个事务 |
| MAXQUERYLEN | NUMBER | 时段内运行的sql最长执行时间，此参数可以用来衡量undo_retention设置是否合理 |
| MAXQUERYID | VARCHAR2(13) | 运行时间最长的SQL ID |
| MAXCONCURRENCY | NUMBER | 时段内最大事务并发数 |
| UNXPSTEALCNT | NUMBER | 试图从其他unexpired extents 偷取undo space的次数 |
| UNXPBLKRELCNT | NUMBER | 为被其他事务使用 某一undo segment 删除的unexpired blocks数量 |
| UNXPBLKREUCNT | NUMBER | 事务重用unexpired undo blocks的数量 |
| EXPSTEALCNT | NUMBER | 从其他回滚段偷取expired undo blocks 的数量 |
| EXPBLKRELCNT | NUMBER | 从从其他回滚段中偷取expired undo blocks 的数量 |
| EXPBLKREUCNT | NUMBER | 被同一回滚段重用的expired undo blocks的数量 |
| SSOLDERRCNT | NUMBER | 时间段内ora-01555发生的次数 |
| NOSPACEERRCNT | NUMBER | 时段内undo tablespace 用尽，无法获取数据的次数 |
| ACTIVEBLKS | NUMBER | 本实例时段内active extent undo block的总数 |
| UNEXPIREDBLKS | NUMBER | 本实例时段内 unexpired extent undo block的总数 |
| EXPIREDBLKS | NUMBER | 本实例时段内 expired extent undo block的总数 |
| TUNED_UNDORETENTION | NUMBER AUM 自动调整UNDO_RETENTION的参数值，用以确定提交后的undo block是否能清理 |
根据以上视图很容易算出undo tablespace size
metalink 给出的公式是
UR =undo_retention参数值 UPS= 每秒产生的undo block数量 DBS = undo tablespace block size
10g之前
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'));
10g以后
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'));
当然也可以用过DBMS_ADVISOR来获取Oracle关于undo的建议设置，当然不是每次都能获取到建议，相比还是上面提供的脚本比较方便。 下面语句来自于官方文档 我稍微做了一点修改 其中123 124是snapshot的ID 需要根据实际做修改。
SET SERVEROUTPUT ON
DECLARE
tid NUMBER;
tname VARCHAR2( 30);
oid NUMBER;
BEGIN
DBMS_ADVISOR.CREATE_TASK('Undo Advisor' , tid, tname, 'Undo Advisor Task' );
DBMS_ADVISOR.CREATE_OBJECT(tname, 'UNDO_TBS' , null, null, null, 'null', oid);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'TARGET_OBJECTS' , oid);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'START_SNAPSHOT' , 123);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'END_SNAPSHOT' , 124);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'INSTANCE' , 1);
DBMS_ADVISOR.execute_task(tname);
DBMS_OUTPUT.put_line( 'select * from dba_advisor_log WHERE TASK_ID ='||tid || ' ;');
DBMS_OUTPUT.put_line( 'select * from DBA_ADVISOR_FINDINGS WHERE TASK_ID ='||tid|| ' ;');
DBMS_OUTPUT.put_line( 'select * from DBA_ADVISOR_RECOMMENDATIONS WHERE TASK_ID ='||tid||' ;');
END;
/