最近有个网友咨询我一个问题，是关于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
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.
因为时间还是太紧，发现测试还是不够到位，所以没有得出最终很肯定，让人信服的结论，后续继续努力。
