SYS.MON_MODS$、SYS.MON_MODS_ALL$和DBA_TAB_MODIFICATIONS

  1. 云栖社区>
  2. 博客>
  3. 正文

SYS.MON_MODS$、SYS.MON_MODS_ALL$和DBA_TAB_MODIFICATIONS

小麦苗 2017-09-19 13:30:01 浏览1291
展开阅读全文
SYS.MON_MODS$、SYS.MON_MODS_ALL$和DBA_TAB_MODIFICATIONS




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

*_TAB_MODIFICATIONS视图是用来记录自从收集统计信息后的DML(包括truncate)操作的次数,通过试验分析数据库是如何实现该功能,并且应用该功能来实现数据库自动收集功能(表变化比例决定是否收集统计信息)

SQL> select * from v$version;
 
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
 
SQL> DESC DBA_TAB_MODIFICATIONS
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_OWNER                                        VARCHAR2(30)
 TABLE_NAME                                         VARCHAR2(30)
 PARTITION_NAME                                     VARCHAR2(30)
 SUBPARTITION_NAME                                  VARCHAR2(30)
 INSERTS                                            NUMBER
 UPDATES                                            NUMBER
 DELETES                                            NUMBER
 TIMESTAMP                                          DATE
 TRUNCATED                                          VARCHAR2(3)
 DROP_SEGMENTS                                      NUMBER
 
SQL> select text from dba_views where view_name='DBA_TAB_MODIFICATIONS';
 
TEXT
--------------------------------------------------------------------------------
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#


这里很清楚,通过union all关联了三个select 语句,分别是查询普通表,分区表,子分区表,这里也可以看出来

对应基表

SQL> desc sys.mon_mods_all$
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJ#                                               NUMBER
 INSERTS                                            NUMBER
 UPDATES                                            NUMBER
 DELETES                                            NUMBER
 TIMESTAMP                                          DATE
 FLAGS                                              NUMBER
 DROP_SEGMENTS                                      NUMBER
 
SQL> desc sys.mon_mods$
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJ#                                               NUMBER
 INSERTS                                            NUMBER
 UPDATES                                            NUMBER
 DELETES                                            NUMBER
 TIMESTAMP                                          DATE
 FLAGS                                              NUMBER
 DROP_SEGMENTS                                      NUMBER


这两个基表结构完全相同,通过收集信息dml操作MON_MODS$然后记录MON_MODS_ALL$中

测试MON_MODS$和MON_MODS_ALL$关系


SQL> SELECT obj#,INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS$ WHERE obj#=69900;
 
      OBJ#    INSERTS    UPDATES    DELETES      FLAGS LAST_TIME           DROP_SEGMENTS
---------- ---------- ---------- ---------- ---------- ------------------- -------------
     69900          0        297          0          0 2013-05-03 01:35:56             0
 
SQL> !date
Fri May  3 01:51:08 CST 2013
 
SQL> SELECT obj#,INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS$ WHERE obj#=69900;
 
      OBJ#    INSERTS    UPDATES    DELETES      FLAGS LAST_TIME           DROP_SEGMENTS
---------- ---------- ---------- ---------- ---------- ------------------- -------------
     69900          0        297          0          0 2013-05-03 01:35:56             0
--15分钟未完全刷新mon_mod$
 
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
 
PL/SQL procedure successfully completed.
 
SQL> SELECT obj#,INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS$ WHERE obj#=69900;
 
no rows selected
 
SQL> SELECT obj#,INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS_ALL$ WHERE obj#=69900;
 
      OBJ#    INSERTS    UPDATES    DELETES      FLAGS LAST_TIME           DROP_SEGMENTS
---------- ---------- ---------- ---------- ---------- ------------------- -------------
     69900          0        323          0          0 2013-05-03 01:54:18             0

这里测试证明DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO实现MON_MODS$刷新到MON_MODS_ALL$,但是未完全刷新MON_MODS$

测试MON_MODS_ALL$
SQL> create table t_xifenfei
  as
  select object_id,object_name from dba_objects;
 
Table created.
 
SQL> select object_id from user_objects where object_name='T_XIFENFEI';
 
 OBJECT_ID
----------
     76703
 
SQL> SELECT COUNT(*) FROM T_XIFENFEI;
 
  COUNT(*)
----------
     74806
 
--MON_MODS$无数据
SQL> SELECT INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS$ WHERE OBJ#=76703;
 
no rows selected
 
--MON_MODS_ALL$中无数据
SQL> SELECT INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS_ALL$ WHERE OBJ#=76703;
 
no rows selected
 
--UPDATE 操作
SQL> UPDATE T_XIFENFEI SET OBJECT_NAME='WWW.XIFENFEI.COM' WHERE MOD(OBJECT_ID,10)=0;
 
7474 rows updated.
 
SQL> COMMIT;
 
Commit complete.
 
--MON_MODS$和MON_MODS_ALL$中无数据,因为未从内存中刷新到MON_MODS$
SQL> SELECT INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS$ WHERE OBJ#=76703;
 
no rows selected
 
SQL> SELECT INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS_ALL$ WHERE OBJ#=76703;
 
no rows selected
 
--执行DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO操作
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
 
PL/SQL procedure successfully completed.
 
--MON_MODS_ALL$中有数据
SQL> SELECT INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS_ALL$ WHERE OBJ#=76703;
 
   INSERTS    UPDATES    DELETES      FLAGS LAST_TIME           DROP_SEGMENTS
---------- ---------- ---------- ---------- ------------------- -------------
         0       7474          0          0 2013-02-12 09:02:05             0
--这里统计的update数据和实际更新条数一致
 
--MON_MODS$中无数据,因为FLUSH_DATABASE_MONITORING_INFO刷新SGA中的dml和MON_MODS$到MON_MODS_ALL$中,并清空MON_MODS$
SQL> SELECT INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS$ WHERE OBJ#=76703;
 
no rows selected
 
--DELETE操作
----session 1
SQL>  DELETE FROM T_XIFENFEI WHERE MOD(OBJECT_ID,3)=2;
 
24940 rows deleted.
 
----session 2
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
 
PL/SQL procedure successfully completed.
 
----session 1
SQL> SELECT INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS_ALL$ WHERE OBJ#=76703;
 
   INSERTS    UPDATES    DELETES      FLAGS LAST_TIME           DROP_SEGMENTS
---------- ---------- ---------- ---------- ------------------- -------------
         0       7474      24940          0 2013-02-12 09:04:15             0
------这里可以看到,未提交的DML操作也统计到MON_MODS_ALL$中
 
----session 1
SQL> rollback;
 
Rollback complete.
 
----session 2
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
 
PL/SQL procedure successfully completed.
 
----session 2
SQL> SELECT INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS_ALL$ WHERE OBJ#=76703;
 
   INSERTS    UPDATES    DELETES      FLAGS LAST_TIME           DROP_SEGMENTS
---------- ---------- ---------- ---------- ------------------- -------------
         0       7474      24940          0 2013-02-12 09:04:15             0
------通过这里可以rollback 操作也不能回滚MON_MODS_ALL$中未提交的事务
 
--再次DELETE操作
SQL> DELETE FROM T_XIFENFEI WHERE MOD(OBJECT_ID,5)=1;
 
14954 rows deleted.
 
----session 2
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
 
PL/SQL procedure successfully completed.
 
----session 1
SQL> SELECT INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS_ALL$ WHERE OBJ#=76703;
 
   INSERTS    UPDATES    DELETES      FLAGS LAST_TIME           DROP_SEGMENTS
---------- ---------- ---------- ---------- ------------------- -------------
         0       7474      39894          0 2013-02-12 09:05:54             0
------DELETE操作在MON_MODS_ALL$中累加
 
--INSERT操作
SQL> insert into t_xifenfei select object_id,object_name from dba_objects;
 
74806 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
 
PL/SQL procedure successfully completed.
 
 
SQL> SELECT INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS_ALL$ WHERE OBJ#=76703;
 
   INSERTS    UPDATES    DELETES      FLAGS LAST_TIME           DROP_SEGMENTS
---------- ---------- ---------- ---------- ------------------- -------------
     74806       7474      39894          0 2013-02-12 09:07:51             0
 
--收集统计信息
SQL> EXEC DBMS_STATS.gather_table_stats('CHF','T_XIFENFEI');
 
PL/SQL procedure successfully completed.
 
--MON_MODS_ALL$数据被清空
SQL> SELECT INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS_ALL$ WHERE OBJ#=76703;
 
no rows selected
 
--TRUNCATE表被清空
SQL> truncate table t_xifenfei;
 
Table truncated.
 
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
 
PL/SQL procedure successfully completed.
 
SQL> SELECT INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS_ALL$ WHERE OBJ#=76703;
 
   INSERTS    UPDATES    DELETES      FLAGS LAST_TIME           DROP_SEGMENTS
---------- ---------- ---------- ---------- ------------------- -------------
         0          0     134658          1 2013-02-12 09:29:49             0
----DELETES和FLAGS记录因为truncate操作而发生改变


从10GR2开始,数据库每15分钟就要把数据库的DML操作写入到mon_mods$(从SGA中写入到mon_mod$),但是这个写入过程1分钟,因此可能不是全部记录所有DML操作到mon_mods$.默认情况下,数据库每天会写入SGA中表的DML操作和mon_mods$到mon_mods_all$,也可以通过DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO来人工写入到mon_mods_all$中,收集统计信息后mon_mods_all$表中信息清空



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-


artType01.jpg 了解你所不知道的SMON功能(九):维护MON_MODS$字典基表
2011-08-22 23:40:33
原创作品,允许转载,转载时请务必以超链接形式标明文章 原始出处 、作者信息和本声明。否则将追究法律责任。http://maclean.blog.51cto.com/2923249/1277944
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$中。
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#
现象: 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视图的数据来源,起到辅助统计信息收集的作用,详见拙作。 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.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.
因为当SMON或用户采用"DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO"存储过程将DML数据刷新到mon_mods$或mon_mods_all$中时会要求持有表上的排它锁,所以在RAC环境中可能出现死锁问题。 另外在早期版本中SMON可能因维护监控表而造成shutdown immediate缓慢或系统性能下降的问题,详见:   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;

本文出自 “Ask Maclean Liu Oracle” 博客,请务必保留此出





在 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也不能阻止,这个表的属性已经被废弃了。


SQL> desc dba_tab_modifications
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_OWNER                                        VARCHAR2(30)
 TABLE_NAME                                         VARCHAR2(30)
 PARTITION_NAME                                     VARCHAR2(30)
 SUBPARTITION_NAME                                  VARCHAR2(30)
 INSERTS                                            NUMBER
 UPDATES                                            NUMBER
 DELETES                                            NUMBER
 TIMESTAMP                                          DATE
 TRUNCATED                                          VARCHAR2(3)
 DROP_SEGMENTS                                      NUMBER
Relationship between Table Monitoring and STATISTICS_LEVEL Parameter (文档 ID 252597.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Oracle Database - Personal Edition - Version 10.1.0.2 and later
Oracle Database - Standard Edition - Version 10.1.0.2 and later
Information in this document applies to any platform.

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:

ALTER TABLE <tablename> MONITORING; 
CREATE TABLE <tablename> MONITORING;
 


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:

select u.TIMESTAMP,
t.last_analyzed,
u.table_name,
u.inserts,
u.updates,
u.deletes,
d.num_rows,
decode(t.num_rows,0,'Table Stats indicate No Rows',
nvl(TO_CHAR(((U.inserts+u.deletes+u.updates)/d.num_rows) * 100,'999.99')
,'Null Value in USER_TAB_MODIFICATIONS')
) percent
from user_tables t,USER_TAB_MODIFICATIONS u,dba_tables d
where u.table_name = t.table_name
and d.table_name = t.table_name
and d.owner = '&Owner'
and (u.inserts > 10000 or u.updates > 10000 or u.deletes > 10000)
order by t.last_analyzed
/



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'

SQL> alter system set STATISTICS_LEVEL='TYPICAL';

System altered.

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.

SQL> insert into emp select * from emp;

14 rows created.
SQL>commit;

 

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:

SQL> exec dbms_stats.set_table_prefs(null,'EMP','STALE_PERCENT',17)

PL/SQL procedure successfully completed.


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

execute DBMS_STATS.GATHER_SCHEMA_STATS ('RAJIV', 
NULL, FALSE, 'FOR ALL COLUMNS SIZE 1', NULL, 'DEFAULT', 
TRUE, NULL, NULL, 'GATHER STALE','LIST' );

Step 6:

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

SQL> select * from user_tab_modifications;

no rows selected


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        小麦苗的微店

.............................................................................................................................................

img_e3029f287d989cd04bd75432ecc1c172.png
DBA笔试面试讲解群1
DBA笔试面试讲解群2
欢迎与我联系



网友评论

登录后评论
0/500
评论
小麦苗
+ 关注