mon_mods$ is used for DML monitoring

今天，在分析一个客户系统的数据库时，发现以下两个SQL占据了较高的解析及执行比例：

Parse Calls	Executions	% Total Parses	SQL Id	SQL Module	SQL Text
784	784	1.50	350f5yrnnmshs		lock table sys.mon_mods$ in ex...
784	784	1.50	g00cj285jmgsw		update sys.mon_mods$ set inser...

两个SQL的执行次数完全相同，显然一个是获取锁定，另外一个是执行更新操作。
SQL的完整文本如下：
lock table sys.mon_mods$ in exclusive mode nowait

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

那么这两个SQL是做什么用的呢？其底层表mon_mods$的功能又是什么呢？
从Oracle的代码里可以看到这个表是进行DML跟踪的，Oracle的自动统计数据收集功能会据此判断哪些表的数据发生了变化，需要进行重新的信息收集：

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)
/

 在某些版本中，会因为这个语句导致如下错误提示：
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

如果数据库关闭了自动统计信息收集功能，启用这个监控就是没有必要的了。

-The End-