oracle 对索引进行监控与分析

查看表索引信息 需要用到dba_indexes, dba_ind_columns，查看表索引列信息，包括复合索引的脚本：

    WITH nonformat AS   
    (SELECT i.index_name,i.table_name,t.column_name,t.column_position,i.uniqueness,t.table_owner  
    FROM dba_indexes i, dba_ind_columns t  
    WHERE i.table_name = t.table_name  
     AND i.table_owner = t.table_owner  
     AND i.index_name = t.index_name  
     AND i.table_name = upper('t')  
     --AND i.table_owner = upper('scott')  
     ORDER BY t.index_name,t.column_position   
     )   
     SELECT nt.index_name,  
            nt.table_name,  
            (SELECT wmsys.wm_concat(column_name)  
               FROM nonformat  
              WHERE nt.index_name = index_name) column_names,  
            nt.uniqueness,  
            nt.table_owner  
       FROM nonformat nt  
      GROUP BY nt.index_name, nt.table_name, nt.uniqueness, nt.table_owner  
      ORDER BY nt.index_name  

索引列column_names以,分隔，需要指定表名与表的所有者信息。

翻了几页书，做一下笔记哦！
发现多余的索引有两种方式
1.根据原理来判断
考虑复合索引，根据复合索引的前缀性与选择性，分析表字段的记录分布情况，对复合索引进行整合。
2.使用oracle的监控特性
alter index <index_name> monitoring usage; --对index_name开启监控
alter index <index_name> nomonitoring usage; --对index_name取消监控
select * from v$object_usage; --查询索引是否被使用

SQL> desc t
Name           Type          Nullable Default Comments 
-------------- ------------- -------- ------- -------- 
OWNER          VARCHAR2(30)  Y                         
OBJECT_NAME    VARCHAR2(128) Y                         
SUBOBJECT_NAME VARCHAR2(30)  Y                         
OBJECT_ID      NUMBER        Y                         
DATA_OBJECT_ID NUMBER        Y                         
OBJECT_TYPE    VARCHAR2(19)  Y                         
CREATED        DATE          Y                         
LAST_DDL_TIME  DATE          Y                         
TIMESTAMP      VARCHAR2(19)  Y                         
STATUS         VARCHAR2(7)   Y                         
TEMPORARY      VARCHAR2(1)   Y                         
GENERATED      VARCHAR2(1)   Y                         
SECONDARY      VARCHAR2(1)   Y                         
SQL> create index idx_t_created on t(created);
Index created
SQL> alter index idx_t_created monitoring usage;
Index altered
SQL> alter index idx_t_created nomonitoring usage; 
Index altered
SQL> select * from v$object_usage;
INDEX_NAME                     TABLE_NAME                     MONITORING USED START_MONITORING    END_MONITORING
------------------------------ ------------------------------ ---------- ---- ------------------- -------------------
IDX_T_CREATED                  T                              NO         NO   06/27/2011 21:31:56 06/27/2011 21:32:44

由USED=NO可知，idx_t_created索引没有被使用。

SQL> alter index idx_t_created monitoring usage;
Index altered
SQL> select * from t where t.created=sysdate;
SQL> alter index idx_t_created nomonitoring usage;
Index altered
SQL> select * from v$object_usage;
INDEX_NAME                     TABLE_NAME                     MONITORING USED START_MONITORING    END_MONITORING
------------------------------ ------------------------------ ---------- ---- ------------------- -------------------
IDX_T_CREATED                  T                              NO         YES  06/27/2011 21:33:38 06/27/2011 21:34:20

以上可以看出idx_t_created索引已经被使用了。
索引碎片分析与整理
建议每周监测一次索引的碎片情况，根据情况制定索引的重建频率以提高索引使用效率。
SQL> analyze index <index_name> structure;
SQL> select name,del_lf_rows_len,lf_rows_len,(del_lf_rows_len/lf_rows_len)*100 from index_stats;
索引碎片率(%) = (被删除的索引长度/索引总长)*100
SQL> alter index <索引名> rebuild;
SQL> alter index <索引名> coalesce;

1.如果索引的叶子行的碎片超过20%，考虑对索引进行重建。
alter index 用户名.索引名 rebuild tablespace 表空间名 storage(initial 初始值 next 扩展值) nologging
重建索引时要注意以下几点：
a.如果不指定tablespace名，索引将建在用户的默认表空间。
b.如果不指定nologging，将会写日志，导致速度变慢。由于索引的重建没有恢复的必要，所以，可以不写日志。
c.如果出现资源忙，表明有进程正在使用该索引，等待一会再提交。

2.如果出于空间或其他考虑，不能重建索引，可以整理索引。
alter index用户名.索引名 coalesce

我查询index_stats总是没有记录，不知为何？csdn博客为什么修改之后不能显示修改之后的结果呢？

http://www.itpub.net/viewthread.php?tid=1368530