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

AiDBA宝典

发布于 2019-09-29 15:41:26

1.2K0

发布于 2019-09-29 15:41:26

文章被收录于专栏：小麦苗的DB宝专栏

♣

题目部分

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

假设有如下的运行结果：