了解你所不知道的SMON功能(九):维护MON_MODS$字典基表

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$中。

现象:

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.askmaclean.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;

沪ICP备14014813号

沪公网安备 31010802001379号