性能优化:监控索引的使用情况

黄玮(Fuyuncat),资深 Oracle DBA,从事 Oracle 数据库管理、维护与开发工作十余年,有丰富的大型数据库设计、开发与维护方面的经验,博客www.HelloDBA.com

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

Oracle 9i后,可以通过设置对索引进行监控,来监视索引在系统中是否被使用到。语法如下:

alter index <INDEX_NAME> monitoring usage;

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

alter index <INDEX_NAME> nomonitoring usage;

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

以下是一个DEMO演示:

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

1、利用library cache数据

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

2、利用statspack表

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

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

3、利用AWR数据

10g以后,oracle出现了比statspack更加强大的性能分析工具AWR,它也同样记录了系统中的统计数据以供分析。我们也同样可以从其中分析出那些索引是被使用到的。

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

----the end

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2016-09-19

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏企鹅号快讯

Django数据从sqlite迁移数据到MySQL

昨天快速搭建了一套自己的知识库 感觉一下子有了很多的事情要做,至少得让自己用得舒服些。 没想到有了这个小工具之后,我发现我之前过得真是刀耕火种的信息收集。为什么...

4096
来自专栏杨建荣的学习笔记

通过top命令抓取cpu高消耗的sql (44天)

top命令在linux环境维护中很实用,虽然功能缺失不够sar那么全面。今天和大家分享一个通过top命令来抓取性能sql的案例。 通过top命令抓取了如下的信息...

3786
来自专栏杨建荣的学习笔记

Django数据从sqlite迁移数据到MySQL

昨天快速搭建了一套自己的知识库:使用Django基础模板搭建自己的知识库 感觉一下子有了很多的事情要做,至少得让自己用得舒服些。 没想到有了这个小工具之后,我发...

3583
来自专栏沃趣科技

Oracle数据库12cR2版本的SQL计划管理

文章翻译自ORACLE WHITE PAPER SQL Plan Management with Oracle Database 12c Release 2 概...

34710
来自专栏杨建荣的学习笔记

物化视图自动刷新的碰壁(r7笔记第61天)

今天和开发的同事讨论一个问题,他们说source 1的环境中存在一个表,现在希望目标环境target 1和target 2中都需要用到这部分的数据。 ? 对...

3554
来自专栏java架构技术

从大神的角度深入理解MySQL,值得收藏~

在此我向大家推荐一个架构学习交流群。程序员面试社区:236283328 里面会分享一些资深架构师录制的视频录像:有Spring,MyBatis,Netty源码分...

1091
来自专栏数据和云

SQL之美 - Oracle 子查询优化系列精讲

题记:SQL优化及SQL审核,是从源头解决性能问题的根本手段,无论是开发人员还是DBA,都应当持续深入的学习SQL开发技能,从而为解决性能问题打下根基。 本系列...

3203
来自专栏数据和云

量化的Oracle世界

Oracle数据库最为复杂的部分是优化器算法,在进行SQL解析的过程中,Oracle将一切都通过数字进行量化,然后评估比对,最后进行选择。在版本更新中,往往这部...

2696
来自专栏杨建荣的学习笔记

一个复杂数据需求的MySQL方案

前些天处理了一个需求,当时的数据库环境是Oracle,我算是想尽了Oracle相关的方案,而且在问题的处理过程中,还在不断的琢磨,如果失败了还有什么其他的...

3128
来自专栏杨建荣的学习笔记

shell脚本自动化采集性能sql(r2笔记39天)

通过v$sql_monitor能够实时采集可能存在的sql性能问题,但是每次问题发生的时候采取采取措施就有点“晚”了,我们需要防患于未然,把一些潜在问题提前发现...

2444

扫码关注云+社区