搜集统计信息导致索引被监控

收集统计信息导致索引被监控

      对于索引的调整，我们可以通过Oracle提供的索引监控特性来跟踪索引是否被使用。尽管该特性并未提供索引使用的频度，但仍不失为我们参考的方式之一。然而，最近在Oracle 10.2.0.3中发现收集统计信息时导致索引也被监控，而不是用于sql查询引发的索引监控。如此这般，索引监控岂不是鸡肋？

1、基于Oracle 10g 收集统计信息索引被监控情形

scott@CNMMBO> select * from v$version where rownum<2;BANNER----------------------------Oracle Database 10g Release 10.2.0.3.0 - 64bit Production--创建临时表tscott@CNMMBO> create table t(id number constraint t_pk primary key);Table created.--启用索引监控scott@CNMMBO> alter index t_pk monitoring usage;Index altered.--查看对象的使用情况scott@CNMMBO> select * from v$object_usage where index_name='T_PK';INDEX_NAME                     Table Name        MON USE START_MONITORING    END_MONITORING------------------------------ ----------------- --- --- ------------------- -------------------T_PK                           T                 YES NO  03/22/2013 20:53:23--收集表t上的统计信息scott@CNMMBO> exec dbms_stats.gather_table_stats('SCOTT','T',cascade=>true);PL/SQL procedure successfully completed.--下面的查询中提示索引没有被使用--这应该是由于表上没有数据的缘故，也就不存在对应的索引段scott@CNMMBO> select * from v$object_usage where index_name='T_PK';INDEX_NAME                     Table Name        MON USE START_MONITORING    END_MONITORING------------------------------ ----------------- --- --- ------------------- -------------------T_PK                           T                 YES NO  03/22/2013 20:53:23--下面尝试插入两条数据scott@CNMMBO> insert into t select 1 from dual;1 row created.scott@CNMMBO> insert into t select 2 from dual;1 row created.--再次收集统计信息scott@CNMMBO> exec dbms_stats.gather_table_stats('SCOTT','T',cascade=>true);PL/SQL procedure successfully completed.--Author : Robinson--Blog   : http://blog.csdn.net/robinson_0612--这下子，索引变成了已经被使用scott@CNMMBO> select * from v$object_usage where index_name='T_PK';INDEX_NAME                     Table Name         MON USE START_MONITORING    END_MONITORING------------------------------ ------------------ --- --- ------------------- -------------------T_PK                           T                  YES YES 03/22/2013 20:53:23

2、基于Oracle 11g收集统计信息索引被监控情形

SQL> select * from v$version where rownum<2;BANNER--------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production--下面的操作与上面雷同SQL> create table t(id number constraint t_pk primary key);Table created.SQL> alter index t_pk monitoring usage;Index altered.SQL> set linesize 190SQL> select * from v$object_usage where index_name='T_PK';INDEX_NAME                     TABLE_NAME        MON USE START_MONITORING    END_MONITORING------------------------------ ----------------- --- --- ------------------- -------------------T_PK                           T                 YES NO  03/22/2013 20:37:57SQL> exec dbms_stats.gather_table_stats('SCOTT','T',cascade=>true);PL/SQL procedure successfully completed.SQL> select * from v$object_usage where index_name='T_PK';INDEX_NAME                     TABLE_NAME       MON USE START_MONITORING    END_MONITORING------------------------------ ---------------- --- --- ------------------- -------------------T_PK                           T                YES NO  03/22/2013 20:37:57SQL> insert into t select 111 from dual;1 row created.SQL> insert into t select 222 from dual;1 row created.SQL> commit;Commit complete.SQL> exec dbms_stats.gather_table_stats('SCOTT','T',cascade=>true);PL/SQL procedure successfully completed.--可以看出，插入数据后，收集统计信息并不会导致索引被使用SQL> select * from v$object_usage where index_name='T_PK';INDEX_NAME                     TABLE_NAME       MON USE START_MONITORING    END_MONITORING------------------------------ ---------------- --- --- ------------------- -------------------T_PK                           T                YES NO  03/22/2013 20:37:57SQL> select index_name,NUM_ROWS,SAMPLE_SIZE,LAST_ANALYZED from dba_indexes where index_name='T_PK';INDEX_NAME                       NUM_ROWS SAMPLE_SIZE LAST_ANALYZED------------------------------ ---------- ----------- -------------------T_PK                                    2           2 2013/03/22 20:39:10--发布sql查询语句SQL> select * from t where id=222;        ID----------       222--再次查看，索引监控提示该索引已经被使用SQL>  select * from v$object_usage where index_name='T_PK';INDEX_NAME                     TABLE_NAME       MON USE START_MONITORING    END_MONITORING------------------------------ ---------------- --- --- ------------------- -------------------T_PK                           T                YES YES 03/22/2013 20:37:57SQL> alter index t_pk nomonitoring usage;

3、小结
  a、对于索引监控，在Oracle 10g中当收集统计信息时，如果当前索引的统计信息也被收集则导致该索引被监控
  b、注意索引能否被收集到还依赖于estimate_percent以及method_opt等收集时的相关参数
  c、由于上述情形存在因此索引监控在10g中功能有限，不过对于索引的使用情况也可以通过查询DBA_HIST_SQL_PLAN来获得
  d、在Oracle 11g中，不会出现上述情况

PL/SQL --> 游标

PL/SQL --> 隐式游标(SQL%FOUND)

批量SQL之 FORALL 语句

批量SQL之 BULK COLLECT 子句

PL/SQL 集合的初始化与赋值

PL/SQL 联合数组与嵌套表
PL/SQL 变长数组
PL/SQL --> PL/SQL记录

SQL tuning 步骤

高效SQL语句必杀技

父游标、子游标及共享游标

绑定变量及其优缺点

dbms_xplan之display_cursor函数的使用

dbms_xplan之display函数的使用

执行计划中各字段各模块描述

使用 EXPLAIN PLAN 获取SQL语句执行计划

Oracle ROWID

NULL 值与索引(一)

NULL 值与索引(二)

启用 AUTOTRACE 功能

函数使得索引列失效

Oracle 绑定变量窥探

Oracle 自适应共享游标