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

黄玮(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 条评论
登录 后参与评论

相关文章

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

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

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

2264
来自专栏数据和云

量化的Oracle世界

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

2646
来自专栏带你撸出一手好代码

sql连接查询中on筛选与where筛选的区别

sql查询这个东西, 要说它简单, 可以很简单, 通常情况下只需使用增删查改配合编程语言的逻辑表达能力,就能实现所有功能。 但是增删查改并不能代表sql语句的所...

3288
来自专栏企鹅号快讯

Django数据从sqlite迁移数据到MySQL

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

3996
来自专栏程序猿

小米开源soar一款对SQL进行优化和改写的自动化工具

SOAR(SQL Optimizer And Rewriter)是一个对SQL进行优化和改写的自动化工具。 由小米人工智能与云平台的数据库团队开...

591
来自专栏人工智能LeadAI

ElasticSearch优化系列三:索引过程

大家可能会遇到索引数据比较慢的过程。其实明白索引的原理就可以有针对性的进行优化。ES索引的过程到相对Lucene的索引过程多了分布式数据的扩展,而这ES主要是用...

3719
来自专栏MYSQL轻松学

MYSQL常用的性能指标

(1) QPS(每秒Query量) QPS = Questions(or Queries) / seconds mysql > show global sta...

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

Django数据从sqlite迁移数据到MySQL

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

3403
来自专栏小俊博客

纯代码实现WordPress邀请码功能

2325
来自专栏沃趣科技

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

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

34110

扫码关注云+社区