dbms_stats vs analyze(2)
当某个索引处于monitoring usage的时候，如果使用dbms_stats去分析表并且同时分析索引，会将该索引的v$object_usage.USED设置为TRUE，导致监控了N天的可疑索引前功近弃。这并不是我们所希望的.
如果使用analyze，索引的状态不会被设置为USE = TRUE
在10gR2中仍然有种情况
SQL> create table test as select *
from dba_objects;
Table created.
SQL> create index test_idx on test(object_id);
Index created.
SQL> alter index test_idx monitoring usage;
Index altered.
SQL>select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USE
------------------------------ ------------
TEST_IDX TEST YES NO
SQL>analyze table test compute statistics;
Table analyzed.
SQL>select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USE
------------------------------ ------------
TEST_IDX TEST YES NO
SQL> exec dbms_stats.gather_table_stats(user,'test',cascade=>true);
PL/SQL procedure successfully completed.
SQL>select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USE
------------------------------ ------------
TEST_IDX TEST YES YES
from dba_objects;
Table created.
SQL> create index test_idx on test(object_id);
Index created.
SQL> alter index test_idx monitoring usage;
Index altered.
SQL>select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USE
------------------------------ ------------
TEST_IDX TEST YES NO
SQL>analyze table test compute statistics;
Table analyzed.
SQL>select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USE
------------------------------ ------------
TEST_IDX TEST YES NO
SQL> exec dbms_stats.gather_table_stats(user,'test',cascade=>true);
PL/SQL procedure successfully completed.
SQL>select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USE
------------------------------ ------------
TEST_IDX TEST YES YES
需要注意。
测试了下,这问题在10.2.0.1下就不存在了
10g测试过程
SQL> alter index idx_k monitoring usage;
索引已更改。
SQL> select index_name,table_name,used from v$object_usage where table_name=’K';
INDEX_NAME TABLE_NAME USE
—————————— —————————— —
IDX_K K NO
SQL> exec dbms_stats.gather_table_stats(user,’K',cascade=>true);
PL/SQL 过程已成功完成。
SQL> select index_name,table_name,used from v$object_usage where table_name=’K';
INDEX_NAME TABLE_NAME USE
—————————— —————————— —
IDX_K K NO
SQL> select * from v$version;
BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
9i测试过程
SQL> create table k(id int);
表已创建。
SQL> create index idx_k on k(id);
索引已创建。
SQL> alter index idx_k monitoring usage;
索引已更改。
SQL> select index_name,table_name,used from v$object_usage where table_name=’K';
INDEX_NAME TABLE_NAME USE
—————————— —————————— —
IDX_K K NO
SQL> exec dbms_stats.gather_table_stats(user,’K',cascade=>true);
PL/SQL 过程已成功完成。
SQL> select index_name,table_name,used from v$object_usage where table_name=’K';
INDEX_NAME TABLE_NAME USE
—————————— —————————— —
IDX_K K YES
SQL> select * from v$version;
BANNER
—————————————————————-
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
10.2.0.3.0中又出现了。
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Feb 20 00:42:10 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
o
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> nn /
Connected.
SQL> create table test as select *
2 from dba_objects;
Table created.
SQL> create index test_idx on test(object_id);
Index created.
SQL> SQL> alter index test_idx monitoring usage;
Index altered.
SQL>
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USE
—————————— —————————— — —
START_MONITORING END_MONITORING
——————- ——————-
TEST_IDX TEST YES NO
02/20/2008 00:42:26
SQL> exec dbms_stats.gather_table_stats(user,’test’,cascade=>true);
PL/SQL procedure successfully completed.
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USE
—————————— —————————— — —
START_MONITORING END_MONITORING
——————- ——————-
TEST_IDX TEST YES YES
02/20/2008 00:42:26