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> validate 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

Use the rebuild_clause to re-create an existing index or one of its partitions or subpartitions.If index is marked UNUSABLE, a successful rebuild will mark it USABLE. For a function-based index, this clause also enables the index. If the function on which the index is based does not exist, the rebuild statement will fail.

SpecifyCOALESCE to instruct Oracle Database to merge the contents of index blocks where possible to free blocks for reuse.

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