【Oracle】undo 自動調優

楊奇龍發表於2011-10-16
Oracle
   Oracle 10gr2的後續版本中新增了UNDO資訊最短保留時間段自動調優的特性，不再僅僅依據引數UNDO_RETENTION的設定，其調優原則如下：
1 當UNDO TABLESPACE為 fixed- size，Oracle將根據表空間的大小和歷史使用情況，自動調整undo資訊儲存時間，同時忽略 undo_retention的值除非 undo_retention的guarantee 特性被啟用。
2 當UNDO TABLESPACE為AUM時，Oracle將動態調整撤銷資訊最短保留時間為該時段最長查詢時間(MAXQUERYLEN)加上300秒或引數UNDO_RETENTION間的較大者，即MAX((MAXQUERYLEN+300),UNDO_RENTION)；
   在自動調整啟用的情況下，實際的撤銷資訊最短保留時間可以透過查詢V$UNDOSTAT檢視上的TUNED_UNDORETENTION列獲得。往往最短儲存時間遠遠大於設定的UNDO_RETENTION。在無法就UNDO TABLESPACE做相應修改的情況，可以透過修改隱式引數”_UNDO_AUTOTUNE”為FALSE關閉該自動調優特性。以上設定生效後，V$UNDOSTAT檢視上TUNED_UNDORETENTION列不再更新，且撤銷資訊最短保留時間固定為引數UNDO_RETENTION的設定值。該引數可以不用重啟資料庫而動態設定生效。
下面做實驗說明undo自動調整的功能以及其弊端：注：實驗環境中無他事務。
進行一個dml 語句不提交，檢視dba_undo_extents 關於回滾段的資訊，
YANG@yangdb-rac3> show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     60
undo_tablespace                      string      UNDOTBS1

YANG@yangdb-rac3> update bind set status='VALID' where wner='YANG';
5 rows updated.
YANG@yangdb-rac3> select segment_name ,tablespace_name,extent_id,status
  2      from dba_undo_extents
  3      where status='ACTIVE';

SEGMENT_NAME         TABLESPACE_NAME  EXTENT_ID STATUS
-------------------- --------------- ---------- ---------
_SYSSMU3_2097677531$ UNDOTBS1                 2 ACTIVE
YANG@yangdb-rac3> commit; 
Commit complete.
YANG@yangdb-rac3> --等待一分鐘之後
YANG@yangdb-rac3> select segment_name ,tablespace_name,extent_id,status
  2     from dba_undo_extents
  3     where status='ACTIVE';

SEGMENT_NAME         TABLESPACE_NAME  EXTENT_ID STATUS
-------------------- --------------- ---------- ---------
_SYSSMU3_2097677531$ UNDOTBS1                 2 UNEXPIRED 

YANG@yangdb-rac3>  --等待一分鐘之後
YANG@yangdb-rac3> select segment_name ,tablespace_name,extent_id,status
  2     from dba_undo_extents
  3     where status='ACTIVE';

SEGMENT_NAME         TABLESPACE_NAME  EXTENT_ID STATUS
-------------------- --------------- ---------- ---------
_SYSSMU3_2097677531$ UNDOTBS1                 2 UNEXPIRED 
可以看到提交一分鐘之後，回滾段_SYSSMU3_2097677531$的狀態依然為UNEXPIRED，儘管undo_retention 設定為60s。本應該釋放的undo卻未被及時釋放。其實這也是為什麼生產環境中undo表空間總是接近100%的原因。
由之前的介紹oracle提供的undo自動調優技術，只是將undo_retention做為一個參考值，而實際設定的undo_retention時間有v$undostat.tuned_undoretention 而定，檢視其資訊；  
YANG@yangdb-rac3> ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS' ;
Session altered.
YANG@yangdb-rac3> SELECT begin_time, end_time, tuned_undoretention FROM v$undostat;
BEGIN_TIME          END_TIME            TUNED_UNDORETENTION
------------------- ------------------- -------------------
2011/10/16 19:46:29 2011/10/16 19:56:29          1412 --進行查詢時候的保留時間明顯大於60s
2011/10/16 19:36:29 2011/10/16 19:46:29          2017 
2011/10/16 19:26:29 2011/10/16 19:36:29          1416
2011/10/16 19:16:29 2011/10/16 19:26:29          2018
2011/10/16 19:06:29 2011/10/16 19:16:29          1418
2011/10/16 18:56:29 2011/10/16 19:06:29          2022
2011/10/16 18:46:29 2011/10/16 18:56:29          1421
2011/10/16 18:36:29 2011/10/16 18:46:29          2026
2011/10/16 18:26:29 2011/10/16 18:36:29          1425
2011/10/16 18:16:29 2011/10/16 18:26:29          2028
。
。
。
464 rows selected.
every coin has two sides,UNDO自動最佳化功能能夠最大限度的使用undo表空間,滿足大部分的sql執行，但是也帶來一個問題：很多事務執行完畢之後，發現UNDO表空間會在很長時間都一直保持著使用率是接近100%的狀態，active 狀態的很少。
這種接近狀態還無法手工的收縮，甚至於重啟資料庫例項也無法緩解，而此時常常會收到undo表空間的監控報警。
可以透過修改隱式引數” _UNDO_AUTOTUNE”為FALSE關閉該自動調優特性。以上設定生效後，V$UNDOSTAT檢視上TUNED_UNDORETENTION列不再更新，且撤銷資訊最短保留時間固定為引數UNDO_RETENTION的設定值。該引數可以不用重啟資料庫而動態設定生效。
禁用UNDO自動最佳化之後，Oracle不再的每十分鐘記錄一次當前UNDO使用情況了，在動態檢視V$UNDOSTAT中也只保留禁止undo自動調優之前的資料：
YANG@yangdb-rac3> conn /as sysdba
Connected.
SYS@yangdb-rac3> alter system set "_undo_autotune"=false;
System altered.
YANG@yangdb-rac3>conn yang/yang
Connected.
YANG@yangdb-rac3>--10分鐘之後
YANG@yangdb-rac3>SELECT count(1) FROM v$undostat;
  COUNT(1)
----------
       464 --還是之前的個數
YANG@yangdb-rac3>SELECT begin_time, end_time, tuned_undoretention FROM v$undostat where rownum =1;
BEGIN_TIME          END_TIME            TUNED_UNDORETENTION
------------------- ------------------- -------------------
2011/10/16 19:56:29 2011/10/16 20:08:11                1592 
---
YANG@yangdb-rac3> SELECT begin_time, end_time, tuned_undoretention FROM v$undostat where rownum<2;
BEGIN_TIME          END_TIME            TUNED_UNDORETENTION
------------------- ------------------- -------------------
2011/10/16 19:56:29 2011/10/16 21:08:53                1592
上面兩個是在不同時間進行的查詢， v$undostat的記錄不足每十分鐘進行一次統計。
再次做一個實驗：這次事務提交一分鐘之後，undo段的狀態變為EXPIRED
YANG@yangdb-rac3> UPDATE bind set status ='INVALID' where status='VALID';
72747 rows updated.
YANG@yangdb-rac3> ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS' ;
Session altered.
YANG@yangdb-rac3> select sysdate from dual;
SYSDATE
-------------------
2011/10/16 20:13:41
YANG@yangdb-rac3> commit;
Commit complete.
--提交時立即做查詢：
YANG@yangdb-rac3> select segment_name ,tablespace_name,extent_id,status
  2     from dba_undo_extents
  3     where segment_name='_SYSSMU3_2097677531$' or status='ACTIVE';

SEGMENT_NAME         TABLESPACE_NAME  EXTENT_ID STATUS
-------------------- --------------- ---------- ---------
_SYSSMU9_1424341975$ UNDOTBS1                 0 ACTIVE  --活動狀態
_SYSSMU9_1424341975$ UNDOTBS1                 1 ACTIVE
_SYSSMU9_1424341975$ UNDOTBS1                 2 ACTIVE
_SYSSMU9_1424341975$ UNDOTBS1                 3 ACTIVE
_SYSSMU9_1424341975$ UNDOTBS1                 4 ACTIVE
_SYSSMU9_1424341975$ UNDOTBS1                 5 ACTIVE
_SYSSMU9_1424341975$ UNDOTBS1                 6 ACTIVE
_SYSSMU9_1424341975$ UNDOTBS1                 7 ACTIVE
_SYSSMU9_1424341975$ UNDOTBS1                 8 ACTIVE
_SYSSMU9_1424341975$ UNDOTBS1                 9 ACTIVE
_SYSSMU9_1424341975$ UNDOTBS1                10 ACTIVE
_SYSSMU9_1424341975$ UNDOTBS1                11 ACTIVE

16 rows selected
YANG@yangdb-rac3> SELECT count(1) FROM v$undostat;
  COUNT(1)
----------
       464
--過1分鐘多一些時間，再次檢視undo回滾段的狀態：
YANG@yangdb-rac3> SELECT begin_time, end_time, tuned_undoretention FROM v$undostat where rownum =1;
BEGIN_TIME          END_TIME            TUNED_UNDORETENTION
------------------- ------------------- -------------------
2011/10/16 19:56:29 2011/10/16 20:14:50                1592

YANG@yangdb-rac3> select segment_name ,tablespace_name,extent_id,status
  2     from dba_undo_extents
  3     where segment_name='_SYSSMU9_1424341975$' or status='ACTIVE';

SEGMENT_NAME         TABLESPACE_NAME  EXTENT_ID STATUS
-------------------- --------------- ---------- ---------
_SYSSMU9_1424341975$ UNDOTBS1                 0 EXPIRED
_SYSSMU9_1424341975$ UNDOTBS1                 1 EXPIRED
_SYSSMU9_1424341975$ UNDOTBS1                 2 EXPIRED
_SYSSMU9_1424341975$ UNDOTBS1                 3 EXPIRED
_SYSSMU9_1424341975$ UNDOTBS1                 4 EXPIRED
_SYSSMU9_1424341975$ UNDOTBS1                 5 EXPIRED
_SYSSMU9_1424341975$ UNDOTBS1                 6 EXPIRED
_SYSSMU9_1424341975$ UNDOTBS1                 7 EXPIRED
_SYSSMU9_1424341975$ UNDOTBS1                 8 EXPIRED
_SYSSMU9_1424341975$ UNDOTBS1                 9 EXPIRED
_SYSSMU9_1424341975$ UNDOTBS1                10 UNEXPIRED
_SYSSMU9_1424341975$ UNDOTBS1                11 EXPIRED
12 rows selected.
可見，修改隱式引數” _UNDO_AUTOTUNE”為FALSE關閉該自動調優特性，可以解決表空間的使用率總是100%的問題。
附上DBA_UNDO_EXTENTS，STATUS的值及其意義：
ACTIVE    活動狀態，說明當前這個資料區被某個正在進行的事務使用。
EXPIRED   已過期，說明已分配的資料區已經完成了它的使命，隨時可以被分配給其它新的事務使用。
UNEXPIRED 未過期，說明分配的資料區已經不屬於任何的活動事務，但是由於UNDO RETENTION設定的需要，一般情況下不會被回收重用。
相關文章：
FAQ – Automatic Undo Management (AUM) / System Managed Undo (SMU) [ID 461480.1]
Ora-30036 On Undo Tablespace After Upgrade To 10.2.0.4 [ID 856344.1]

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-709213/，如需轉載，請註明出處，否則將追究法律責任。

相關文章