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