UNDO TABLESPACE 大小设置

关于Ora-01555这个问题，网上已经有很多讨论了，感兴趣的同学自己搜索下。

那么对于此问题的解决无外乎以下两种

1 减少查询时间

2 增加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;
/