MON_MODS$和MON_MODS_ALL$统计DML操作次数

mon_mods$ is used for DML monitoring

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

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

2011-08-22 23:40:33

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

Rem =========================================================================
Rem End Usage monitoring tables
Rem =========================================================================

VIEW DBA_TAB_MODIFICATIONS

select 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# and
      o.obj# = tsp.obj# and o2.obj# = tsp.pobj#

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.oracledatabase12g.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.

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

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

show parameter statistics_level
alter system set statistics_level = basic;

 #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;

在 Oracle9i 中，您可以通过打开表监视选项 (ALTER TABLE ...MONITORING) 然后检查这些表的视图 DBA_TAB_MODIFICATIONS，检查表中的数据是否已明显更改过。

在 10g 中，已不再使用 MONITORING 语句了。而通过设置初始化参数 STATISTIC_LEVEL 为 TYPICAL 或 ALL，就可以自动收集统计信息。（默认值为 TYPICAL，因此可以随即启用自动收集统计信息的功能。）Oracle 数据库 10g 具有一个预定义的调度程序作业，名称为 GATHER_STATS_JOB，它由 STATISTIC_LEVEL 参数的适当数值所激活。

SQL> show parameter statistics_

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------
statistics_level                     string      TYPICAL

statistics_level 默认是typical，在10g中表监控是激活的，强烈建议在10g中此参数的值是typical。如果STATISTICS_LEVEL设置为basic，不仅不能监控表，而且将禁掉如下一些10g的新功能：
ASH(Active Session History)
ASMM(Automatic Shared Memory Management)
AWR(Automatic Workload Repository)
ADDM(Automatic Database Diagnostic Monitor)

Oracle 9i 下面建立table默认是 NOMONITORING ，必须通过 alter table xxxx monitoring ;  来设置表的属性 。

CREATE TABLE SFIS1.C_BOM_KEYPART_T
(
  BOM_NO        VARCHAR2(16 BYTE)    NOT NULL,
  KEY_PART_NO   VARCHAR2(25 BYTE)    NOT NULL,
  KP_RELATION   NUMBER(2),
  KP_COUNT      NUMBER(2),
  VERSION_CODE  VARCHAR2(10 BYTE),
  GROUP_NAME    VARCHAR2(16 BYTE)
)
TABLESPACE BASE_DATA
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          1M
            NEXT             5M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
NOMONITORING;

不过到了Oracle10g ,  建立table默认是 MONITORING 。  

CREATE TABLE DFMS.WIP_D_FPF_DATA
(
  WORK_DATE          VARCHAR2(8 BYTE)    NOT NULL,
  PLANT_CODE         VARCHAR2(25 BYTE)   NOT NULL,
  SKU_NO             VARCHAR2(25 BYTE)   NOT NULL,
  ISCFI              VARCHAR2(1 BYTE)    NOT NULL,
  PRODUCTION_TYPE    VARCHAR2(20 BYTE)   NOT NULL 
)
TABLESPACE LOG_DATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          20M
            NEXT             20M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;   

9i， 10g 下的 statistics_level  参数默认都是typical .  

在数据库技术大会上，做了《构建高可用数据库监控系统》的分享以后，很多朋友对北斗如何实现表的DML次数监控有兴趣，会上因为时间的原因，我只是说有系统视图可以查到这个信息，因此有了本文，可以稍微详细一点来说明是如何实现的。

我说的系统视图，具体指的是dba_tab_modifications/all_tab_modifications/user_tab_modifications ，这几个视图收集了表自从上一次分析之后的DML累积次数。但是要注意，考虑到性能的影响，Oracle并不是实时统计这个数据的，在Oracle9i之前，约3个小时SMON进程会刷新一次数据，而Oracle9i以后这个时间间隔变成了15分钟。

因此以较高的频率来实时监控这个表的话，得到的并不是当前的准确数据。Oracle在dbms_stat包中提供了一个过程来手动刷新统计数据，假如在一天的业务低峰期采集一次数据的话，可以先执行该过程，就能得到较为准确的数据。但是，不建议在业务高峰期执行该过程。

exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

在Oracle10g之前，必须手工开启表的monitoring属性，才会将DML统计信息收集到这个视图中。可以通过dba_tables.monitoring列查看表是否已经开启了监控。关于这个变化，可以参考Metalink ID 252597.1

alter table test monitoring;

Oracle10g之后，只要statistics_level是TYPICAL（默认）或者ALL，就能自动收集信息了，即使给表设置为nomonitoring也不能阻止，这个表的属性已经被废弃了。

APPLIES TO:

PURPOSE

Describes the relationship between Table monitoring and STATISTICS_LEVEL parameter  

SCOPE

All DBA's.

DETAILS

Prior to Oracle 10g, automated collection of statistics for objects that had become stale was controlled by the setting of the MONITORING flag on table.
Depending on the MONITORING flag, the GATHER_STATS_JOB job collected "GATHER EMPTY" and "GATHER STALE" on the flagged objects.

In 10g the MONITORING and NOMONITORING keywords are deprecated and will be ignored when executing a CREATE or ALTER TABLE DDL statement with these keywords.

For Example:

Table-monitoring feature is now controlled by the STATISTICS_LEVEL parameter.

• When STATISTICS_LEVEL is set to BASIC, monitoring is disabled on the table.
• When STATISTICS_LEVEL is set to TYPICAL, then monitoring is enabled.

By default STATISTICS_LEVEL is set to TYPICAL and monitoring of tables is enabled. 
It is strongly recommended to set STATISTICS_LEVEL to TYPICAL in 10g.

By setting this parameter to BASIC, you will be disabling most of the manageability features in 10g.
The following will be disabled:

• ASH (Active Session History)
• AWR (Automatic Workload Repository)
• ASMM(Automatic Shared Memory Management)
• ADDM(Automatic Database Diagnostic Monitor)

Monitoring tracks the approximate number of INSERT, UPDATE, and DELETE operations for the table since the last time statistics were gathered. This information on "changes made" is maintained in the SGA and periodically (about every 15 minutes) the SMON flushes the data into the data dictionary tables. You can manually flush the information by calling dbms_stats.FLUSH_DATABASE_MONITORING_INFO(). The data dictionary information is made visible through the views: DBA_TAB_MODIFICATIONS, ALL_TAB_MODIFICATIONS and USER_TAB_MODIFICATIONS.

Oracle uses these views to identify tables that have stale statistics. 
Whenever there is 10% change in data in a table, Oracle considers its statistics to be stale.

Up to date statistics are important to generate good execution plans. Automatic statistics collection job using DBMS_STATS packages depend on the monitoring data to determine when to collect statistics on objects with stale statistics.

Stats are considered stale when #(INSERTS + UPDATES + DELETES) >= 10% of NUM_ROWS from dba_tables:

The 10000 is there so as to pick up only the biggest changes.

Example:

Step 1:

Create table EMP. Its description is as follows

SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- ------------------------------------
 EMPNO                                              NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> select count(*) from emp;

  COUNT(*)
----------
        14

SQL> select * from user_tab_modifications;

no rows selected

Initially there are 14 rows in EMP.

Step 2:

Set parameter STATISTICS_LEVEL='TYPICAL'

Step 3:

Insert additional 14 rows. This will increase the data in EMP by 50% and therefore the statistics in EMP will be regarded as stale by Oracle.

Step 4:

The information about the inserts, updates and deletes on tables will be in SGA. After every 15 minutes SMON will push this data in data-dictionary tables. You can view them by querying table dba_tab_modifications.

In this case, we have manually flushed the information using dbms_stats.FLUSH_DATABASE_MONITORING_INFO() to push the information about the modifications in the tables from SGA to data-dictionary tables:

SQL> exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO();

PL/SQL procedure successfully completed.

SQL> select * from user_tab_modifications;

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
SUBPARTITION_NAME                 INSERTS    UPDATES    DELETES TIMESTAMP TRU
------------------------------ ---------- ---------- ---------- --------- ---
EMP
                                       14          0          0 16-OCT-03 NO

Step 5:

If a monitored table has been modified more than 10%, then these statistics are considered stale

Prior to Oracle11g, the staleness threshold is hardcoded at 10%. This means that an object is considered stale if the number of rows inserted, updated or deleted since the last statistics gathering time is more than 10% of the number of rows. There is no way to modify this value prior to Oracle 11g.

Starting with Oracle11g, the staleness threshold can be set using the STALE_PERCENT statistics preference. This can be set globally using DBMS_STATS.SET_GLOBAL_PREFS or at the table level using DBMS_STATS.SET_TABLE_PREFS.

E.g. to modify and check the staleness threshold for table EMP in SCOTT schema:

Gather statistics on the tables whose statistics have become stale using the following command:

Step 6:

Query dba_tab_modifications to check whether statistics have been gathered on the table.

No rows in dba_tab_modifications indicates that statistics have been gathered on the table.

About Me

............................................................................................................................................. ● 本文整理自网络，若有侵权请联系小麦苗删除 ● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新 ● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/ ● 本文博客园地址：http://www.cnblogs.com/lhrbest ● 本文pdf版、个人简介及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/ ● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA宝典今日头条号地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826 ............................................................................................................................................. ● QQ群号：230161599（满）、618766405 ● 微信群：可加我微信，我拉大家进群，非诚勿扰 ● 联系我请加QQ好友（646634621），注明添加缘由 ● 于 2017-09-01 09:00 ~ 2017-09-30 22:00 在魔都完成 ● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解 ● 版权所有，欢迎分享本文，转载请保留出处 ............................................................................................................................................. ● 小麦苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail ● 小麦苗出版的数据库类丛书：http://blog.itpub.net/26736162/viewspace-2142121/ ............................................................................................................................................. 使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号（xiaomaimiaolhr）及QQ群（DBA宝典），学习最实用的数据库技术。    小麦苗的微信公众号      小麦苗的DBA宝典QQ群1     小麦苗的DBA宝典QQ群2        小麦苗的微店 .............................................................................................................................................