深入解析Oracle undo原理_undo表空间使用率100%问题处理

作者简介:惠星星,现就职于北京海天起点,持有OCP 10g、OCP 11g、OCM 11g证书,并有长达8年电力行业业务维护、数据库维护服务经验,擅长Oracle数据库性能优化、故障处理及数据可视化技术研究。

 

问题描述

根据客户反映,某系统Oracle数据库,版本11.2.0.4, undo表空间使用率一直维持100%,请协助分析处理。

问题分析

查询数据库undo extent状态信息,

通过以上查询语句发现undo extent 99%状态为UNEXPIRED,
查询数据库undo_retention设置

查询TUNED_UNDORETENTION

_undo_autotune隐含参数检查:

undo数据文件自动扩展检查:

通过以上分析,该问题为oracle数据库undo保留时间自动调节(Automatic Tuning of Undo Retention Feature)特性造成undo保留时间超长,大量undo extents状态为unexpired,业务功能运行正常。

Oracle 10g/11g会自动调整Undo Retention,以减少长时间运行查询时出现”snapshot too old”错误的几率。

当undo表空间设置为AUTOEXTEND NO时,空间分配算法如下,

When the UNDO tablespace is created with NO AUTOEXTEND, the below allocation algorithm is being followed:

  • If the current extent has more free blocks then the next free block is allocated.–从当前事务的undo extent剩余空间申请
  • Otherwise, if the next extent expired then wrap in the next extent and return the first block.–如果下一个extent为expired,wrap到这个extent
  • If the next extent is not expired then get space from the UNDO tablespace. If a free extent is available then allocate it to the undo segment and return the first block in the new extent.–如果下一个extent为unexpired,从undo表空间的free extent中申请
  • If there is no free extent available, then steal expired extents from offline undo segments. De-allocate the expired extent from the offline undo segment and add it to the undo segment. Return the first free block of the extent.–如果undo表空间无free extent则从offline undo segments中steal expired extents
  • If no expired extents are available in offline undo segments, then steal from online undo segments and add the new extents to the current undo segment. Return the first free block of the extent.–如果offline undo segments没有expired extents,则steal from online undo segments
  • Extend the file in the UNDO tablespace. If the file can be extended then add an extent to the current undo segment and then return the block.–如果表空间可以自动增长的,数据文件进行扩展
  • Tune down retention in decrements of 10% and steal extents that were unexpired, but now expired with respect to the lower retention value.–减少10%的retention,steal expired extents
  • Steal unexpired extents from any offline undo segments.
  • Try to reuse unexpired extents from own undo segment. If all extents are currently busy (they contains uncommitted information) go to the step 10. Otherwise, wrap into the next extent.
  • Try to steal unexpired extents from any online undo segment.
  • If all the above fails then return ORA-30036 unable to extend segment by %s in undo tablespace ‘%s’

解决方案

  • 修改UNDO表空间的数据文件为AUTOEXTEND ON
  • 通过隐含参数改变数据库的调整undo_retention的策略

  • 取消auto UNDO retention tuning

由于方案2、3都需要重启数据库所以建议使用方法1。

修改后数据库undo表空间使用率情况:

操作完成后,undo表空间使用率从100%降低到4.01%。

参考文档

Automatic Tuning of Undo Retention Common Issues (文档 ID 1579779.1)


未经允许不得转载:Oracle一体机用户组 » 深入解析Oracle undo原理_undo表空间使用率100%问题处理

相关推荐