对索引的监控

1、对索引的监控

方法一:
SQL> alter index i  monitoring usage;
索引已更改。

SQL> SELECT INDEX_NAME, MONITORING, USED, START_MONITORING, END_MONITORING from v$object_usage;

INDEX_NAME                     MON USE START_MONITORING    END_MONITORING
------------------------------ --- --- ------------------- -------------------
I                              YES NO  05/20/2013 16:11:43

SQL> select *From t1 where empno=7788;

未选定行

SQL> SELECT INDEX_NAME, MONITORING, USED, START_MONITORING, END_MONITORING from v$object_usage;

INDEX_NAME                     MON USE START_MONITORING    END_MONITORING
------------------------------ --- --- ------------------- -------------------
I                              YES YES 05/20/2013 16:11:43

结论：但是这中方法只能监控monitoring 自己用户下的索引，不能监控其他用户下的

 

 

2、

方法二：从内存中监控某个表的索引是否使用
select a.object_owner, a.object_name
  from v$sql_plan a, v$sqlarea b
 where a.sql_id = b.sql_id
   and a.object_type = 'INDEX'
   and object_name IN
       (select INDEX_NAME From dba_indexES where table_name = 'PD_WO')
 group by object_name, object_owner;

 

 

3、

方法三：从awr报告里监控某个索引是否被使用

select b.object_owner, b.object_name
  from dba_hist_snapshot a, dba_hist_sql_plan b, dba_hist_sqlstat c
 where a.snap_id = c.snap_id   
   and b.sql_id = c.sql_id    
   and b.object_type = 'INDEX'
   and object_owner = 'SCOTT'
   and object_name in (select INDEX_NAME From  dba_indexES where table_name='PD_WO') group by object_name, object_owner;

 

 

4

方法四： Statspack

Statspack建立以后，为了记录快照的统计数据，会创建一系列的以stats$开头的表。其中stats$sql_plan表记录了每个快照中超过其阈值的语句的查询计划。因此我们可以将出现在该表中索引对象排除在监控范围之外：
但是，这张表在默认情况下（snapshot level＝5）是不会记录数据的，只有snapshot>=6才会有记录
select a.object_owner, a.object_name
from stats$sql_plan a, stats$sql_plan_usage b
where a.plan_hash_value = b.plan_hash_value
and a.object_type='INDEX'
and b.last_active_time > <START_AUDIT_DATE>;

 

 

 

参考：http://blog.chinaunix.net/uid-7900301-id-2548186.html