最近有個網友諮詢我一個問題，是關於undo_retention的，對於這個引數沒有過多關注，只是知道需要設定undo_retention搭配使用undotablespace retention guarantee
透過郵件的操作記錄可以看出這個網友還是很嚴謹的，每一個步驟都很詳細的列了出來，這位網友在測試11.2.0.1.0的環境中發現undo retention沒有像期望值那樣來達到預期的效果。
自己在本地測試了多次，雖然結果還是不夠理想，不過基本思路已經有了，繼續努力。
我所在的環境是11.2.0.4.0，但是問題的效果是一樣的，所以可以斷定這個現象在新版本的庫中也應該存在。
SQL> select * from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
檢視undo retention的預設值為900，即是900秒
SQL> show parameter undo_retention
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     900
為了測試期間，設定為60秒
SQL> alter system set undo_retention=60;
System altered.
然後使用一個測試使用者來建立臨時表。
SQL> conn test/test
Connected.
SQL> create table test_tab as select *from all_objects;
Table created.

SQL> select *from cat;
TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
TEST_TAB                       TABLE
基本的測試資料就準備完了，開始測試。
SQL> conn test/test
Connected.
首先更新一行。
SQL> update test_tab set object_id=2 where rownum=1;
1 row updated.
然後在事務檢視中檢視
SQL> select xidusn,status from v$transaction where addr=(select taddr from v$session where sid=(select sid from v$mystat where rownum=1));
    XIDUSN STATUS
---------- ----------------
         3 ACTIVE
找到對應的回滾段。
SQL> select name from v$rollname where usn=3;
NAME
------------------------------
_SYSSMU3_1723003836$

SQL> set linesize 200
select owner,segment_name,tablespace_name,extent_id,file_id,block_id,blocks,status from dba_undo_extents where segment_name='_SYSSMU3_1723003836$';
OWN SEGMENT_NAME                   TABLESPACE_NAME                 EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS STATUS
--- ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------
SYS _SYSSMU3_1723003836$           UNDOTBS1                                0          3        160          8 EXPIRED
SYS _SYSSMU3_1723003836$           UNDOTBS1                                1          3        264          8 EXPIRED
SYS _SYSSMU3_1723003836$           UNDOTBS1                                2          3       3200        128 ACTIVE
這個時候存在active的回滾資訊，一般狀態分為三種。EXPIRED，UNEXPIRED，ACTIVE。ACTIVE表示目前活躍的事務回滾資訊，UNEXPIRED表示雖然事務已經結束但回滾資訊的保留時間仍未超過例項引數UNDO_RETENTION所設定的值，EXPIRED表示回滾資訊保留時間已超過UNDO_RETENTION所設定的值。
然後短暫等待後，提交。
SQL> commit;
Commit complete.
提交後檢視會存在EXPIRED和UNEXPIRED的回滾資訊
SQL> select owner,segment_name,tablespace_name,extent_id,file_id,block_id,blocks,status from dba_undo_extents where segment_name='_SYSSMU3_1723003836$';
OWN SEGMENT_NAME                   TABLESPACE_NAME                 EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS STATUS
--- ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------
SYS _SYSSMU3_1723003836$           UNDOTBS1                                0          3        160          8 EXPIRED
SYS _SYSSMU3_1723003836$           UNDOTBS1                                1          3        264          8 EXPIRED
SYS _SYSSMU3_1723003836$           UNDOTBS1                                2          3       3200        128 UNEXPIRED
這個時候如果在同一個session中檢視原來的回滾段就無從查起了，因為事務已經終結。
SQL> select xidusn,status from v$transaction where addr=(select taddr from v$session where sid=(select sid from v$mystat where rownum=1));
no rows selected
那麼我們還是根據之前的回滾資訊來看。
SQL> select owner,segment_name,tablespace_name,extent_id,file_id,block_id,blocks,status from dba_undo_extents where segment_name='_SYSSMU3_1723003836$';
OWN SEGMENT_NAME                   TABLESPACE_NAME                 EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS STATUS
--- ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------
SYS _SYSSMU3_1723003836$           UNDOTBS1                                0          3        160          8 EXPIRED
SYS _SYSSMU3_1723003836$           UNDOTBS1                                1          3        264          8 EXPIRED
SYS _SYSSMU3_1723003836$           UNDOTBS1                                2          3       3200        128 UNEXPIRED

如果再次更新，等待超過60秒，效果也還是一樣，還是存在UNEXPIRED的回滾資訊。
OWN SEGMENT_NAME                   TABLESPACE_NAME                 EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS STATUS
--- ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------
SYS _SYSSMU3_1723003836$           UNDOTBS1                                0          3        160          8 EXPIRED
SYS _SYSSMU3_1723003836$           UNDOTBS1                                1          3        264          8 EXPIRED
SYS _SYSSMU3_1723003836$           UNDOTBS1                                2          3       3200        128 UNEXPIRED
網友認為這個時候，如果按照undo_retention的設定思想，應該為EXPIRED
這個時候有個檢視可以參考 v$undostat
SQL> select max(maxquerylen),max(tuned_undoretention) from v$undostat;
MAX(MAXQUERYLEN) MAX(TUNED_UNDORETENTION)
---------------- ------------------------
            1904                     2804
可以看到資料庫根據演算法計算出來的最大TUNED_UNDORETENTION要比最大MAXQUERYLEN大不少，undo表空間中的資料經過TUNED_UNDORETENTION之後才會由UNEXPIRED變成 EXPIRED。TUNED_UNDORETENTION的最佳化，mos上也有幾篇文章可以參考。Undo Remains Unexpired When Using Non-autoextensible Datafiles For The Undo Tablespace (文件 ID 1112431.1)
當然自己對於這個問題也是心生疑惑，發現兩篇相關的文章。
Master Note: High Undo Space Usage (Doc ID 1578639.1)
Undo Remains Unexpired When Using Non-autoextensible Datafiles For The Undo Tablespace (Doc ID 1112431.1)

對於undo retention其實涉及的場景還是相對比較複雜的，一方面和undo資料檔案的自動擴充套件與否有關,而且資料庫級有一個設定為retention guarantee,同時還有一個隱含引數_undo_autotune
    select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
      from x$ksppi a, x$ksppcv b
    where a.indx = b.indx and a.ksppinm like '_undo_autotune'
這個引數是預設開啟了undo的最佳化的。
NAME                 VALUE                DESCRIPTION
-------------------- -------------------- ----------------------------------------
_undo_autotune       TRUE                 enable auto tuning of undo_retention
當然也可以選擇關掉這個部分，實際中還是需要考量。
SQL> alter system set "_undo_autotune"=false;
System altered.
不過修改後發現資料庫計算出的最大TUNED_UNDORETENTION和最大MAXQUERYLEN還是沒有任何變化。
SQL> select max(maxquerylen),max(tuned_undoretention) from v$undostat;
MAX(MAXQUERYLEN) MAX(TUNED_UNDORETENTION)
---------------- ------------------------
            1904                     2804
對於回滾資訊，還是顯示為UNEXPIRED
SQL> select owner,segment_name,tablespace_name,extent_id,file_id,block_id,blocks,status from dba_undo_extents where segment_name='_SYSSMU3_1723003836$';
OWN SEGMENT_NAME                   TABLESPACE_NAME                 EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS STATUS
--- ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------
SYS _SYSSMU3_1723003836$           UNDOTBS1                                0          3        160          8 EXPIRED
SYS _SYSSMU3_1723003836$           UNDOTBS1                                1          3        264          8 EXPIRED
SYS _SYSSMU3_1723003836$           UNDOTBS1                                2          3       3200        128 UNEXPIRED
對於這個部分反覆測試，有時候結果會有一些不同，所以後續繼續跟進來看看哪裡有細節的差別。
當然undo表空間設定為retention guarantee就會做相關的校驗了。
SQL> alter tablespace undotbs1 retention guarantee;
Tablespace altered.
而這個部分的測試結果還是和之前沒有差別。
SQL> select tablespace_name,contents,retention from dba_tablespaces where tablespace_name like '%UNDOTBS%';
TABLESPACE_NAME                CONTENTS RETENTION
------------------------------ --------- -----------
UNDOTBS1                       UNDO      GUARANTEE

將undo表空間自動擴充套件屬性取消
SQL> select file_name,autoextensible,bytes/1024/1024 size_MB,tablespace_name from dba_data_files where tablespace_name like 'UNDO%';
FILE_NAME                                          AUT    SIZE_MB TABLESPACE_NAME
-------------------------------------------------- --- ---------- ------------------------------
/DATA/app/oracle/oradata/test11g/undotbs01.dbf     YES        275 UNDOTBS1
SQL> alter database datafile '/DATA/app/oracle/oradata/test11g/undotbs01.dbf' autoextend off;
Database altered.
恢復為預設值
SQL> show parameter _undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_undo_autotune                       boolean     FALSE
SQL> alter system set "_undo_autotune"=true;
System altered.

自己嘗試迴圈小批次刪除資料，在guarantee設定下，很快會出現ORA-30036錯誤：
SQL> select count(*)from test_tab;
COUNT(*)
----------
   21858560

begin
       for i in 1..1000
      loop
        delete from test_tab where rownum<1001;
         commit;
      end loop;
     end;
/
begin
*
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'
ORA-06512: at line 4
而在修改了undo表空間retention屬性後，刪除可以順利完成
PL/SQL procedure successfully completed.
因為時間還是太緊，發現測試還是不夠到位，所以沒有得出最終很肯定，讓人信服的結論，後續繼續努力。

undo retention的思考(一)

相關文章