了解你所不知道的SMON功能(九):维护MON

你所不知道的SMON功能(九):维护MON"/>

了解你所不知道的SMON功能(九):维护MON

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 monitoringcreate 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_MODIFICATIONSselect 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# ando.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 - ProductionSQL> select * from global_name;GLOBAL_NAME
--------------------------------------------------------------------------------
www.oracledatabase12g/* 填充mon_mods$字典基表 */lock table sys.mon_mods$ in exclusive mode nowaitinsert 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     = :timewhere obj# = :objnlock 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 thenupdateset 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 theninsert(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 thenupdateset 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 theninsert(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$ mwhere exists (select /*+ unnest */*from sys.tab$ twhere 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 - ProductionSQL> 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 - ProductionSQL> 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 ASMMalter system set sga_target=0;alter system set statistics_level = basic;

本文转自maclean_007 51CTO博客，原文链接: