eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 使用ora_rowscn识别误操作数据时间点 | Blog首页 | 天道酬勤 - 9月13日将出席China-Pub9周年庆典 »

mon_mods$ is used for DML monitoring
modb.pro

今天,在分析一个客户系统的数据库时,发现以下两个SQL占据了较高的解析及执行比例:
Parse CallsExecutions % Total Parses SQL IdSQL ModuleSQL 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-








历史上的今天...
    >> 2011-09-09文章:
    >> 2008-09-09文章:
    >> 2005-09-09文章:
           Oracle and Hurricane Katrina

By eygle on 2009-09-09 08:20 | Comments (0) | FAQ | 2392 |


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com