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