ITPUX技术网

交流 . 资讯 . 分享
Make progress together!
Oracle数据库DBA高级工程师培训视频
Oracle数据库培训-备份恢复-性能优化-集群容灾
mysql数据库视频教程

关于oracle UNDO表空间自动管理与自动调优的原则(包括FAQ-Automatic Undo Management)

内容发布:风哥| 发布时间:2014-4-22 11:30:08
关于oracle UNDO表空间自动管理自动调优的原则介绍,在Oracle 10gr2后面的版本中添加了UNDO信息最短保留时间段自动调优的特性,不再仅仅依据参数UNDO_RETENTION的设定,其调优原则如下:


1 UNDO TABLESPACEfixed-sizeOracle将根据表空间的大小和历史使用情况,自动调整undo信息保存时间,同时忽略 undo_retention的值除非 undo_retentionguarantee 特性被启用。


2 UNDO TABLESPACEAUM时,Oracle将动态调整撤销信息最短保留时间为该时段最长查询时间(MAXQUERYLEN)加上300秒或参数UNDO_RETENTION间的较大者,即MAX((MAXQUERYLEN+300),UNDO_RENTION)


在自动调整启用的情况下,实际的撤销信息最短保留时间可以通过查询V$UNDOSTAT视图上的TUNED_UNDORETENTION列获得。往往最短保存时间远远大于设定的UNDO_RETENTION。在无法就UNDO TABLESPACE做相应修改的情况,可以通过修改隐式参数_UNDO_AUTOTUNEFALSE关闭该自动调优特性。以上设定生效后,V$UNDOSTAT视图上TUNED_UNDORETENTION列不再更新,且撤销信息最短保留时间固定为参数UNDO_RETENTION的设定值。该参数可以不用重启数据库而动态设置生效。


下面做实验说明undo自动调整的功能以及其弊端:注:实验环境中无他事务。

进行一个dml 语句不提交,查看dba_undo_extents 关于回滚段的信息,
$ora-rac> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 60
undo_tablespace string UNDOTBS1

$ora-rac> update bind set status='VALID' where wner='test';
5 rows updated.

$ora-rac> select segment_name ,tablespace_name,extent_id,status from dba_undo_extents where status='ACTIVE';
SEGMENT_NAME TABLESPACE_NAME EXTENT_ID STATUS
-------------------- --------------- ---------- ---------
_SYSSMU3_2097677531$ UNDOTBS1 2 ACTIVE
$ora-rac> commit;
Commit complete.

$ora-rac> --等待一分钟之后
$ora-rac> select segment_name ,tablespace_name,extent_id,status from dba_undo_extents where status='ACTIVE';
SEGMENT_NAME TABLESPACE_NAME EXTENT_ID STATUS
-------------------- --------------- ---------- ---------
_SYSSMU3_2097677531$ UNDOTBS1 2 UNEXPIRED

$ora-rac> --等待一分钟之后
$ora-rac> select segment_name ,tablespace_name,extent_id,status from dba_undo_extents 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 而定,查看其信息;

$ora-rac> ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS' ;
Session altered.

$ora-rac> SELECT begin_time, end_time, tuned_undoretention FROM v$undostat;
BEGIN_TIME END_TIME TUNED_UNDORETENTION
------------------- ------------------- -------------------
2013/10/16 19:46:29 2011/10/16 19:56:29 1412 --进行查询时候的保留时间明显大于60s
2013/10/16 19:36:29 2011/10/16 19:46:29 2017
2013/10/16 19:26:29 2011/10/16 19:36:29 1416
2013/10/16 19:16:29 2011/10/16 19:26:29 2018
2013/10/16 19:06:29 2011/10/16 19:16:29 1418
2013/10/16 18:56:29 2011/10/16 19:06:29 2022
2013/10/16 18:46:29 2011/10/16 18:56:29 1421
2013/10/16 18:36:29 2011/10/16 18:46:29 2026
2013/10/16 18:26:29 2011/10/16 18:36:29 1425
2013/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自动调优之前的数据:
$ora-rac> conn /as sysdba
Connected.
SYS@testdb-rac> alter system set "_undo_autotune"=false;
System altered.
$ora-rac>conn test/test
Connected.
$ora-rac>--10分钟之后

$ora-rac>SELECT count(1) FROM v$undostat;
COUNT(1)
----------
464 --还是之前的个数
$ora-rac>SELECT begin_time, end_time, tuned_undoretention FROM v$undostat where rownum =1;
BEGIN_TIME END_TIME TUNED_UNDORETENTION
------------------- ------------------- -------------------
2013/10/16 19:56:29 2011/10/16 20:08:11 1592
---

$ora-rac> SELECT begin_time, end_time, tuned_undoretention FROM v$undostat where rownum<2;
BEGIN_TIME END_TIME TUNED_UNDORETENTION
------------------- ------------------- -------------------
2013/10/16 19:56:29 2011/10/16 21:08:53 1592

上面两个是在不同时间进行的查询, v$undostat的记录不足每十分钟进行一次统计。

再次做一个实验:这次事务提交一分钟之后,undo段的状态变为EXPIRED

$ora-rac> UPDATE bind set status ='INVALID' where status='VALID';
72747 rows updated.
$ora-rac> ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS' ;
Session altered.
$ora-rac> select sysdate from dual;
SYSDATE
-------------------
2013/10/16 20:13:41
$ora-rac> commit;
Commit complete.
--提交时立即做查询:
$ora-rac> select segment_name ,tablespace_name,extent_id,status from dba_undo_extents 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
$ora-rac> SELECT count(1) FROM v$undostat;
COUNT(1)
----------
464

--过1分钟多一些时间,再次查看undo回滚段的状态:
$ora-rac> SELECT begin_time, end_time, tuned_undoretention FROM v$undostat where rownum =1;
BEGIN_TIME END_TIME TUNED_UNDORETENTION
------------------- ------------------- -------------------
2013/10/16 19:56:29 2011/10/16 20:14:50 1592


$ora-rac> 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]




上一篇:关于Oracle数据库undo回滚空间的解析与分析
下一篇:FAQ – Automatic Undo Management (AUM) / System Managed Undo (SMU)
189070296,150201289

专业提供Oracle数据库服务、主机、存储、备份、中间件等相关技术支持服务,QQ号:176140749
关注ITPUX技术网微信公众号itpux_com  ,了解本站最新技术资料的分享.

欢迎加QQ群,提供超多高质量Oracle/Unix/Linux技术文档与视频教程的下载。

Oracle/MySQL/Linux群4-5:189070296  150201289  
Oracle/MySQL/Linux群6-8:244609803   522261684   522651731
备注:请勿重复加群,另请注明 from itpux

加群分享视频教程部分如下:

1、公开课视频:Oracle/MySQL数据库工程师职业发展前景讲解(免费)
http://edu.51cto.com/course/7015.html

2、51CTO学院Oracle数据库高级工程师培训(高薪就业.课程介绍)
http://edu.51cto.com/px/train/131?xiaotu

3、Oracle DBA数据库高级工程师培训视频课程1.1(系列78套+七大阶段+上千案例)
套餐视频地址: http://edu.51cto.com/topic/1121.html

4、MySQL数据库(终身门徒)套餐:http://edu.51cto.com/sd/1e1a6

回复

使用道具 举报

1框架
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

快速回复 返回顶部 返回列表