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

【学习笔记】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的使用案例