了解你所不知道的SMON功能(七):清理IND$字典基表
SMON的作用还包括清理IND$字典基表(cleanup ind$):
触发场景
当我们在线创建或重建索引时(create or rebuild index online)，服务进程会到IND$字典基表中将该索引对应的记录的FLAGS字段修改为十进制的256或者512(见上图0×100=256,0×200=512)，如:
SQL> create index macleans_index on larges(owner,object_name) online;
SQL> select obj# from obj$ where name='MACLEANS_INDEX';
OBJ#
----------
1343842
SQL> select FLAGS from ind$ where obj#=1343842;
FLAGS
----------
256
ind_online$字典基表记录了索引在线创建/重建的历史
SQL> select * from ind_online$;
OBJ# TYPE# FLAGS
---------- ---------- ----------
1343839 1 256
1343842 1 256
create table ind_online$
( obj# number not null,
type# number not null, /* what kind of index is this? */
/* normal : 1 */
/* bitmap : 2 */
/* cluster : 3 */
/* iot - top : 4 */
/* iot - nested : 5 */
/* secondary : 6 */
/* ansi : 7 */
/* lob : 8 */
/* cooperative index method : 9 */
flags number not null
/* index is being online built : 0x100 */
/* index is being online rebuilt : 0x200 */
)
原则上online create/rebuild index的的清理工作由实际操作的服务进程负责完成，这种清理在DDL语句成功的情况下包括一系列数据字典的维护，在该DDL语句失败的情形中包括 对临时段的清理 和数据字典的维护，无论如何都需要drop在线日志中间表 SYS_JOURNAL_nnnnn(nnnn为该索引的obj#) 。数据字典的维护工作就包含对IND$基表中相应索引记录的FLAGS标志位的恢复，但是如果服务进程在语句执行过程中意外终止的话，那么短时间内FLAGS标志位字段就无法得到恢复，这将导致对该索引的后续操作因ORA-8104错误而无法继续:
SQL> drop index macleans_index;
drop index macleans_index
*
ERROR at line 1:
ORA-08104: this index object 1343842 is being online built or rebuilt
08104, 00000, "this index object %s is being online built or rebuilt"
// *Cause: the index is being created or rebuild or waited for recovering
// from the online (re)build
// *Action: wait the online index build or recovery to complete
SMON负责在启动后(startup)的每小时执行一次对IND$基表中因在线创建/重建索引失败所留下记录的清理，这种清理工作由kdicclean函数驱动(kdicclean is run by smon every 1 hour，called from SMON to find if there is any online builder death and cleanup our ind$ and obj$ and drop the journal table, stop journaling)。
这种清理工作典型的调用堆栈stack call如下:
ksbrdp -> ktmSmonMain ktmmon -> kdicclean -> kdic_cleanup -> ktssdrp_segment
注意因为SMON进程的清理工作每小时才执行一次，而且在工作负载很高的情况下可能实际很久都不会得到清理，在这种情景中我们总是希望能尽快完成对索引的在线创建或重建，在10gr2以后的版本中我们可以直接使用dbms_repair.online_index_clean来手动清理online index rebuild的遗留问题:
SQL> drop index macleans_index;
drop index macleans_index
*
ERROR at line 1:
ORA-08104: this index object 1343842 is being online built or rebuilt
DECLARE
isClean BOOLEAN;
BEGIN
isClean := FALSE;
WHILE isClean=FALSE
LOOP
isClean := dbms_repair.online_index_clean(
dbms_repair.all_index_id, dbms_repair.lock_wait);
dbms_lock.sleep(10);
END LOOP;
END;
/
SQL> drop index macleans_index;
drop index macleans_index
*
ERROR at line 1:
ORA-01418: specified index does not exist
成功清理
但是如果在9i中的话就比较麻烦，可以尝试用以下方法(不是很推荐，除非你已经等了很久):
1.首先手工删除在线日志表，通过以下手段找出这个中间表的名字
select object_name
from dba_objects
where object_name like
(select '%' || object_id || '%'
from dba_objects
where object_name = '&INDEX_NAME')
/
Enter value for index_name: MACLEANS_INDEX
old 6: where object_name = '&INDEX_NAME')
new 6: where object_name = 'MACLEANS_INDEX')
OBJECT_NAME
--------------------------------------------------------------------------------
SYS_JOURNAL_1343845
SQL> drop table SYS_JOURNAL_1343845;
Table dropped.
2.第二步要手动修改IND$字典基表
!!!!!! 注意！手动修改数据字典要足够小心！！
select flags from ind$ where obj#=&INDEX_OBJECT_ID;
Enter value for index_object_id: 1343845
old 1: select flags from ind$ where obj#=&INDEX_OBJECT_ID
new 1: select flags from ind$ where obj#=1343845
FLAGS
----------
256
a) 针对online create index，手动删除对应的记录
delete from IND$ where obj#=&INDEX_OBJECT_ID
b) 针对online rebuild index，手动恢复对应记录的FLAGS标志位
update IND$ set FLAGS=FLAGS-512 where obj#=&INDEX_OBJECT_ID
接下来我们实际观察一下清理工作的细节:
SQL> select obj# from obj$ where name='MACLEANS_INDEX';
OBJ#
----------
1343854
SQL> select FLAGS from ind$ where obj#=1343854;
FLAGS
----------
256
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 8;
Statement processed.
SQL> DECLARE
2 isClean BOOLEAN;
3 BEGIN
4 isClean := FALSE;
5 WHILE isClean=FALSE
6 LOOP
7 isClean := dbms_repair.online_index_clean(
8 dbms_repair.all_index_id, dbms_repair.lock_wait);
9
10 dbms_lock.sleep(10);
11 END LOOP;
12 END;
13 /
PL/SQL procedure successfully completed.
===============================10046 trace=============================
select i.obj#, i.flags, u.name, o.name, o.type#
from sys.obj$ o, sys.user$ u, sys.ind_online$ i
where (bitand(i.flags, 256) = 256 or bitand(i.flags, 512) = 512)
and (not ((i.type# = 9) and bitand(i.flags, 8) = 8))
and o.obj# = i.obj#
and o.owner# = u.user#
select u.name,
o.name,
o.namespace,
o.type#,
decode(bitand(i.property, 1024), 0, 0, 1)
from ind$ i, obj$ o, user$ u
where i.obj# = :1
and o.obj# = i.bo#
and o.owner# = u.user#
delete from object_usage
where obj# in (select a.obj#
from object_usage a, ind$ b
where a.obj# = b.obj#
and b.bo# = :1)
drop table "SYS"."SYS_JOURNAL_1343854" purge
delete from icoldep$ where obj# in (select obj# from ind$ where bo#=:1)
delete from ind$ where bo#=:1
delete from ind$ where obj#=:1
我们可以利用以下语句找出系统中可能需要恢复的IND$记录，注意不要看到查询有结果就认为这是操作失败的征兆，很可能是有人在线创建或重建索引:
select i.obj#, i.flags, u.name, o.name, o.type#
from sys.obj$ o, sys.user$ u, sys.ind_online$ i
where (bitand(i.flags, 256) = 256 or bitand(i.flags, 512) = 512)
and (not ((i.type# = 9) and bitand(i.flags, 8) = 8))
and o.obj# = i.obj#
and o.owner# = u.user#
/
相关诊断事件
可以通过设置诊断事件event=’8105 trace name context forever’
来禁止SMON清理IND$(Oracle event to turn off smon cleanup for online index build)
alter system set events '8105 trace name context forever';
了解你所不知道的SMON功能(八):Transaction Recover
July 15, 2011
By
Leave a Comment
SMON的作用还包括启动(startup)时的Transaction Recover:
SMON: enabling cache recovery
Archived Log entry 87 added for thread 1 sequence 58 ID 0xa044e7d dest 1:
[15190] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:421305354 end:421305534 diff:180 (1 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
在
<了解你所不知道的SMON功能(五):Recover Dead transaction>
中我们介绍了SMON清理死事务的功能，数据库打开时由SMON所启动的TX recovery与Recover Dead transaction所作的工作是类似的，fast_start_parallel_rollback参数决定了SMON在回滚事务时使用的并行度(详见原帖)。
但是请注意，实际startup时的TX recovery要比普通的Dead transaction recover复杂的多。其大致步骤如下:
1.在SYSTEM回滚段(Undo Segment Number为o)中的Active Transaction将被第一时间优先回滚
2.在其他回滚段中的Active Transaction将被标记为’DEAD’
3.之后SMON将扫描非SYSTEM的回滚段并实施对死事务的回滚，其典型的调用堆栈stack call如下:
kturec <- kturax <- ktprbeg <- ktmmon <- ktmSmonMain
4.SMON仍将扫描_OFFLINE_ROLLBACK_SEGMENTS所列出的回滚段，但对其上的Active Transaction不做回滚，若发现corrupted则只报错
5.SMON将忽略_CORRUPTED_ROLLBACK_SEGMENTS所列出的回滚段，甚至在启动时不做扫描，所有指向这类回滚段地事务都被认为已经提交了。
具体SMON在对ktuini的函数调用中启动Transaction Recover,该function的经典stack call如下：
adbdrv -> ktuini -> ktuiup -> kturec -> kturrt
or
adbdrv -> ktuini -> ktuiof -> ktunti -> kqrpre -> kqrpre1 -> ktuscr
其中由ktuiof函数判断_OFFLINE_ROLLBACK_SEGMENTS和_CORRUPTED_ROLLBACK_SEGMENTS的值，并将这些重要的回滚段信息转存到fixed array。
注意SYSTEM回滚段是bootstrap的重要对象，所以我们不能指定system rollback segment为offline或者corrupted。
SMON执行Transaction Recover时的大致步骤如下：
调用ktuiof保存_OFFLINE_ROLLBACK_SEGMENTS和_CORRUPTED_ROLLBACK_SEGMENTS所列出的回滚段
调用ktuiup函数，开始恢复回滚段上的死事务
第一优先级地恢复USN=0的SYSTEM回滚段上的事务，由kturec函数控制
对undo$字典基表上的记录循环：
FOR usn in undo$ loop
IF usn==0
恢复SYSTEM回滚段上在第一轮中未完成的事务，同样由kturec控制;
ELSE
将任何活动事务标记为DEAD，由kturec控制;
USN++
end loop
相关诊断事件
与Transaction Recover密切相关的诊断事件有不少，其中最为重要的是event 10013和10015；10015事件对于普通的dead transaction rollback也有效，之所以把该事件列在<Transaction Recover>功能内，是因为我们经常在非正常手段打开数据库时会遇到一些ORA-600[4xxx]的内部错误，可以通过10015事件了解相关的usn，然后以_SYSSMU(USN#)$的形式加入到_CORRUPTED_ROLLBACK_SEGMENTS以绕过内部错误(注意在11g中不能这样做了)：
Event 10013:Monitor transaction recovery during startup
SQL> alter system set event='10013 trace name context forever,level 10' scope=spfile;
Event 10015:Dump undo segment headers before and after transaction recovery
SQL> alter system set event='10015 trace name context forever,level 10' scope=spfile;
System altered.
======================10015 sample trace===========================
UNDO SEG (BEFORE RECOVERY): usn = 0 Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 6 #blocks: 47
last map 0x00000000 #maps: 0 offset: 4128
Highwater:: 0x0040000b ext#: 0 blk#: 1 ext size: 7
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
Unlocked
Map Header:: next 0x00000000 #extents: 6 obj#: 0 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x0040000a length: 7
0x00400011 length: 8
0x00400181 length: 8
0x00400189 length: 8
0x00400191 length: 8
0x00400199 length: 8
TRN CTL:: seq: 0x012c chd: 0x0033 ctl: 0x0026 inc: 0x00000000 nfb: 0x0001
mgc: 0x8002 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x0040000b.012c.1b scn: 0x0000.021fa595
Version: 0x01
FREE BLOCK POOL::
uba: 0x0040000b.012c.1b ext: 0x0 spc: 0x4a0
uba: 0x00000000.005c.07 ext: 0x2 spc: 0x1adc
uba: 0x00000000.0034.37 ext: 0x4 spc: 0x550
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num
------------------------------------------------------------------------------------------------
0x00 9 0x00 0x025d 0x002b 0x0000.02215c0b 0x0040000b 0x0000.000.00000000 0x00000001 0x00000000 0x0000
0x01 9 0x00 0x025d 0x0006 0x0000.0220a58c 0x0040000a 0x0000.000.00000000 0x00000001 0x00000000 0x0000
0x02 9 0x00 0x025d 0x000e 0x0000.0220a58a 0x0040000a 0x0000.000.00000000 0x00000001 0x00000000 0x0000
0x03 9 0x00 0x025d 0x000f 0x0000.02215be4 0x0040000b 0x0000.000.00000000 0x00000001 0x00000000 0x0000
0x04 9 0x00 0x025d 0x0008 0x0000.0220a57a 0x0040000a 0x0000.000.00000000 0x00000001 0x00000000 0x0000
0x05 9 0x00 0x025d 0x0056 0x0000.0220a583 0x0040000a 0x0000.000.00000000 0x00000001 0x00000000 0x0000
0x06 9 0x00 0x025d 0x0017 0x0000.0220a58d 0x0040000a 0x0000.000.00000000 0x00000001 0x00000000 0x0000
0x07 9 0x00 0x025d 0x0050 0x0000.0220a57f 0x0040000a 0x0000.000.00000000 0x00000001 0x00000000 0x0000
0x08 9 0x00 0x025d 0x0061 0x0000.0220a57c 0x0040000a 0x0000.000.00000000 0x00000001 0x00000000 0x0000
0x09 9 0x00 0x025d 0x0013 0x0000.02215c01 0x0040000b 0x0000.000.00000000 0x00000001 0x00000000 0x0000
0x0a 9 0x00 0x025d 0x0022 0x0000.02215bf7 0x0040000b 0x0000.000.00000000 0x00000001 0x00000000 0x0000
0x0b 9 0x00 0x025d 0x0014 0x0000.02215bdd 0x0040000a 0x0000.000.00000000 0x00000001 0x00000000 0x0000
0x0c 9 0x00 0x025c 0x003a 0x0000.021ff3fa 0x004001a0 0x0000.000.00000000 0x00000001 0x00000000 0x0000
0x0d 9 0x00 0x025d 0x0010 0x0000.02215c05 0x0040000b 0x0000.000.00000000 0x00000001 0x00000000 0x0000
0x0e 9 0x00 0x025d 0x0001 0x0000.0220a58b 0x0040000a 0x0000.000.00000000 0x00000001 0x00000000 0x0000
0x0f 9 0x00 0x025d 0x001c 0x0000.02215be6 0x0040000b 0x0000.000.00000000 0x00000001 0x00000000 0x0000
0x10 9 0x00 0x025d 0x002a 0x0000.02215c07 0x0040000b 0x0000.000.00000000 0x00000001 0x00000000 0x0000
0x11 9 0x00 0x025d 0x0025 0x0000.02215bf2 0x0040000b 0x0000.000.00000000 0x00000001 0x00000000 0x0000
0x12 9 0x00 0x025d 0x0018 0x0000.02215bee 0x0040000b 0x0000.000.00000000 0x00000001 0x00000000 0x0000
0x13 9 0x00 0x025d 0x000d 0x0000.02215c03 0x0040000b 0x0000.000.00000000 0x00000001 0x00000000 0x0000
0x14 9 0x00 0x025d 0x005a 0x0000.02215bdf 0x0040000a 0x0000.000.00000000 0x00000001 0x00000000 0x0000
0x15 9 0x00 0x025d 0x0058 0x0000.0220a587 0x0040000a 0x0000.000.00000000 0x00000001 0x00000000 0x0000
0x16 9 0x00 0x025d 0x000a 0x0000.02215bf6 0x0040000b 0x0000.000.00000000 0x00000001 0x00000000 0x0000
0x17 9 0x00 0x025d 0x000b 0x0000.0220a58e 0x0040000a 0x0000.000.00000000 0x00000001 0x00000000 0x0000
0x18 9 0x00 0x025d 0x0011 0x0000.02215bf0 0x0040000b 0x0000.000.00000000 0x00000001 0x00000000 0x0000
0x19 9 0x00 0x025c 0x0044 0x0000.021ff410 0x004001a0 0x0000.000.00000000 0x00000001 0x00000000 0x0000
0x1a 9 0x00 0x025d 0x005c 0x0000.02215bea 0x0040000b 0x0000.000.00000000 0x00000001 0x00000000 0x0000
0x1b 9 0x00 0x025d 0x001d 0x0000.02215bfd 0x0040000b 0x0000.000.00000000 0x00000001 0x00000000 0x0000
0x1c 9 0x00 0x025d 0x001a 0x0000.02215be8 0x0040000b 0x0000.000.00000000 0x00000001 0x00000000 0x0000
0x1d 9 0x00 0x025d 0x0009 0x0000.02215bff 0x0040000b 0x0000.000.00000000 0x00000001 0x00000000 0x0000
0x1e 9 0x00 0x025d 0x005f 0x0000.02215bfa 0x0040000b 0x0000.000.00000000 0x00000001 0x00000000 0x0000
0x1f 9 0x00 0x025c 0x0032 0x0000.021fa59b 0x0040019f 0x0000.000.00000000 0x00000001 0x00000000 0x0000
0x20 9 0x00 0x025c 0x0038 0x0000.021fa599 0x0040019f 0x0000.000.00000000 0x00000001 0x00000000 0x0000
可用以下命令分析smon的10015 trace，并列出相关回滚段名
[oracle@rh2 bdump]$ cat g10r2_smon_18738.trc|grep "usn ="|grep -v "usn = 0" |awk '{print "_SYSSMU"$7"$"}'|sort -u
_SYSSMU1$
_SYSSMU10$
_SYSSMU2$
_SYSSMU3$
_SYSSMU4$
_SYSSMU5$
_SYSSMU6$
_SYSSMU7$
_SYSSMU8$
_SYSSMU9$
了解你所不知道的SMON功能(九):维护MON_MODS$字典基表
August 22, 2011
By
1 Comment
SMON后台进程的作用还包括维护MON_MODS$基表，当初始化参数STATISTICS_LEVEL被设置为TYPICAL或ALL时默认会启用Oracle中表监控的特性，Oracle会默认监控表上的自上一次分析以后(Last analyzed)发生的INSERT,UPDATE,DELETE以及表是否被TRUNCATE截断，并将这些操作数量的近似值记录到数据字典基表MON_MODS$中，我们常用的一个DML视图dba_tab_modifications的数据实际来源于另一个数据字典基表MON_MODS_ALL$，SMON定期会将MON_MODS$中符合要求的数据MERGE到MON_MODS_ALL$中。
Rem DML monitoring
create table mon_mods$
(
obj# number, /* object number */
inserts number, /* approx. number of inserts since last analyze */
updates number, /* approx. number of updates since last analyze */
deletes number, /* approx. number of deletes since last analyze */
timestamp date, /* timestamp of last time this row was changed */
flags number, /* flags */
/* 0x01 object has been truncated */
drop_segments number /* number of segemnt in part/subpartition table */
)
storage (initial 200K next 100k maxextents unlimited pctincrease 0)
/
create unique index i_mon_mods$_obj on mon_mods$(obj#)
storage (maxextents unlimited)
/
Rem DML monitoring, has info aggregated to global level for paritioned objects
create table mon_mods_all$
(
obj# number, /* object number */
inserts number, /* approx. number of inserts since last analyze */
updates number, /* approx. number of updates since last analyze */
deletes number, /* approx. number of deletes since last analyze */
timestamp date, /* timestamp of last time this row was changed */
flags number, /* flags */
/* 0x01 object has been truncated */
drop_segments number /* number of segemnt in part/subpartition table */
)
storage (initial 200K next 100k maxextents unlimited pctincrease 0)
/
create unique index i_mon_mods_all$_obj on mon_mods_all$(obj#)
storage (maxextents unlimited)
/
Rem =========================================================================
Rem End Usage monitoring tables
Rem =========================================================================
VIEW DBA_TAB_MODIFICATIONS
select u.name, o.name, null, null,
m.inserts, m.updates, m.deletes, m.timestamp,
decode(bitand(m.flags,1),1,'YES','NO'),
m.drop_segments
from sys.mon_mods_all$ m, sys.obj$ o, sys.tab$ t, sys.user$ u
where o.obj# = m.obj# and o.obj# = t.obj# and o.owner# = u.user#
union all
select u.name, o.name, o.subname, null,
m.inserts, m.updates, m.deletes, m.timestamp,
decode(bitand(m.flags,1),1,'YES','NO'),
m.drop_segments
from sys.mon_mods_all$ m, sys.obj$ o, sys.user$ u
where o.owner# = u.user# and o.obj# = m.obj# and o.type#=19
union all
select u.name, o.name, o2.subname, o.subname,
m.inserts, m.updates, m.deletes, m.timestamp,
decode(bitand(m.flags,1),1,'YES','NO'),
m.drop_segments
from sys.mon_mods_all$ m, sys.obj$ o, sys.tabsubpart$ tsp, sys.obj$ o2,
sys.user$ u
where o.obj# = m.obj# and o.owner# = u.user# and
o.obj# = tsp.obj# and o2.obj# = tsp.pobj#
现象：
SMON后台进程会每15分钟将SGA中的DML统计信息刷新到SYS.MON_MODS$基表中(SMON flush every 15 minutes to SYS.MON_MODS$)，
同时会将SYS.MON_MODS$中符合要求的数据MERGE合并到MON_MODS_ALL$中，并清空原MON_MODS$中的数据。
MON_MODS_ALL$作为dba_tab_modifications视图的数据来源，起到辅助统计信息收集的作用,详见拙作
<Does GATHER_STATS_JOB gather all objects’ stats every time?>
。
SMON具体将DML统计数据刷新到SYS.MON_MODS$、合并到MON_MODS_ALL$、并清除数据的操作如下：
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
www.oracledatabase12g.com
/* 填充mon_mods$字典基表 */
lock table sys.mon_mods$ in exclusive mode nowait
insert into sys.mon_mods$
(obj#, inserts, updates, deletes, timestamp, flags, drop_segments)
values
(:1, :2, :3, :4, :5, :6, :7)
update sys.mon_mods$
set inserts = inserts + :ins,
updates = updates + :upd,
deletes = deletes + :del,
flags =
(decode(bitand(flags, :flag), :flag, flags, flags + :flag)),
drop_segments = drop_segments + :dropseg,
timestamp = :time
where obj# = :objn
lock table sys.mon_mods_all$ in exclusive mode
/* 以下merge命令会将mon_mods$中的记录合并到mon_mods_all$,
若有匹配的记录，则在原记录的基础上增加inserts、updates、deletes总数，
否则插入新的记录
*/
merge /*+ dynamic_sampling(mm 4) dynamic_sampling_est_cdn(mm)
dynamic_sampling(m 4) dynamic_sampling_est_cdn(m) */
into sys.mon_mods_all$ mm
using (select m.obj# obj#,
m.inserts inserts,
m.updates updates,
m.deletes deletes,
m.flags flags,
m.timestamp timestamp,
m.drop_segments drop_segments fr om sys.mon_mods$ m,
tab$ t where m.obj# = t.obj#) v
on (mm.ob j# = v.obj#)
when matched then
update
set mm.inserts = mm.inserts + v.inserts,
mm.updates = mm.updates + v.updates,
mm.deletes = mm.deletes + v.deletes,
mm.flags = mm.flags + v.flags - bitand(mm.flags, v.flags) /* bitor(mm.flags,v.flags) */,
mm.timestamp = v.timestamp,
mm.drop_segments = mm.drop_segments + v.drop_segments
when NOT matched then
insert
(obj#, inserts, updates, deletes, timestamp, flags, drop_segments)
values
(v.obj#,
v.inserts,
v.updates,
v.deletes,
sysdate,
v.flags,
v.drop_segments) / all merge /*+ dynamic_sampling(mm 4) dynamic_sampling_est_cdn(mm)
dynamic_sampling(m 4) dynamic_sampling_est_cdn(m) */
into sys.mon_mods_all$ mm using
(select m.obj# obj#,
m.inserts inserts,
m.updates updates,
m.deletes deletes,
m.flags flags,
m.timestamp timestamp,
m.drop_segments drop_segments fr om sys.mon_mods$ m,
tab$ t where m.obj# = t.obj#) v on
(mm.ob j# = v.obj#)
when matched then
update
set mm.inserts = mm.inserts + v.inserts,
mm.updates = mm.updates + v.updates,
mm.deletes = mm.deletes + v.deletes,
mm.flags = mm.flags + v.flags - bitand(mm.flags, v.flags)
/* bitor(mm.flags,v.flags) */,
mm.timestamp = v.timestamp,
mm.drop_segments = mm.drop_segments + v.drop_segments
when NOT matched then
insert
(obj#, inserts, updates, deletes, timestamp, flags, drop_segments)
values
(v.obj#,
v.inserts,
v.updates,
v.deletes,
sysdate,
v.flags,
v.drop_segments)
/* 最后删除sys.mon_mods$上的相关记录 */
delete /*+ dynamic_sampling(m 4) dynamic_sampling_est_cdn(m) */
from sys.mon_mods$ m
where exists (select /*+ unnest */
*
from sys.tab$ t
where t.obj# = m. obj#)
select obj#
from sys.mon_mods$
where obj# not in (select obj# from sys.obj$)
Used to have a FULL TABLE SCAN on obj$ associated with monitoring information
extracted in conjunction with mon_mods$ executed by SMON periodically.
因为当SMON或用户采用”DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO”存储过程将DML数据刷新到mon_mods$或mon_mods_all$中时会要求持有表上的排它锁，所以在RAC环境中可能出现死锁问题。
另外在早期版本中SMON可能因维护监控表而造成shutdown immediate缓慢或系统性能下降的问题，详见:
<Shutdown immediate hangs if table monitoring enabled on [ID 263217.1]>
<Bug 2806297 – SMON can cause bad system performance if TABLE MONITORING enabled on lots of tables [ID 2806297.8]>
SMON维护MON_MODS$时相关的Stack CALL
kglpnal <- kglpin <- kxsGetRuntimeLock
<- kksfbc <- kkspsc0 <- kksParseCursor <- opiosq0 <- opiall0
<- opikpr <- opiodr <- PGOSF175_rpidrus <- skgmstack <- rpiswu2
<- kprball <- kprbbnd0 <- kprbbnd <- ksxmfmel <- ksxmfm
<- ksxmfchk <- ksxmftim <- ktmmon <- ktmSmonMain <- ksbrdp
<- opirip <- opidrv <- sou2o <- opimai_real <- ssthrdmain
<- main <- libc_start_main <- start
如何禁止SMON维护MON_MODS$
注意在缺省参数环境中创建的表总是启用table monitoring的:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> create table maclean1 (t1 int);
Table created.
/* 在10g以后nomonitoring或monitoring选项不再有效 */
SQL> create table maclean2 (t1 int) nomonitoring;
Table created.
SQL> select table_name,monitoring from dba_tables where table_name like 'MACLEAN%';
TABLE_NAME MON
------------------------------ ---
MACLEAN1 YES
MACLEAN2 YES
通常来说我们不需要禁止SMON维护MON_MODS$，除非是在SMON维护过程中遭遇shutdown过慢、性能降低或者异常情况恢复SMON随机terminate实例的问题。
在10g以前可以使用MONITORING和NOMONITORING这2个选项来控制表级别的监控是否被开启，此外我们还可以通过dbms_stats.ALTER_SCHEMA_TAB_MONITORING(‘maclean’,false)存储过程在schema级别的monitoring是否被开启，但是在10g以后这些方法不再有效，MONITORING和NOMONITORING选项被废弃(In 10g the MONITORING and NOMONITORING keywords are deprecated and will be ignored.)，其原有功能被STATISTICS_LEVEL参数所覆盖。
Table-monitoring特性现在完全由STATISTICS_LEVEL参数所控制：
当STATISTICS_LEVEL设置为BASIC时，Table-monitoring将被禁用
当STATISTICS_LEVEL设置为TYPICAL或ALL时,Table-monitoring将启用
换而言之我们可以通过设置STATISTICS_LEVEL为BASIC达到禁止SMON后台进程该种功能的作用，具体修改该参数的命令如下：
show parameter statistics_level
alter system set statistics_level = basic;
但是请注意如果你正在使用AMM或ASMM自动内存管理特性的话，那么STATISTICS_LEVEL参数是不能设置为BASIC的，因为Auto-Memory或Auto-Sga特性都依赖于STATISTICS_LEVEL所控制的性能统计信息。若一定要这样做那么首先要diable AMM&ASMM:
#diable 11g AMM ,have to bounce instance
#alter system set memory_target =0 scope=spfile;
#diable 10g ASMM
alter system set sga_target=0;
alter system set statistics_level = basic;
了解你所不知道的SMON功能(十):维护SMON_SCN_TIME字典基表
November 13, 2011
By
2 Comments
SMON后台进程的作用还包括维护SMON_SCN_TIME基表。
SMON_SCN_TIME基表用于记录过去时间段中SCN(system change number)与具体的时间戳(timestamp)之间的映射关系，因为是采样记录这种映射关系，所以SMON_SCN_TIME可以较为较为粗糙地(不精确地)定位某个SCN的时间信息。实际的SMON_SCN_TIME是一张cluster table簇表。
SMON_SCN_TIME时间映射表最大的用途是为闪回类型的查询(flashback type queries)提供一种将时间映射为SCN的途径(The SMON time mapping is mainly for flashback type queries to map a time to an SCN)。
Metalink文档<Error ORA-01466 while executing a flashback query. [ID 281510.1]>介绍了SMON更新SMON_SCN_TIME的规律：
-
在版本10g中SMON_SCN_TIME每6秒钟被更新一次(In Oracle Database 10g, smon_scn_time is updated every 6 seconds hence that is the minimum time that the flashback query time needs to be behind the timestamp of the first change to the table.)
-
在版本9.2中SMON_SCN_TIME每5分钟被更新一次(In Oracle Database 9.2, smon_scn_time is updated every 5 minutes hence the required delay between the flashback time and table properties change is at least 5 minutes.)
另外从10g开始SMON也会清理SMON_SCN_TIME中的记录了，SMON后台进程会每5分钟被唤醒一次，检查SMON_SCN_TIME在磁盘上的映射记录总数，若总数超过144000条，则会使用以下语句删除最老的一条记录(time_mp最小):
delete from smon_scn_time
where thread = 0
and time_mp = (select min(time_mp) from smon_scn_time where thread = 0)
若仅仅删除一条记录不足以获得足够的空间，那么SMON会反复多次执行以上DELETE语句。
触发场景
虽然Metalink文档<Error ORA-01466 while executing a flashback query. [ID 281510.1]>指出了在10g中SMON会以每6秒一次的频率更新SMON_SCN_TIME基表，但是实际观测可以发现更新频率与SCN的增长速率相关，在较为繁忙的实例中SCN的上升极快时SMON可能会以6秒一次的最短间隔频率更新 ， 但是在空闲的实例中SCN增长较慢，则仍会以每5或10分钟一次频率更新，例如：
[oracle@vrh8 ~]$ ps -ef|grep smon|grep -v grep
oracle 3484 1 0 Nov12 ? 00:00:02 ora_smon_G10R21
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
www.oracledatabase12g.com & www.askmaclean.com
SQL> oradebug setospid 3484;
Oracle pid: 8, Unix process pid: 3484, image: oracle@vrh8.oracle.com (SMON)
SQL> oradebug event 10500 trace name context forever,level 10 : 10046 trace name context forever,level 12;
Statement processed.
SQL>
SQL> oradebug tracefile_name;
/s01/admin/G10R21/bdump/g10r21_smon_3484.trc
/* 等待一定时间 */
找出SMON trace文件中insert数据到SMON_SCN_TIME的记录:
grep -A20 "insert into smon_scn_time" /s01/admin/G10R21/bdump/g10r21_smon_3484.trc
insert into smon_scn_time (thread, time_mp, time_dp, scn, scn_wrp, scn_bas, num_mappings, tim_scn_map)
values (0, :1, :2, :3, :4, :5, :6, :7)
END OF STMT
PARSE #4:c=0,e=43,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1290280848899596
BINDS #4:
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=7fb29844edb8 bln=22 avl=06 flg=05
value=767145793
Bind#1
oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=8 off=0
kxsbbbfp=7fff023ae780 bln=07 avl=07 flg=09
value="11/14/2011 0:3:13"
Bind#2
oacdty=02 mxl=22(04) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=7fff023ae70c bln=22 avl=04 flg=09
value=954389
Bind#3
--
insert into smon_scn_time (thread, time_mp, time_dp, scn, scn_wrp, scn_bas, num_mappings, tim_scn_map)
values (0, :1, :2, :3, :4, :5, :6, :7)
END OF STMT
PARSE #1:c=0,e=21,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1290281434933390
BINDS #1:
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=7fb29844edb8 bln=22 avl=06 flg=05
value=767146393
Bind#1
oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=8 off=0
kxsbbbfp=7fff023ae780 bln=07 avl=07 flg=09
value="11/14/2011 0:13:13"
Bind#2
oacdty=02 mxl=22(04) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=7fff023ae70c bln=22 avl=04 flg=09
value=954720
Bind#3
--
insert into smon_scn_time (thread, time_mp, time_dp, scn, scn_wrp, scn_bas, num_mappings, tim_scn_map)
values (0, :1, :2, :3, :4, :5, :6, :7)
END OF STMT
PARSE #3:c=0,e=20,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1290281727955249
BINDS #3:
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=7fb29844e960 bln=22 avl=06 flg=05
value=767146993
Bind#1
oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=8 off=0
kxsbbbfp=7fff023ae780 bln=07 avl=07 flg=09
value="11/14/2011 0:23:13"
Bind#2
oacdty=02 mxl=22(04) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=7fff023ae70c bln=22 avl=04 flg=09
value=954926
Bind#3
insert into smon_scn_time (thread, time_mp, time_dp, scn, scn_wrp, scn_bas, num_mappings, tim_scn_map)
values (0, :1, :2, :3, :4, :5, :6, :7)
END OF STMT
PARSE #4:c=0,e=30,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1290282313990553
BINDS #4:
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=7fb29844edb8 bln=22 avl=06 flg=05
value=767147294
Bind#1
oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=8 off=0
kxsbbbfp=7fff023ae780 bln=07 avl=07 flg=09
value="11/14/2011 0:28:14"
Bind#2
oacdty=02 mxl=22(04) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=7fff023ae70c bln=22 avl=04 flg=09
value=955036
Bind#3
可以通过以上INSERT语句的TIME_DP绑定变量值中发现其更新SMON_SCN_TIME的时间规律，一般为5或10分钟一次。这说明SMON_SCN_TIME的更细频率与数据库实例的负载有关，其最短的间隔是每6秒一次，最长的间隔为10分钟一次。
由于SMON_SCN_TIME的更新频率问题可能引起ORA-01466错误，详见:
Error ORA-01466 while executing a flashback query. [ID 281510.1]
由于SMON_SCN_TIME的数据不一致可能引起ORA-00600[6711]或频繁地执行”delete from smon_scn_time”删除语句，详见：
ORA-00600[6711]错误一例
High Executions Of Statement “delete from smon_scn_time…” [ID 375401.1]
SMON维护SMON_SCN_TIME时相关的Stack CALL，ktf_scn_time是更新SMON_SCN_TIME的主要函数:
ksedst ksedmp ssexhd kghlkremf kghalo kghgex kghalf kksLoadChild kxsGetRuntimeLock kksfbc
kkspsc0 kksParseCursor opiosq0 opiall0 opikpr opiodr rpidrus skgmstack rpidru rpiswu2 kprball
ktf_scn_time
ktmmon ktmSmonMain ksbrdp opirip opidrv sou2o opimai_real main main_opd_entry
SMON 还可能使用以下SQL语句维护SMON_SCN_TIME字典基表：
select smontabv.cnt,
smontab.time_mp,
smontab.scn,
smontab.num_mappings,
smontab.tim_scn_map,
smontab.orig_thread
from smon_scn_time smontab,
(select max(scn) scnmax,
count(*) + sum(NVL2(TIM_SCN_MAP, NUM_MAPPINGS, 0)) cnt
from smon_scn_time
where thread = 0) smontabv
where smontab.scn = smontabv.scnmax
and thread = 0
insert into smon_scn_time
(thread,
time_mp,
time_dp,
scn,
scn_wrp,
scn_bas,
num_mappings,
tim_scn_map)
values
(0, :1, :2, :3, :4, :5, :6, :7)
update smon_scn_time
set orig_thread = 0,
time_mp = :1,
time_dp = :2,
scn = :3,
scn_wrp = :4,
scn_bas = :5,
num_mappings = :6,
tim_scn_map = :7
where thread = 0
and scn = (select min(scn) from smon_scn_time where thread = 0)
delete from smon_scn_time
where thread = 0
and scn = (select min(scn) from smon_scn_time where thread = 0)
如何禁止SMON更新SMON_SCN_TIME基表
可以通过设置诊断事件event=’12500 trace name context forever, level 10′来禁止SMON更新SMON_SCN_TIME基表(Setting the 12500 event at system level should stop SMON from updating the SMON_SCN_TIME table.):
SQL> alter system set events '12500 trace name context forever, level 10';
System altered.
一般我们不推荐禁止SMON更新SMON_SCN_TIME基表，因为这样会影响flashback Query闪回查询的正常使用，但是在某些异常恢复的场景中SMON_SCN_TIME数据讹误可能导致实例的Crash，那么可以利用以上12500事件做到不触发SMON_SCN_TIME被更新。
如何手动清除SMON_SCN_TIME的数据
因为SMON_SCN_TIME不是bootstrap自举核心对象，所以我们可以手动更新该表上的数据、及重建其索引。
如我在
<ORA-00600[6711]错误一例>
中介绍了因为SMON_SCN_TIME与其索引的数据不一致时，可以通过重建索引来解决问题：
connect / as sysdba
drop index smon_scn_time_scn_idx;
drop index smon_scn_time_tim_idx;
create unique index smon_scn_time_scn_idx on smon_scn_time(scn);
create unique index smon_scn_time_tim_idx on smon_scn_time(time_mp);
analyze table smon_scn_time validate structure cascade;
可以在设置了12500事件后手动删除SMON_SCN_TIME上的记录，重启实例后SMON会继续正常更新SMON_SCN_TIME。除非是因为SMON_SCN_TIME表上的记录与索引smon_scn_time_tim_idx或smon_scn_time_scn_idx上的不一致造成DELETE语句无法有效删除该表上的记录:文档
<LOCK ON SYS.SMON_SCN_TIME [ID 747745.1]>
说明了该问题，否则我们没有必要手动去清除SMON_SCN_TIME上的数据。
具体方法如下：
SQL> conn / as sysdba
/* Set the event at system level */
SQL> alter system set events '12500 trace name context forever, level 10';
/* Delete the records from SMON_SCN_TIME */
SQL> delete from smon_scn_time;
SQL> commit;
SQL> alter system set events '12500 trace name context off';
完成以上步骤后重启实例restart instance
shutdown immediate;
startup;
了解你所不知道的SMON功能(十一):OFFLINE UNDO SEGMENT
June 7, 2012
By
1 Comment
SMON这个老牌的后台关键进程的作用还包括对UNDO/ROLLBACK SEGMENT的维护， 这种维护主要体现在2个方面: OFFLINE和SHRINK UNDO/ROLLBACK SEGMENT， 今天我们主要介绍OFFLINE ROLLBACK SEGMENT。
你肯定要问，Oracle为什么OFFLINE UNDO/ROLLBACK SEGMENT?
最主要的目的是减轻高并发事务环境中对UDNO SPACE撤销空间使用的压力。
触发场景
在10g之前的9i中每12个小时SMON会根据V$UNDOSTAT中记录来决定在现有基础上要OFFLINE多少个UNDO SEGMENT，又要保留多少个UNDO SEGMENT； 在9i中被OFFLINED UNDO SEGMENT 还会被SMON DROP掉，以进一步回收空间。
具体保留多少个UNDO SEGMENT，取决于过去12个小时内的V$UNDOSTAT动态视图记录的最大并发事务数量在加上1，具体公式可以参考下面的SQL:
SQL> select max(MAXCONCURRENCY)+1 from v$undostat where begin_time> (sysdate-1/2);
MAX(MAXCONCURRENCY)+1
---------------------
4
若你在alert.log中发现类似以下的信息则说明OFFLINE UNDO SEGS已经在你的系统中发生过了:
SMON offlining US=13
Freeing IMU pool for usn 13
SMON offlining US=14
SMON offlining US=15
SMON offlining US=16
SMON offlining US=17
9i中SMON通过ktusmofd函数实现对UDNO SEGMENT的OFFLINE，ktusmofd的含义为[K]ernel [T]ransaction [U]ndo [S]ystem [M]anaged OFFLINE & DROP
通过ktsmgfru函数返回必要保留的ONLINE UNDO SEGMENT， 其详细的算法如下：
SMON调用ktusmofd ，并发现instance启动未超过12个小时并且_smu_debug_mode未设置KTU_DEBUG_SMU_SMON_SHRINK标志位
(_smu_debug_mode是SYSTEM MANAGED UNDO内部参数，KTU_DEBUG_SMU_SMON_SHRINK标志位控制是否强制SMON做SHRINK)
YES - SMON不OFFLINE任何东西直接返回
NO - 调用ktsmgfru 获得过去12小时的最大并发事务数
设置keep_online变量为ktsmgfru 返回值加上1
尝试hold TA ENQUEUE(该队列锁控制UNDO TABLESPACE的串行操作)，该操作的超时限制为30s
若无法获得该ENQUEUE则说明正在切换UNDO TABLESPACE，ktusmofd将直接返回且不OFFLINE任何UNDO SEGMENTS
成功获得该ENQUEUE锁，进一步调用ktusmofxu并使用之前获得的keep_online作为参数，开始OFFLINE
调用kslgpl函数获得KTU LATCH 包括parent和所有的children
LOOP 在现有的ONLINE UNDO SEGMENT之间循环
若发现该UNDO SEGMENT是SMU-SYSTEM MANAGED UNDO且其所在表空间是当前undo_tablespace指向的表空间的话
若keep_online >0 ， 则keep_online--
否则
释放KTU latches
调用kturof1函数实际OFFLINE 该UNDO SEGMENT
重新get KTU latches
END LOOP
释放 KTU latches
SMON 调用ktusmofd维护OFFLINE UNDO SEGMENT的常见STACK CALL如下：
ktmmon->ktusmofd->ktusmdxu->ktcrcm->ktccpcmt->ktcccdel->ktadrpc->ktssdro_segment->
ktssdrbm_segment->ktsxbmdelext->kqrcmt->ktsscu
xctrol ktcpoptx ktccpcmt ktcrcm ktusmdxu ktusmofd ktmmon
ksedmp ksfdmp kgeasnmierr ktusmgmct ktusmdxu ktusmofd ktmmon ksbrdp opirip
opidrv sou2o main
10g以前的UNDO OFFLINE算法仍不完善，这导致在实例重启或切换UNDO TABLESPACE撤销表空间时，生成一定数量ONLINE UNDO SEGMENT的系统预热时间可能长达几分钟，对于高并发的环境来说这种延时是难以接受的。
从10g开始改进了SMON OFFLINE UNDO SEGMENT的算法，SMON会基于过去7天的(而非12个小时的)V$UNDOSTAT动态视图信息或者AWR自动负载仓库中的UNDO历史快照使用信息来决定OFFLINE UNDO SEGMENT的数量， 且在10g中SMON 不再DROP掉多余的UNDO SEGS，而仅仅OFFLINE掉；作为一种SMU的改良算法这种做法被叫做”Fast Ramp-Up”。”Fast Ramp-Up”避免了早期版本中由SMON维护UNDO SEGS引起的等待或性能问题; 此外，未公开的BUG5079978可能在版本10.2.0.1中被触发，该BUG的信息如下:
Unpublished
Bug 5079978 – APPST GSI 10G : – PRODUCTION INSTANCE UNUSABLE DUE TO US ENQUEUE WAITS
is fixed in 11.1 and patch set 10.2.0.4 and interim patches are available for several earlier versions.
Please refer to Note 5079978.8
可以通过后面要介绍的 10511 event来规避以上bug，Oracle官方也推荐在10g以前的版本中使用10511 event来避免SMON过度OFFLINE UNDO SEGS所引起的问题。
10g以后的具体算法如下：
判断实例启动是否超过7天？
YES - 直接使用v$undostat中过去7天的最大并发事务数max(maxconcurrency)
NO - 判断是否是第一次调用OFFLINE UNDO SEGMENT的内核函数
YES - 检查是否存在select_workload_repository function (SWRF)快照数据
NO - ONLINE 最小数目的UNDO SEGMENTS
YES - 尝试获取AWR记录表wrh$_undostat中过去7天的最大并发事务数max(maxconcurrency)
若无法获得以上值，则尝试读取wrh$_rollstat中最近7天的最大rollback segs数量max(rbs cnt)
将返回值保存到内部变量中
NO - 直接使用内部变量中的值
如何禁止SMON OFFLINE UNDO SEGMENT?
可以通过设置诊断事件event=’10511trace name context forever, level 1′ 来禁用SMON OFFLINE UNDO SEGS; 但是10511事件不会跳过”Fast Ramp Up”，而仅会限制SMON对UNDO SEGS产生的工作负载。 一旦设置了10511 event， 则所有已生成的 UNDO SEGS会始终保持ONLINE状态。
具体的设置方法：
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
www.oracledatabase12g.com
[oracle@vrh8 ~]$ oerr ora 10511
10511, 00000, "turn off SMON check to cleanup undo dictionary"
// *Cause:
// *Action:
SQL> alter system set events '10511 trace name context forever,level 1';
System altered.
OFFLINE UNDO SEGS的相关BUG
以下列出了SMON OFFLINE UNDO SEGS的一些公开的BUG，这些BUG一般都存在于10.2.0.3之前; 若你真的遇到了，可以在考虑升级之余 采用10511 event workaround规避该问题：
Hdr: 2726601 9.2.0.2 RDBMS 9.2.0.2 TXN MGMT LOCAL PRODID-5 PORTID-46 ORA-600 3439552
Abstract: ORA-600 [4406] IN ROUTINE KTCRAB(); 4 NODE RAC CLUSTER
Hdr: 6878461 9.2.0.4.0 RDBMS 9.2.0.4.0 TXN MGMT LOCAL PRODID-5 PORTID-23 ORA-601 5079978
Abstract: ESSC: ORA-601 ORA-474 AFTER OFFLINING UNDO SEGMENTS
Hdr: 4253991 9.2.0.4.0 RDBMS 9.2.0.4.0 TXN MGMT LOCAL PRODID-5 PORTID-23 ORA-600 2660394
Abstract: ORA-600 [KTSXR_ADD-4] FOLLOWED BY ORA-600 [KTSISEGINFO1]
Hdr: 2696314 9.2.0.2.0 RDBMS 9.2.0.2.0 TXN MGMT LOCAL PRODID-5 PORTID-46
Abstract: RECEIVING ORA-600: [KTUSMGMCT-01] AFTER APPLYING 92020 PATCH SET
Hdr: 3578807 9.2.0.4 RDBMS 9.2.0.4 TXN MGMT LOCAL PRODID-5 PORTID-23 ORA-600
Abstract: OERI 4042 RAISED INTERMITTENTLY
Hdr: 2727303 9.2.0.1.0 RDBMS 9.2.0.1.0 TXN MGMT LOCAL PRODID-5 PORTID-100 ORA-600
Abstract: [RAC] ORA-600: [KTUSMGMCT-01] ARE OCCURED IN HIGH LOAD
