监控索引使用情况(去除无用索引) [转帖]_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 3741 | 回复: 0   主题: 监控索引使用情况(去除无用索引) [转帖]        下一篇 
Gavin
注册用户
等级:少校
经验:878
发帖:130
精华:1
注册:2011-7-21
状态:离线
发送短消息息给Gavin 加好友    发送短消息息给Gavin 发消息
发表于: IP:您无权察看 2011-8-8 9:34:20 | [全部帖] [楼主帖] 楼主

转自:http://www.jiagulun.com/thread-400-1-4.html

一个系统,经过长期的运行、维护和版本更新后,可能会产生大量的索引,甚至索引所占空间远远大于数据所占的空间。很多索引,在初期设计时,对于系统来说是有用的。但是,经过系统的升级、数据表结构的调整、应用的改变,很多索引逐渐不被使用,成为了垃圾索引。这些索引占据了大量数据空间,增加了系统的维护量,甚至会降低系统性能。因此,DBA应该根据系统的变化,找出垃圾索引,为系统减肥。
Oracle 9i后,可以通过设置对索引进行监控,来监视索引在系统中是否被使用到。语法如下:

alter index <INDEX_NAME> monitoring usage;


如果需要取消监控,可以使用以下语句:

alter index <INDEX_NAME> nomonitoring usage;


设置监控后,就可以查询视图v$object_usage来确认该索引是否被使用。

以下是一个DEMO演示:

SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
------------------------------ ------------------------------ ---------- ---- ------------------- -------------------
SQL> alter index QUEST_TEMPLATE_IDX monitoring usage;
Index altered
SQL> select count(*) from quest_template a
2 where minlevel >=38
3 and maxlevel <= 45;
COUNT(*)
----------
165
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
------------------------------ ------------------------------ ---------- ---- ------------------- -------------------
QUEST_TEMPLATE_IDX QUEST_TEMPLATE YES YES 05/22/2007 14:02:51


但是,这个方法可能存在一个问题:对于一个复杂系统来说,索引的数量可能是庞大的,那么我们如何来鉴定那些索引是值得怀疑的,应该被监控的呢?换句话说,我们如何减少监控范围呢?这里介绍几个方法。

1、利用library cache数据
在library cache中,存储了系统中游标的查询计划(并非全部,受library cache大小的限制),通过视图v$sql_plan,我们可以查询到这些数据。利用这些数据,我们可以排除那些出现在查询计划中的索引:

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 b.last_load_time > <START_AUDIT_DATE>;


2、利用statspack表

Statspack建立以后,为了记录快照的统计数据,会创建一系列的以stats$开头的表。其中stats$sql_plan表记录了每个快照中超过其阈值的语句的查询计划。因此我们可以将出现在该表中索引对象排除在监控范围之外:

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


但是,这张表在默认情况下(snapshot level=5)是不会记录数据的,只有snapshot>=6才会有记录。另外,该表在8i中是没有的。

3、利用AWR数据

10g以后,oracle出现了比statspack更加强大的性能分析工具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 a.startup_time > <START_AUDIT_DATE>;


利用上述方法,过滤掉大部分肯定被使用的index后,再综合应用,选择可疑索引进行监控,找出并删除无用索引,为数据库减肥。




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论