dbms_stats vs analyze(2)

May 28th, 2007 | Categories: Boring | Tags:
Leave a comment | Trackback

当某个索引处于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


需要注意。

Del.icio.us Google书签 Digg Live Bookmark Technorati Furl Yahoo书签 Facebook 百度搜藏 新浪 ViVi 365Key 网摘 天极网摘 和讯网摘 博拉网 POCO 网摘 饭否 QQ 书签 Digbuzz 我挖网 Mister Wong
  1. 棉花糖ONE
    February 19th, 2008 at 10:17
    Quote | #1

    测试了下,这问题在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

  2. yumianfeilong
    February 20th, 2008 at 15:43
    Quote | #2

    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