« 使用ora_rowscn识别误操作数据时间点 | Blog首页 | 天道酬勤 - 9月13日将出席China-Pub9周年庆典 »
mon_mods$ is used for DML monitoring
作者:eygle | 【转载请注出处】|【云和恩墨 领先的zData数据库一体机 | zCloud PaaS云管平台 | SQM SQL审核平台 | ZDBM 数据库备份一体机】
链接:https://www.eygle.com/archives/2009/09/mon_mods_is_use.html
今天,在分析一个客户系统的数据库时,发现以下两个SQL占据了较高的解析及执行比例:链接:https://www.eygle.com/archives/2009/09/mon_mods_is_use.html
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的完整文本如下:
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文章:
By eygle on 2009-09-09 08:20 | Comments (0) | FAQ | 2392 |