3 积分	0 好友	1 主题

发消息

[SQL调优] UNDO表空间使用率99%的处理办法

1^#

发表于 2014-12-4 12:09:15 | 查看: 7414| 回复: 5

   巡检发现运营商某数据库的UNDOTBS1表空间使用率高达99%，用户对此比较担心，经过对UNDO表空间实际使用情况进行分析，认为这是Oracle 10gR2以后Automatic Undo Management(AUM)的机制引起的，属于正常的行为。

Oracle 10gR2以后AUM的申请空间顺序如下：
1.    从当前事务的undo extent剩余空间申请。
2.    如果附近的undo extent已经expired，wrap到这个extent。
3.    如果附近的undo extent是unexpired，从undo表空间已分配的空间里申请。
4.    回收已经提交了且undo block标记为expired空间。
5.    如果表空间可以自动增长的，数据文件进行扩展。
6.    回收事务已经提交但是状态为unexpired的undo block，这个操作叫STEAL。
7.    回收还没有提交事务事务的undo block，这个操作容易导致ORA-1555错误。
8.    如果经过上述操作空间依然不能满足，报错UNDO表空间无法扩展。

尽管数据库设置了undo_retention参数，但是Oracle 10gR2以后这个参数只是一个最低值，实际保留多久由数据库动态调整。对于表空间是否自动增长，有两种不同的计算方法：
(1)          如果表空间是固定大小的，则根据剩余空间和当前每秒产生的undo block来计算，最大可能的保证不出ORA-1555错误，导致调整后的undo_retention远大于参数设定值。
(2)          如果表空间是自动增长的，则根据max(maxquerylength+300, undo_retetion)来计算保留时间
XXX数据库的UNDO表空间是不可自动增长的，undo_retention设定为1800秒。
select file_name,autoextensible,bytes,maxbytes from dba_data_files where tablespace_name='UNDOTBS1';
FILE_NAME                                        AU BYTES    MAXBYTES
------------------------------------------------- -- ---------- --------
+DG_DBFILE/scp11g/datafile/undotbs1.264.692904117 NO 6442450944 0

通过查询v$undostat发现，undo的实际保留时间是360多万秒，远远大于初始设定值，且没有发生steal from expired(第6步)和unexpired(第7步)，最大事务长度也不超过设定的1800s。
SELECT to_char(begin_time,'yyyy-mm-dd hh24:mi') begin_time,
   to_char(end_time,'yyyy-mm-dd hh24:mi') end_time,
   UNXPSTEALCNT,EXPSTEALCNT,UNXPBLKRELCNT,EXPBLKREUCNT,TUNED_UNDORETENTION
FROM v$undostat
order by begin_time;
因此可以说明目前undo表空间是足够大的，使用率100%完全是AUM的机制导致的，不会出现因UNDO表空间不足导致业务失败的问题。

解决方案:
当然表空间始终很高给监控带来困扰，为了让UNDO表空间较真实的反映实际使用情况，我们可以采用如下三种办法：
1.    修改UNDO表空间的数据文件为自动增长，并且设定maxsize为一个可以接受的值。
例如:
alter database datafile ‘+DG_DBFILE/scp11g/datafile/undotbs1.264.692904117’ autoextend on maxsize 8g;
如果只想给UNDO表空间6g，和现在的大小一样，也可以指定maxsize 6g，实际上根本无法扩展，但是数据文件属性是autoextend on，算法还是按自动增长的来。

2.    通过隐含参数改变数据库的调整undo_retention的策略
初始化参数文件中增加：
_smu_debug_mode=33554432
实际上方案1和2的效果是一样，就是根据max(maxquerylength+300, undo_retention)来计算实际undo_retention。

3.    取消10gR2的auto UNDO retention tuning
初始化参数文件中增加：
_undo_autotune = false
这样修改后undo_retention就固定为参数设定值(1800s)，如果参数设的不够大，有可能会导致ORA-1555错误。

综合比较这三种方案，由于方案1可以在线执行，完全不影响业务的运行，而且效果和方案2是一样的，因此最推荐。方案3如果出现事务运行时间太长超过设定的undo_retention，可能导致ORA-1555错误，不是很建议。

分享0