ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 对索引开启monitoring方法

对索引开启monitoring方法

原创 Linux操作系统 作者:linfeng_oracle 时间:2013-10-16 14:59:49 0 删除 编辑

对索引开启monitoring方法

 

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

 

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

alter index monitoring usage;

 

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

alter index nomonitoring usage;

 

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

select * from v$object_usage;

 

ps可以通过sys用户对其他用户的索引开启或关闭monitoring,但是sys无法查询其他用户索引的monitoring结果,只能使用索引的owner来查询v$object_usage视图

 

 

案例:

下面通过sys用户对dwmodel.xj_table_col1_idx 索引开启monitoring

 

Connected as SYS

 

SQL> alter index dwmodel.xj_table_col1_idx monitoring usage;

 

Index altered

 

 

 

Connected as dwmodel

 

SQL> select * from v$object_usage;

 

INDEX_NAME          TABLE_NAME   MONITORING USED START_MONITORING    END_MONITORING

-----------------   ----------------------- ---- ------------------- --------------

XJ_TABLE_COL1_IDX   XJ_TABLE     YES        NO   10/16/2013 10:58:41

 

 

 

SQL> select col1 from dwmodel.xj_table where col1=100;

 

      COL1

----------

       100

 

SQL> select * from v$object_usage;

 

INDEX_NAME          TABLE_NAME    MONITORING USED START_MONITORING    END_MONITORING

------------------- ------------- ---------- ---- ------------------- -------------------

XJ_TABLE_COL1_IDX   XJ_TABLE      YES        YES  10/16/2013 10:58:41

 

 

 

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

 

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 level5)是不会记录数据的,只有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后,再综合应用,选择可疑索引进行监控,找出并删除无用索引,为数据库减肥。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24996904/viewspace-774480/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-09-14

  • 博文量
    76
  • 访问量
    418393