了解你所不知道的SMON功能(十二):Shrink UNDO(rollback) SEGMENT
SMON对于Undo(Rollback)segment的日常管理还不止于OFFLINE UNDO SEGMENT ,在AUM(automatic undo management或称SMU)模式下SMON还定期地收缩Shrink Rollback/undo segment。
触发场景
这种AUM下rollback/undo segment的undo extents被shrink的现象可能被多种条件触发:
- 当另一个回滚段的transaction table急需undo空间时
- 当SMON定期执行undo/rollback管理时(每12个小时一次):
- SMON会从空闲的undo segment中回收undo space,以便保证其他tranaction table需要空间时可用。另一个好处是undo datafile的身材不会急速膨胀导致用户要去resize
- 当处于undo space空间压力时,特别是在发生UNDO STEAL的条件下; SGA中会记录前台进程因为undo space压力而做的undo steal的次数(v$undostat UNXPSTEALCNT EXPSTEALCNT);若这种UNDO STEAL的次数超过特定的阀值,则SMON会尝试shrink transaction table
- 若是offline的undo segment,则回收其所有的已过期expired undo extents,保持最小2个extents的空间
- 若是online的undo segment,则回收其所有的已过期expired undo extents,但是保持其segment所占空间不小于平均retention对应的大小。
[oracle@vmac1 ~]$ oerr ora 1551 01551, 00000, "extended rollback segment, pinned blocks released" // *Cause: Doing recursive extent of rollback segment, trapped internally // by the system // *Action: None如何禁止SMON SHRINK UNDO SEGMENT? 可以通过设置诊断事件event='10512 trace name context forever, level 1'来禁用SMON OFFLINE UNDO SEGS;
SQL> select * from global_name; GLOBAL_NAME -------------------------------------------------------------------------------- www.askmaclean.com SQL> alter system set events '10512 trace name context forever,level 1'; System altered.相关BUG 这些BUG主要集中在9.2.0.8之前,10.2.0.3以后几乎绝迹了: Bug 1955307 - SMON may self-deadlock (ORA-60) shrinking a rollback segment in SMU mode [ID 1955307.8] Bug 3476871 : SMON ORA-60 ORA-474 ORA-601 AND DATABASE CRASHED Bug 5902053 : SMON WAITING ON 'UNDO SEGMENT TX SLOT' HANGS DATABASE Bug 6084112 : INSTANCE SLOW SHOW SEVERAL LONGTIME RUNNING WAIT EVENTS
posted on 2013-03-19 00:52 Oracle和MySQL 阅读(343) 评论(0) 编辑 收藏 举报