oracle 之 Undo Retention
Rhys-Oracle
于 2013-08-21 22:09:38 发布
1556
收藏
分类专栏： ORACLE 之 UNDO
版权
ORACLE 之 UNDO
专栏收录该内容
11 篇文章0 订阅
订阅专栏
oracle 之 Undo Retention
oralce database 根据undo表空间大小和系统活动状态自动调整保留时间，可以调整undo_retention 参数设置保留时间。但是准确的将分为如下两种情况。
1）在国定大小的undo表空间中undo_retention参数被忽略，oracle会根据系统活动和空间大小自动调整合适的保留时间来满足应用需求。如果决定把undo 表空间设置为固定大小，那么一定要保证该空间具有足够的size。该undo_retention设置的参数被忽略，如果 选择的空间很小那么会出现如下情况
1、dml会失败，因为没有足够的空间去分配新的事务。
2、运行很长的query可能会出现snapshot too old 错误 ，这是由于不能满足一致性读的问题 （常见为 ORA-01555)
eg：
1)创建一个很小的不是自动扩展的表空间
SQL> create undo tablespace undotbs3 datafile ‘/opt/app/oracle/RHYS/undotbs3.dbf’ size 50M autoextend off;
Tablespace created.
SQL> select file_name,tablespace_name,autoextensible from dba_data_files;
FILE_NAME TABLESPACE_NAME AUT
/opt/app/oracle/RHYS/system01.dbf SYSTEM YES
/opt/app/oracle/RHYS/sysaux01.dbf SYSAUX YES
/opt/app/oracle/RHYS/undotbs01.dbf UNDOTBS1 YES
/opt/app/oracle/RHYS/users01.dbf USERS YES
/opt/app/oracle/RHYS/test.dbf TEST YES
/opt/app/oracle/RHYS/arch_tbs01.dbf ARCH_TBS NO
/opt/app/oracle/RHYS/statspack.dbf STATSPACK NO
/opt/app/oracle/RHYS/undotbs2.dbf UNDOTBS2 YES
/opt/app/oracle/RHYS/undotbs21.dbf UNDOTBS2 NO
/opt/app/oracle/RHYS/undotbs3.dbf UNDOTBS3 NO
10 rows selected.
SQL> show parameter undo
NAME TYPE VALUE
_optimizer_undo_cost_change string 11.2.0.1
undo_management string AUTO
undo_retention integer 60
undo_tablespace string UNDOTBS2
SQL> alter system set undo_tablespace=undotbs3;
System altered.
SQL> select a.name,a.usn,b.status from vrollname a where a.usn=b.usn;
NAME USN STATUS
SYSTEM 0 ONLINE
_SYSSMU21_285059280$ 21 ONLINE
_SYSSMU22_1963502844$ 22 ONLINE
_SYSSMU23_2778616806$ 23 ONLINE
_SYSSMU24_564050484$ 24 ONLINE
_SYSSMU25_800148274$ 25 ONLINE
_SYSSMU26_1460554600$ 26 ONLINE
_SYSSMU27_931264876$ 27 ONLINE
_SYSSMU28_1920348465$ 28 ONLINE
_SYSSMU29_1181389426$ 29 ONLINE
_SYSSMU30_2665996069$ 30 ONLINE
11 rows selected.
SQL> show parameter undo
NAME TYPE VALUE
_optimizer_undo_cost_change string 11.2.0.1
undo_management string AUTO
undo_retention integer 60
undo_tablespace string UNDOTBS3
SQL>
SQL> show parameter undo
NAME TYPE VALUE
_optimizer_undo_cost_change string 11.2.0.1
undo_management string AUTO
undo_retention integer 60
undo_tablespace string UNDOTBS3
SQL> select begin_time,end_time,undotsn,undoblks,maxquerylen,maxqueryid from v$undostat;
BEGIN_TIM END_TIME UNDOTSN UNDOBLKS MAXQUERYLEN MAXQUERYID
21-AUG-13 21-AUG-13 9 18 336 0rc4km05kgzb9
21-AUG-13 21-AUG-13 8 41 940 0rc4km05kgzb9
21-AUG-13 21-AUG-13 8 6 338 0rc4km05kgzb9
21-AUG-13 21-AUG-13 8 60 939 0rc4km05kgzb9
21-AUG-13 21-AUG-13 8 12 337 0rc4km05kgzb9
SQL> r
1* select begin_time,end_time,undotsn,undoblks,maxquerylen,maxqueryid from v$undostat
BEGIN_TIM END_TIME UNDOTSN UNDOBLKS MAXQUERYLEN MAXQUERYID
21-AUG-13 21-AUG-13 9 0 336 0rc4km05kgzb9
21-AUG-13 21-AUG-13 9 18 336 0rc4km05kgzb9
21-AUG-13 21-AUG-13 8 41 940 0rc4km05kgzb9
21-AUG-13 21-AUG-13 8 6 338 0rc4km05kgzb9
21-AUG-13 21-AUG-13 8 60 939 0rc4km05kgzb9
21-AUG-13 21-AUG-13 8 12 337 0rc4km05kgzb9
6 rows selected.
2）看到系统自动调整的undo retention为940s
SQL> delete from t;
73083 rows deleted.
SQL> begin
2 for i in 1…100 loop
3 insert into t select * from obj;
4 commit;
5 end loop;
6* end;
PL/SQL procedure successfully completed.
SQL> select count() from t;
COUNT()
14624600
SQL> select begin_time,end_time,undotsn,undoblks,maxquerylen,maxqueryid from v$undostat;
BEGIN_TIM END_TIME UNDOTSN UNDOBLKS MAXQUERYLEN MAXQUERYID
21-AUG-13 21-AUG-13 9 8554 637 0rc4km05kgzb9
21-AUG-13 21-AUG-13 9 18 336 0rc4km05kgzb9
21-AUG-13 21-AUG-13 8 41 940 0rc4km05kgzb9
21-AUG-13 21-AUG-13 8 6 338 0rc4km05kgzb9
21-AUG-13 21-AUG-13 8 60 939 0rc4km05kgzb9
21-AUG-13 21-AUG-13 8 12 337 0rc4km05kgzb9
6 rows selected.
SQL> show parameter undo_retention
NAME TYPE VALUE
undo_retention integer 60
SQL> select file_name,tablespace_name,bytes/1024/1024||‘M’ MB,autoextensible from dba_data_files;
FILE_NAME TABLESPACE_NAME MB AUT
/opt/app/oracle/RHYS/system01.dbf SYSTEM 990M YES
/opt/app/oracle/RHYS/sysaux01.dbf SYSAUX 600M YES
/opt/app/oracle/RHYS/undotbs01.dbf UNDOTBS1 325M YES
/opt/app/oracle/RHYS/users01.dbf USERS 5M YES
/opt/app/oracle/RHYS/test.dbf TEST 1480M YES
/opt/app/oracle/RHYS/arch_tbs01.dbf ARCH_TBS 500M NO
/opt/app/oracle/RHYS/statspack.dbf STATSPACK 200M NO
/opt/app/oracle/RHYS/undotbs2.dbf UNDOTBS2 376.9375M YES
/opt/app/oracle/RHYS/undotbs21.dbf UNDOTBS2 50M NO
/opt/app/oracle/RHYS/undotbs3.dbf UNDOTBS3 50M NO
10 rows selected.
3）删除一个大表，产生数据前镜像需要使用undo
SQL> delete from t;
delete from t
*
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace ‘UNDOTBS3’
可以看到已经无法分配空间了，导致了dml错误。
注意：自动调整保留时间对于lobs不适应，lobs依然使用undo_retention进行保留。
2)当undo tablespace为 自动扩展的话，那么可以 通过 设置undo_retention参数进行保留一定的undo record单位为妙，如果没有达到该值，那么会自动扩展表空间，当达到maxsize那么就会overwrite record。但是可以使用guarantee参数，强制不覆盖undo记录。但是这是 会出现error提示。另外oracle自动调整保留策略是max((undo_retention),(maxquery+300)）。
oracle该自动调整retention 受一个隐含参数控制，当该隐含参数为false，那么该自动调整功能失效，受undo_retention参数影响。
eg：
SQL> get getpar.sql
1* select a.ksppinm,b.ksppstvl,a.ksppdesc from xksppi a where a.indx=b.indx and a.ksppinm like ‘%&var%’
SQL> set linesize 200
SQL> set verify off
SQL> r
1* select a.ksppinm,b.ksppstvl,a.ksppdesc from xksppi a where a.indx=b.indx and a.ksppinm like ‘%&var%’
Enter value for var: undo_autotune
KSPPINM KSPPSTVL KSPPDESC
_undo_autotune TRUE enable auto tuning of undo_retention
SQL>
Reference：
The UNDO_RETENTION parameter is ignored for a fixed size undo tablespace. The database always tunes the undo retention period for the best possible retention, based on system activity and undo tablespace size. See “Automatic Tuning of Undo Retention” for more information.
For an undo tablespace with the AUTOEXTEND option enabled, the database attempts to honor the minimum retention period specified by UNDO_RETENTION. When space is low, instead of overwriting unexpired undo information, the tablespace auto-extends. If the MAXSIZE clause is specified for an auto-extending undo tablespace, when the maximum size is reached, the database may begin to overwrite unexpired undo information. The UNDOTBS1 tablespace that is automatically created by DBCA is auto-extending.
Oracle Database automatically tunes the undo retention period based on how the undo tablespace is configured.
If the undo tablespace is configured with the AUTOEXTEND option, the database dynamically tunes the undo retention period to be somewhat longer than the longest-running active query on the system. However, this retention period may be insufficient to accommodate Oracle Flashback operations. Oracle Flashback operations resulting in snapshot too old errors are the indicator that you must intervene to ensure that sufficient undo data is retained to support these operations. To better accommodate Oracle Flashback features, you can either set the UNDO_RETENTION parameter to a value equal to the longest expected Oracle Flashback operation, or you can change the undo tablespace to fixed size.
If the undo tablespace is fixed size, the database dynamically tunes the undo retention period for the best possible retention for that tablespace size and the current system load. This best possible retention time is typically significantly greater than the duration of the longest-running active query.
If you decide to change the undo tablespace to fixed-size, you must choose a tablespace size that is sufficiently large. If you choose an undo tablespace size that is too small, the following two errors could occur:
DML could fail because there is not enough space to accommodate undo for new transactions.
Long-running queries could fail with a snapshot too old error, which means that there was insufficient undo data for read consistency.
note：
Automatic tuning of undo retention is not supported for LOBs. This is because undo information for LOBs is stored in the segment itself and not in the undo tablespace. For LOBs, the database attempts to honor the minimum undo retention period specified by UNDO_RETENTION. However, if space becomes low, unexpired LOB undo information may be overwritten.
————————————————
版权声明：本文为CSDN博主「Rhys-Oracle」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/rhys_oracle/article/details/10162043
