专栏首页小麦苗的DB宝专栏【DB笔试面试562】在Oracle中,如何监控索引的使用状况?

【DB笔试面试562】在Oracle中,如何监控索引的使用状况?

题目部分

在Oracle中,如何监控索引的使用状况?

答案部分

在开发应用程序时,可能会建立很多索引,那么这些索引的使用到底怎么样,是否有些索引一直都没有用到过,在这种情况下就需要对这些索引进行监控,以便确定它们的使用情况,并为是否可以清除它们给出依据。

监控索引有两种方式:

1、直接监控索引的使用情况

(1)设置所要监控的索引:ALTER INDEX IDX_T_XX MONITORING USAGE;

(2)查看该索引有没有被使用:SELECT * FROM V$OBJECT_USAGE;

(3)关闭监控:ALTER INDEX IDX_T_XX NOMONITORING USAGE;

查询V$OBJECT_USAGE就可以知道数据库对索引的使用情况了。通过一段时间的监控,就可以确定哪些是无用的索引。另外,为了避免使用V$OBJECT_USAGE只能查询到当前用户下索引的监控情况,可以使用如下语句查询数据库中所有被监控索引的使用情况:

 SELECT U.NAME OWNER,
        IO.NAME INDEX_NAME,
        T.NAME TABLE_NAME,
        DECODE(BITAND(I.FLAGS, 65536), 0, 'NO', 'YES') MONITORING,
        DECODE(BITAND(OU.FLAGS, 1), 0, 'NO', 'YES') USED,
        OU.START_MONITORING START_MONITORING,
        OU.END_MONITORING END_MONITORING
  FROM SYS.USER$        U,
        SYS.OBJ$         IO,
        SYS.OBJ$         T,
        SYS.IND$         I,
        SYS.OBJECT_USAGE OU
  WHERE I.OBJ# = OU.OBJ#
    AND IO.OBJ# = OU.OBJ#
    AND T.OBJ# = I.BO#
    AND U.USER# = IO.OWNER#;

2、通过查看历史的执行计划,分析索引的使用情况

可以从视图DBA_HIST_SQL_PLAN中获取到数据库中所有索引的扫描次数情况,然后根据扫描次数和开发人员沟通是否需要保留索引。

WITH TMP1 AS
 (SELECT I.OWNER INDEX_OWNER,
         I.TABLE_OWNER,
         TABLE_NAME,
         INDEX_NAME,
         INDEX_TYPE,
         (SELECT NB.CREATED
            FROM DBA_OBJECTS NB
           WHERE NB.OWNER = I.OWNER
             AND NB.OBJECT_NAME = I.INDEX_NAME
             AND NB.SUBOBJECT_NAME IS NULL
             AND NB.OBJECT_TYPE = 'INDEX') CREATED,
         (SUM(S.BYTES) / 1024 / 1024) INDEX_MB,
        (SELECT COUNT(1)
        FROM   DBA_IND_COLUMNS DIC
        WHERE  DIC.INDEX_NAME = I.INDEX_NAME
        AND    DIC.TABLE_NAME = I.TABLE_NAME
        AND    DIC.INDEX_OWNER = I.OWNER) COUNT_INDEX_COLS
    FROM DBA_SEGMENTS S, DBA_INDEXES I
   WHERE I.INDEX_NAME = S.SEGMENT_NAME
     AND I.OWNER = S.OWNER
     AND S.OWNER NOT LIKE '%SYS%'
   GROUP BY I.OWNER, I.TABLE_OWNER, TABLE_NAME, INDEX_NAME, INDEX_TYPE
  HAVING SUM(S.BYTES) > 1024 * 1024),
TMP2 AS
 (SELECT INDEX_OWNER,
         INDEX_NAME,
         PLAN_OPERATION,
         (SELECT MIN(TO_CHAR(NB.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24:MI:SS'))
            FROM DBA_HIST_SNAPSHOT NB
           WHERE NB.SNAP_ID = V.MIN_SNAP_ID) MIN_DATE,
         (SELECT MAX(TO_CHAR(NB.END_INTERVAL_TIME, 'YYYY-MM-DD HH24:MI:SS'))
            FROM DBA_HIST_SNAPSHOT NB
           WHERE NB.SNAP_ID = V.MAX_SNAP_ID) MAX_DATE,
         COUNTS
    FROM (SELECT D.OBJECT_OWNER INDEX_OWNER,
                  D.OBJECT_NAME INDEX_NAME,
                  D.OPERATION || ' ' || D.OPTIONS PLAN_OPERATION,
                  MIN(H.SNAP_ID) MIN_SNAP_ID,
                  MAX(H.SNAP_ID) MAX_SNAP_ID,
                  COUNT(1) COUNTS
             FROM DBA_HIST_SQL_PLAN D, DBA_HIST_SQLSTAT H
            WHERE D.OPERATION LIKE '%INDEX%'
         AND D.SQL_ID = H.SQL_ID
            GROUP BY D.OBJECT_OWNER, D.OBJECT_NAME, D.OPERATION, D.OPTIONS) V)
SELECT A.TABLE_OWNER,
       A.TABLE_NAME,
       A.INDEX_OWNER,
       A.INDEX_NAME,
       A.CREATED,
       A.INDEX_TYPE,
       A.INDEX_MB,
             A.COUNT_INDEX_COLS,
       B.PLAN_OPERATION,
       CASE
         WHEN MIN_DATE IS NULL THEN
          (SELECT MIN(TO_CHAR(NB.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24:MI:SS'))
             FROM DBA_HIST_SNAPSHOT NB)
         ELSE
          MIN_DATE
       END AS  MIN_DATE,
       CASE
         WHEN MAX_DATE IS NULL THEN
          (SELECT MAX(TO_CHAR(NB.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24:MI:SS'))
             FROM DBA_HIST_SNAPSHOT NB)
         ELSE
          MAX_DATE
       END AS  MAX_DATE,
       COUNTS
  FROM TMP1 A
  LEFT OUTER JOIN TMP2 B
    ON (A.INDEX_OWNER = B.INDEX_OWNER AND A.INDEX_NAME = B.INDEX_NAME);

假设有如下的运行结果:

从图中可以看到有一个3.6G大的索引在13号到22号从没使用过,接下来,可以继续查询该索引是否是联合索引,创建是否合理,分析为何不走该索引,从而判断是否可以删除索引。

& 说明:

有关索引的监控过程可以参考我的BLOG:http://blog.itpub.net/26736162/viewspace-2120752/

本文选自《Oracle程序员面试笔试宝典》,作者:李华荣。

本文分享自微信公众号 - DB宝(xiaomaimiaolhr),作者:小麦苗best

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-05-20

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

我来说两句

0 条评论
登录 后参与评论

相关文章

  • scrapy shell

    执行scrapy shell http://www.521609.com,查看response

    小麦苗DBA宝典
  • 【OCP最新题库解析(052)--题22】Which three of these must be accessible

    小麦苗DBA宝典
  • 【DB笔试面试467】Oracle中行列互换有哪些方法?

    行列转换包括以下六种情况:(1)列转行。(2)行转列。(3)多列转换成字符串。(4)多行转换成字符串。(5)字符串转换成多列。(6)字符串转换成多行。其中,重点...

    小麦苗DBA宝典
  • Node.js结合ProtoBuffer,从零实现一个redis! [一万字]

    Peter谭金杰
  • 测试工程师职业发展漫谈

    中国最早的对 IT 工程的启蒙和测试定位的探索大多来自于微软。当年的大背景是微软故意放任 Windows 的盗版,并积极的输出他自身的 IT 生态技术栈到中国。...

    IT大咖说
  • 正则表达式(四):Java regex

    Java 作为一种被广泛使用的编程语言,从 jdk-1.4 开始,标准库提供了 java.util.regex 包来支持正则表达式的使用。正则在 Java 中的...

    zhipingChen
  • 我们一直都在接受A / B测试

    网络正在被无处不在的实践所重塑 - 这是一个严重的道德问题 Testing出的解决方案是设计过程的核心部分,并且在网络上,即经常发生在A / B测试的形式:设...

    银河1号
  • JavaScript经典面试题(一)

    也就是,第 n 个数由数列的前两个相加而来:f(n) = f(n - 1) + f(n -2)

    祈澈菇凉
  • Entity Framework 的一些性能建议

    这是一篇我在2012年写的老文章,至今适用(没错,我说的就是适用于EF Core)。因此使用微信重新推送,希望能帮到大家。

    Edi Wang
  • Kebotix利用机器学习和机器人加速化合物和材料设计

    机器人手臂将移液器浸入培养皿中,并将少量明亮的液体转移到位于另一台机器前面的许多容器中。当所有样品都准备好后,第二台机器测试它们的光学特性,并将结果输送到控制臂...

    AiTechYun

扫码关注云+社区

领取腾讯云代金券