表监控一些实践学习

表监控设置语句

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as yuzh

SQL> alter table test monitoring;

Table altered

 

但是查询表的监控状况时的时候

SQL> select * from sys.dba_tab_modifications where table_owner='YUZH';

0 rows selected

 

我以为是要动作一下才行

SQL> insert into test values ('即',20);

1 row inserted

SQL> commit;

Commit complete

 

结果还是没有

SQL> select * from sys.dba_tab_modifications where table_owner='YUZH';

0 rows selected

 

查看是否设置成功

SQL> select monitoring from user_tables;

MONITORING
----------
YES
YES
YES
YES
YES
YES
YES
YES
YES
YES
YES
YES
YES
YES
YES
YES

16 rows selected

结果全是yes，看来默认就是yes

查询为何前面没有显示

 

需要刷新一下，执行个存储过程才行

SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed

 

再查

SQL> select * from sys.dba_tab_modifications where table_owner='YUZH';

TABLE_OWNER   TABLE_NAME                 INSERTS  UPDATES DELETES TIMESTAMP   TRUNCATED DROP_SEGMENTS
------------- ------------------------- -------- -------- ------- ----------- --------- -------------
YUZH          TEST1                                     2484823        0   39962 2010-2-1 12 YES                   0
YUZH          SOM_DEPARTURE_MASTER         31188        0       0     2010-2-1 12 NO                    0
YUZH          SOM_DEPARTURE_DETAIL          33584        0       0     2010-2-1 12 NO                    0
YUZH          VMI_SPECIALPRICEAUTH_SLIP   70571        0       0     2010-2-1 12 NO                    0
YUZH          SOM_GOODS_RETURN_MASTER  25225        0       0     2010-2-1 12 NO                    0
YUZH          SOM_GOODS_RETURN_DETAIL   35678        0       0     2010-2-1 12 NO                    0
YUZH          VMI_SPECIALPRICEAUTH_MST       623        0       0      2010-2-1 12 NO                    0
YUZH          VMI_SPECIALPRICEAUTH_DTL      4547        0       0      2010-2-1 12 NO                    0
YUZH          MST_PARTS_DTL                          17255        0       0     2010-2-1 12 NO                    0
YUZH          VMI_SPECIALPRICEAUTH_CUST   10349        0       0     2010-2-1 12 NO                    0
YUZH          CUT_CUST_PREMIUM                    8450        0       0      2010-2-1 12 NO                    0
YUZH          IVC_STOCK_COST                       15583        0       0     2010-2-1 12 NO                    0
YUZH          MST_COMPANY_DTL                            1        0       0     2010-2-1 12 NO                    0
YUZH          RPMMT                                           5003        0       0     2010-2-1 12 NO                    0
YUZH          TEST                                                    6        0       0     2010-2-1 12 NO                    0

15 rows selected

全列出来了

 

设置为不监控

SQL> alter table test nomonitoring;

Table altered

SQL> insert into test values ('即',20);

1 row inserted

SQL> commit;

Commit complete

SQL> select monitoring from user_tables where table_name='TEST';

MONITORING
----------
YES

SQL> select INSERTS,UPDATES,DELETES,TIMESTAMP,TRUNCATED,DROP_SEGMENTS from sys.dba_tab_modifications where table_name='TEST';

   INSERTS    UPDATES    DELETES TIMESTAMP   TRUNCATED DROP_SEGMENTS
---------- ---------- ---------- ----------- --------- -------------
         6          0          0 2010-2-1 12 NO                    0
1 rows selected
        
SQL> select * from test;

姓名           成绩
-------- ----------
即               20
即               20
即               20
你               79
他               79
它               20
我               80

7 rows selected

新插入一条记录，但是inserts还是6，看上去是被禁用了但是好像不管用，状态还是yes，奇怪的很

但是做以下操作

SQL> delete from test;

7 rows deleted

SQL> commit;

Commit complete

SQL> select * from test;

姓名           成绩
-------- ----------

SQL> select INSERTS,UPDATES,DELETES,TIMESTAMP,TRUNCATED,DROP_SEGMENTS from sys.dba_tab_modifications where table_name='TEST';

   INSERTS    UPDATES    DELETES TIMESTAMP   TRUNCATED DROP_SEGMENTS
---------- ---------- ---------- ----------- --------- -------------
         6          0          0 2010-2-1 12 NO                    0

SQL> alter table test monitoring;

Table altered

SQL> truncate table test;

Table truncated

SQL> select INSERTS,UPDATES,DELETES,TIMESTAMP,TRUNCATED,DROP_SEGMENTS from sys.dba_tab_modifications where table_name='TEST';

   INSERTS    UPDATES    DELETES TIMESTAMP   TRUNCATED DROP_SEGMENTS
---------- ---------- ---------- ----------- --------- -------------
         6          0          0 2010-2-1 12 NO                    0

SQL> insert into test values('dd',12);

1 row inserted

SQL> commit;

Commit complete

SQL> select INSERTS,UPDATES,DELETES,TIMESTAMP,TRUNCATED,DROP_SEGMENTS from sys.dba_tab_modifications where table_name='TEST';

   INSERTS    UPDATES    DELETES TIMESTAMP   TRUNCATED DROP_SEGMENTS
---------- ---------- ---------- ----------- --------- -------------
         6          0          0 2010-2-1 12 NO                    0

 

看上去好像再设置为监控也不顶用

 

SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed

SQL> select INSERTS,UPDATES,DELETES,TIMESTAMP,TRUNCATED,DROP_SEGMENTS from sys.dba_tab_modifications where table_name='TEST';

   INSERTS    UPDATES    DELETES TIMESTAMP   TRUNCATED DROP_SEGMENTS
---------- ---------- ---------- ----------- --------- -------------
         8          0          7 2010-2-1 12 YES                   0

刷一下又都有了

接着我再设置为无效

SQL> alter table test nomonitoring;

Table altered

SQL> insert into test values('dd',12);

1 row inserted

SQL> commit;

Commit complete

SQL> select INSERTS,UPDATES,DELETES,TIMESTAMP,TRUNCATED,DROP_SEGMENTS from sys.dba_tab_modifications where table_name='TEST';

   INSERTS    UPDATES    DELETES TIMESTAMP   TRUNCATED DROP_SEGMENTS
---------- ---------- ---------- ----------- --------- -------------
         8          0          7 2010-2-1 12 YES                   0

SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed

SQL> select INSERTS,UPDATES,DELETES,TIMESTAMP,TRUNCATED,DROP_SEGMENTS from sys.dba_tab_modifications where table_name='TEST';

   INSERTS    UPDATES    DELETES TIMESTAMP   TRUNCATED DROP_SEGMENTS
---------- ---------- ---------- ----------- --------- -------------
         9          0          7 2010-2-1 12 YES                   0

刷一下又都有了，看来alter table test nomonitoring;根本不是开关。。。。

 

接着是重置统计信息

SQL> exec dbms_stats.gather_table_stats('YUZH','TEST');

PL/SQL procedure successfully completed

 

重置后就没有记录

SQL> select * from sys.dba_tab_modifications where table_name='TEST';

0 rows selected

SQL> select monitoring from user_tables where table_name='TEST';

MONITORING
----------
YES

1 rows selected

 

刷新也没有

SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed

SQL> select * from sys.dba_tab_modifications where table_name='TEST';

0 rows selected

 

设置也没有

SQL> alter table test monitoring;

Table altered

SQL> select * from sys.dba_tab_modifications where table_name='TEST';

0 rows selected

SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed

SQL> select * from sys.dba_tab_modifications where table_name='TEST';

0 rows selected

 

尝试再插入数据

SQL> insert into test values('ff',22);

1 row inserted

SQL> commit;

Commit complete

 

还是没有

SQL> select * from sys.dba_tab_modifications where table_name='TEST';

0 rows selected

 

刷新后就有了

SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed

SQL> select * from sys.dba_tab_modifications where table_name='TEST';

TABLE_OWNER                    TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME                 INSERTS    UPDATES    DELETES TIMESTAMP   TRUNCATED DROP_SEGMENTS
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ----------- --------- -------------
YUZH                           TEST                                                                                                  1          0          0 2010-2-1 15 NO                    0

15 rows selected