请教大神们有没哪些方法可以监控生产库索引使用情况？

lxhbww

请教大神们有没哪些方法可以监控生产库哪些索引没用哪些有用？

saup007

1. 仅供参考，我也没测试
   
2. 
   
3. 如何监控oracle的索引是否使用 | 半瓶来源网址： http://www.banping.com/2010/03/24/monitoring_index/
   
4. 如何监控oracle的索引是否使用
   
5. by BANPING on 2010/03/24 · 4 COMMENTS · in ORACLE
   
6. 很多软件开发过程中，没有注意合理规划索引，造成一个表上有N多个索引，为后续的维护和优化带来麻烦。因此有时候需要监控已有的索引是否在使用，oracle提供了监控索引是否使用的工具，很简单，简要介绍一下。
   
7. 首先，我们如果是监控一个表上的所有索引，可以这样先生成监控的命令：
   
8. SQL> select 'alter index '||index_name||' monitoring usage;' from user_indexes where table_name=upper('mpaymentappl')
   
9. 2  /
   
10. 'ALTERINDEX'||INDEX_NAME||'MONITORINGUSAGE;'
    
11. ------------------------------------------------------------
    
12. alter index IDX_MPAYMENTAPPL_BCODE monitoring usage;
    
13. alter index MPAYMENTAPPL_FLAGS monitoring usage;
    
14. alter index MPAYMENTAPPL_PAICODE monitoring usage;
    
15. 然后执行这些脚本就开始监控了，监控信息可通过V$OBJECT_USAGE查看，通过used列可知道这个索引是否被使用：
    
16. SQL> select * from V$OBJECT_USAGE
    
17. 2  /
    
18. INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
    
19. ------------------------------ ------------------------------ --- --- ------------------- -------------------
    
20. IDX_MPAYMENTAPPL_BCODE         MPAYMENTAPPL                   YES NO  03/24/2010 10:55:27
    
21. MPAYMENTAPPL_FLAGS             MPAYMENTAPPL                   YES NO  03/24/2010 10:55:27
    
22. MPAYMENTAPPL_PAICODE           MPAYMENTAPPL                   YES NO  03/24/2010 10:55:28
    
23. 取消监控某个索引：
    
24. SQL> ALTER INDEX MPAYMENTAPPL_FLAGS NOMONITORING USAGE;
    
25. Index altered.
    
26. SQL> select * from V$OBJECT_USAGE
    
27. 2  /
    
28. INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
    
29. ------------------------------ ------------------------------ --- --- ------------------- -------------------
    
30. IDX_MPAYMENTAPPL_BCODE         MPAYMENTAPPL                   YES NO  03/24/2010 10:55:27
    
31. MPAYMENTAPPL_FLAGS             MPAYMENTAPPL                   NO  NO  03/24/2010 10:55:27 03/24/2010 10:57:19
    
32. MPAYMENTAPPL_PAICODE           MPAYMENTAPPL                   YES NO  03/24/2010 10:55:28
    
33. 那些持续关注一定时间没有使用的索引就可以删除了，以提高DML操作效率。
    
34. 
    
35. 下面是该视图列的描述：
    
36. INDEX_NAME: sys.obj$.name 中的索引名字
    
37. TABLE_NAME: sys.obj$obj$name 中的表名
    
38. MONITORING: YES (索引正在被监控), NO (索引没有被监控)
    
39. USED: YES (索引已经被使用过), NO (索引没有被使用过)
    
40. START_MONITORING: 开始监控的时间
    
41. END_MONITORING: 结束监控的时间
    
42. 所有被使用过至少一次的索引都可以被监控并显示到这个视图中
    
43. 
    
44. 
    
45. 如何监控索引的使用情况 - edwardking888的个人空间 - ITPUB个人空间 - powered by X-Space来源网址： http://space.itpub.net/8183550/viewspace-662770
    
46. 如何监控索引的使用情况上一篇 / 下一篇  2010-05-15 14:53:40 / 个人分类：oracle学习
    
47. 查看( 88 ) / 评论( 0 ) / 评分( 0 / 0 )
    
48. 一个系统，经过长期的运行、维护和版本更新后，可能会产生大量的索引，甚至索引所占空间远远大于数据所占的空间。很多索引，在初期设计时，对于系统来说是有用的。但是，经过系统的升级、数据表结构的调整、应用的改变，很多索引逐渐不被使用，成为了垃圾索引。这些索引占据了大量数据空间，增加了系统的维护量，甚至会降低系统性能。因此，DBA应该根据系统的变化，找出垃圾索引，为系统减肥。
    
49. Oracle 9i后，可以通过设置对索引进行监控，来监视索引在系统中是否被使用到。语法如下：
    
50. 
    
51. alter index  monitoring usage;
    
52. 
    
53. 如果需要取消监控，可以使用以下语句：
    
54. 
    
55. alter index  nomonitoring usage;
    
56. 
    
57. 设置监控后，就可以查询视图v$object_usage来确认该索引是否被使用。
    
58. 
    
59. 以下是一个DEMO演示：
    
60. 
    
61. SQL> select * from v$object_usage;
    
62. 
    
63. INDEX_NAME                     TABLE_NAME                     MONITORING USED START_MONITORING    END_MONITORING
    
64. ------------------------------ ------------------------------ ---------- ---- ------------------- -------------------
    
65. 
    
66. SQL> alter index QUEST_TEMPLATE_IDX monitoring usage;
    
67. 
    
68. Index altered
    
69. 
    
70. SQL> select count(*) from quest_template a
    
71.   2  where minlevel >=38
    
72.   3  and maxlevel <= 45;
    
73. 
    
74.   COUNT(*)
    
75. ----------
    
76.        165
    
77. 
    
78. SQL> select * from v$object_usage;
    
79. 
    
80. INDEX_NAME                     TABLE_NAME                     MONITORING USED START_MONITORING    END_MONITORING
    
81. ------------------------------ ------------------------------ ---------- ---- ------------------- -------------------
    
82. QUEST_TEMPLATE_IDX             QUEST_TEMPLATE                 YES        YES  05/22/2007 14:02:51
    
83. 
    
84. 但是，这个方法可能存在一个问题：对于一个复杂系统来说，索引的数量可能是庞大的，那么我们如何来鉴定那些索引是值得怀疑的，应该被监控的呢？换句话说，我们如何减少监控范围呢？这里介绍几个方法。
    
85. 
    
86. 1、利用library cache数据
    
87. 在library cache中，存储了系统中游标的查询计划(并非全部，受library cache大小的限制)，通过视图v$sql_plan，我们可以查询到这些数据。利用这些数据，我们可以排除那些出现在查询计划中的索引：
    
88. 
    
89. 
    
90. select a.object_owner, a.object_name
    
91. from v$sql_plan a, v$sqlarea b
    
92. where a.sql_id = b.sql_id
    
93. and a.object_type='INDEX'
    
94. and b.last_load_time > ;
    
95. 
    
96. 2、利用statspack表
    
97. 
    
98. Statspack建立以后，为了记录快照的统计数据，会创建一系列的以stats$开头的表。其中stats$sql_plan表记录了每个快照中超过其阈值的语句的查询计划。因此我们可以将出现在该表中索引对象排除在监控范围之外：
    
99. 
    
100. select a.object_owner, a.object_name
     
101. from stats$sql_plan a, stats$sql_plan_usage b
     
102. where a.plan_hash_value = b.plan_hash_value
     
103. and a.object_type='INDEX'
     
104. and b.last_active_time > ;
     
105. 
     
106. 但是，这张表在默认情况下（snapshot level＝5）是不会记录数据的，只有snapshot>=6才会有记录。另外，该表在8i中是没有的。
     
107. 
     
108. 3、利用AWR数据
     
109. 
     
110. 10g以后，oracle出现了比statspack更加强大的性能分析工具AWR，它也同样记录了系统中的统计数据以供分析。我们也同样可以从其中分析出那些索引是被使用到的。
     
111. 
     
112. select b.object_owner, b.object_name
     
113. from dba_hist_snapshot a, dba_hist_sql_plan b, dba_hist_sqlstat c
     
114. where a.snap_id = c.snap_id
     
115. and b.sql_id=c.sql_id
     
116. and b.object_type = 'INDEX'
     
117. and a.startup_time > ;
     
118. 
     
119. 
     
120. 利用上述方法，过滤掉大部分肯定被使用的index后，再综合应用，选择可疑索引进行监控，找出并删除无用索引，为数据库减肥。

复制代码

lxhbww

谢谢。。。。。。。。