关于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;
/