sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle表监视功能table monitoring的使用案例

时间:2016-10-20 19:38   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 ORACLE 表监视功能(table monitoring) 通过打开表监视选项 (ALTER TABLE ...MONITORING) 然后检查这些表的视图 DBA_TAB_MODIFICATIONS,检查表中的数据是否已明显更改过。
测试环境:DB:10.2.0.4

1.statistics_level的值


oracleplus.net>show parameter statistics_level

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----Oracleoracleplus.net--------------------------
statistics_level                     string      TYPICAL

2.创建测试表


oracleplus.net>show user;
USER is "SCOTT"
oracleplus.net>create table modifications as select * from emp;

Table created.

oracleplus.net>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等功能都不能正常使用。

oracleplus.net>alter table modifications nomonitoring;

Table altered.

oracleplus.net>select table_name,monitoring from user_tables where table_name='MODIFICATIONS';

TABLE_NAME                     MON
------------------------------ ---
MODIFICATIONS                  YES
表监视到行记录变化后.我们可以通过dba_tab_modifications表查看.下面是此表的底层表的信息:

oracleplus.net>set long 555555
oracleplus.net>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.修改行记录


oracleplus.net>update modifications set ename='HTZ' where rownum=1;

1 row updated.

oracleplus.net>commit;

Commit complete.

oracleplus.net>show user
USER is "SYS"

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

PL/SQL procedure successfully completed.


这里我们发现已经有相关的记录信息.注意这里的条件包括了rollback部分的.不用很准确
oracleplus.net>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

oracleplus.net>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


oracleplus.net>oradebug setmypid
Statement processed.
oracleplus.net>oradebug event 10046 trace name context forever,level 8;
Statement processed.
oracleplus.net>oradebug tracefile_name
/u01/app/oracle/admin/orcl10g/udump/orcl10g_ora_8209.trc
oracleplus.net>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/2013/09/09/oracle-%e8%a1%a8%e7%9b%91%e8%a7%86%e5%8a%9f%e8%83%bdtable-monitoring.html | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle表监视功能table monitoring的使用案例

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1016.html

Oracle研究中心

关键词:

table monitoring的使用案例

如何使用Oracle表监视功能