oracle 对索引进行监控与分析

nfmzbpbfil

　　

查看表索引信息需要用到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 tName           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 createdSQL> alter index idx_t_created monitoring usage;Index alteredSQL> alter index idx_t_created nomonitoring usage; Index alteredSQL> 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 alteredSQL> select * from t where t.created=sysdate;SQL> alter index idx_t_created nomonitoring usage;Index alteredSQL> 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

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 willfail.

　　

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