ORACLE工具：表监视功能(table monitoring)

我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新 ，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。

ORACLE工具：表监视功能(table monitoring)

测试环境：DB:10.2.0.4

1，statistics_level的值

www.htz.pw >show parameter statistics_level

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

2，创建测试表

www.htz.pw >show user;
USER is "SCOTT"
www.htz.pw >create table modifications as select * from emp;

Table created.

www.htz.pw >select table_name,monitoring from user_tables where table_name='MODIFICATIONS';

TABLE_NAME                     MON
------------------------------ ---
MODIFICATIONS                  YES

这里人为的nomonitoring也不生效，其实在10G后，创建的所有的表默认都是已经monitoring了的，并且不允许我们禁用，除非我们修改statistics_level参数为basic。
如果修改此参数为basic,assm,asm,addm,ash等功能都不能正常使用。

www.htz.pw >alter table modifications nomonitoring;

Table altered.

www.htz.pw >select table_name,monitoring from user_tables where table_name='MODIFICATIONS';

TABLE_NAME                     MON
------------------------------ ---
MODIFICATIONS                  YES

表监视到行记录变化后，我们可以通过dba_tab_modifications表查看，下面是此表的底层表的信息：

www.htz.pw >set long 555555
www.htz.pw >select text from dba_views where view_name='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#

3，修改行记录

www.htz.pw >update modifications set ename='HTZ' where rownum=1;

1 row updated.

www.htz.pw >commit;

Commit complete.

www.htz.pw >show user
USER is "SYS"

这里我们手动flush一次信息，默认情况下是由smon进程每隔15分钟把内存中的信息刷到mon_mods$这张基表中去

www.htz.pw >exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

这里我们发现已经有相关的记录信息，注意这里的条件包括了rollback部分的，不用很准确

www.htz.pw >alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

www.htz.pw >select * from user_tab_modifications where table_name='MODIFICATIONS';

TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME                 INSERTS    UPDATES    DELETES TIMESTAMP           TRU DROP_SEGMENTS
------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ------------------- --- -------------
MODIFICATIONS                                                                                         0          1          0 2013-09-09 23:02:34 NO              0

千万注意这里的记录的条件不一定正确哦。

4，下面我们来看一下flush_database_monitoring_info后面到底在运行那些SQL

www.htz.pw >oradebug setmypid
Statement processed.
www.htz.pw >oradebug event 10046 trace name context forever,level 8;
Statement processed.
www.htz.pw >oradebug tracefile_name
/u01/app/oracle/admin/orcl10g/udump/orcl10g_ora_8209.trc
www.htz.pw >exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed

这里我们可以发现下面一些SQL，更新mon_mods$里面的记录，其实这里就是把内存中的数据更新到mon_mods$表中，后来再merge到mon_mods_all$中,最后是delete掉mon_mods$中的记录，并没有发现他们说的truncate操作。

lock table sys.mon_mods$ in exclusive mode
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
 
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#)
         
delete from sys.mon_mods$

         
lock table sys.mon_mods_all$ in exclusive mode

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
         from sys.mon_mods$ m, tab$ t
        where m.obj# = t.obj#) v
on (mm.obj# = 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)

------------------作者介绍-----------------------
姓名：黄廷忠
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)