【Oracle】undo 自动调优
2023-09-14 08:57:29 时间
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 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设置的需要，一般情况下不会被回收重用。
Oracle 短时间内误删数据，如何快速找回？（UNDO） 首先，这个短时间内，通常是值 undo 段没有被覆盖，undo 保留的时间为多长呢？ 1、需要看 undo_retention 的设置，默认为 900s，也就是 15 分钟。 2、需要看数据库的业务繁忙程度，如果1天切一个归档那种，3天前删的说不定都能用 UNDO 找回来。
在自动调整启用的情况下，实际的撤销信息最短保留时间可以通过查询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 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设置的需要，一般情况下不会被回收重用。
Oracle 短时间内误删数据，如何快速找回？（UNDO） 首先，这个短时间内，通常是值 undo 段没有被覆盖，undo 保留的时间为多长呢？ 1、需要看 undo_retention 的设置，默认为 900s，也就是 15 分钟。 2、需要看数据库的业务繁忙程度，如果1天切一个归档那种，3天前删的说不定都能用 UNDO 找回来。
相关文章
- EF Core + Oracle自动生成数据库
- 修改oracle字符集
- 【Oracle】修改oracle监听端口
- 【Oracle】undo 自动调优
- 修改oracle数据库用户名和密码
- 《oracle每日一练》Oracle DBLink连接数过多的问题(Ora-02020)
- (ORACLE)查看分区表的相关信息
- Oracle转化成为百分比
- 修改oracle监听地址
- Oracle 11g 默认用户名和密码
- cx_Oracle连接oracle数据库
- Oracle Latch视图
- Oracle 11g 在使用时应该启动哪些服务？
- 如何查看Oracle的版本信息
- oracle 11g客户端如何完全卸载
- C# 连接Oracle数据库，免安装oracle客户端
- Oracle数据库 External component has thrown an exception
- Oracle中添加虚拟列（emp是oracle自带表）
- oracle sql 替换 in ，dual性能优化
- 玩转oracle学习第五天
- 探索Oracle之数据库升级八 12c Downgrade 11gR2
- Oracle数据库用户管理之二---权限授权去权（用户和角色）