如何设置UNDO大小

Posted by 道行尚浅 on September 14, 2014

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