索引干货-索引监控脚本

索引监控的查询脚本

drop table t purge;

create table t as select * from dba_objects;

create index idx_t_id on t(object_id);

create index idx_t_name t(object_name);

--未监控索引时，v$object_usage查询不到任何记录

select * from v$object_usage;

--接下来对idx_t_id和idx_t_name两列索引做监控

 

索引监控的实施

alter index idx_t_id monitoring usage;

alter index idx_t_name monitoring usage;

set linesize 166

col index_name for a10

col table_name for a10

col monitoring for a10

col used for a10

col start_monitoring for a25

col end_monitoring for a25

select * from v$object_usage;

 

索引监控的跟踪

--以下查询必然用到object_id列的索引

select object_id from t where object_id = 19;

--观察分析，果然发现idx_t_id列的索引的used果然更改为YES

select * from v$object_usage;

查看输出结果如下：

SQL> select * from v$object_usage;

INDEX_NAME TABLE_NAME MONITORING USED       START_MONITORING          END_MONITORING
---------- ---------- ---------- ---------- ------------------------- --------------------------------------------------------------------
IDX_T_ID   T             YES       YES                      04/24/2013 14:13:09
IDX_T_NAME T          YES        NO                       04/24/2013 14:13:09