Oracle 监控索引使用率脚本分享

飞奔的炮台

Oracle提供了索引监控特性来判断索引是否被使用。在Oracle 10g中，收集统计信息会使得索引被监控，在Oracle 11g中该现象不复存在。尽管如此，该方式仅提供的是索引是否被使用。索引被使用的频率未能得以体现。下面的脚本将得到索引的使用率，可以很好的度量索引的使用情况以及根据这个值来判断当前的这些索引是否可以被移除或改进。
1、索引使用频率报告

--运行环境
SQL> select * from v$version where rownum<2;
BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
--获得当前数据库索引的使用频率
SQL> @idx_usage_detail.sql
Enter value for 1: GO_ADMIN
Enter value for 2: 100
                     Index
Table name       Index name       Index type  Size MB Index operation  Executions
------------------------------ ------------------------------ ------------ ----------- --------------------- ----------
ACC_POS_CASH_PL_TBL_ARC  PK_ACC_POS_CASH_PL_ARCH_TBL  NORMAL   3,328.00 RANGE SCAN      99
                      SAMPLE FAST FULL SCAN   8
                      UNIQUE SCAN      3
                      SKIP SCAN       2
****************************** ****************************** ************ -----------      ----------
sum                   13,312.00         112

ACC_POS_CASH_TBL_ARC    PK_ACC_POS_CASH_ARCH_TBL  NORMAL   2,560.00 RANGE SCAN      168
                      UNIQUE SCAN      14
                      SAMPLE FAST FULL SCAN   12
                      SKIP SCAN       1
****************************** ****************************** ************ -----------      ----------
sum                   10,240.00         195

ACC_POS_HIST_TBL    ACC_HIST_TRANS_DATE_IDX  NORMAL    384.00 RANGE SCAN      917
                      SKIP SCAN      210
                      SAMPLE FAST FULL SCAN   4
                      FAST FULL SCAN     1
        PK_ACC_POS_HIST_TBL    NORMAL    192.00 UNIQUE SCAN      7
                      SAMPLE FAST FULL SCAN   3
        TRANS_NUM_IDX     NORMAL    232.00 RANGE SCAN      41
                      SAMPLE FAST FULL SCAN   3
                      FAST FULL SCAN     1
****************************** ****************************** ************ -----------      ----------
sum                    2,616.00        1,187

ACC_POS_INT_TBL    ACC_POS_INT_10DIG_IDX   FUNCTION-  2,622.00 RANGE SCAN      59
                 BASED NORMAL
                      SAMPLE FAST FULL SCAN   4
                      FAST FULL SCAN     2
        PK_ACC_POS_INT_TBL     NORMAL   2,496.00 RANGE SCAN      65
                      FAST FULL SCAN    53
                      UNIQUE SCAN      14
                      SKIP SCAN       13
                      SAMPLE FAST FULL SCAN   1
****************************** ****************************** ************ -----------      ----------
sum                   20,346.00         211

ACC_POS_STOCK_TBL_ARC   PK_ACC_POS_STOCK_ARCH_TBL   NORMAL   18,977.00 RANGE SCAN      177
                      SAMPLE FAST FULL SCAN   10
                      UNIQUE SCAN      4
                      SKIP SCAN       3
****************************** ****************************** ************ -----------      ----------
sum                   75,908.00         194

STK_TBL_ARC      PK_STK_ARCH_TBL    NORMAL    920.00 RANGE SCAN      126
                      UNIQUE SCAN      38
                      SKIP SCAN       17
                      SAMPLE FAST FULL SCAN   2
****************************** ****************************** ************ -----------      ----------
sum                    3,680.00         183

STK_TBL_LOG      PK_STK_TBL_LOG     NORMAL    480.00 UNIQUE SCAN      56
****************************** ****************************** ************ -----------      ----------
sum                     480.00        56

TRADE_BROKER_CHRG_TBL_ARC   PK_TRADE_BROKER_CHRG_TBL_ARC  NORMAL    128.00  -      0
        UNI_TDBK_CHRG_ARC     NORMAL    104.00 RANGE SCAN      283
****************************** ****************************** ************ -----------      ----------
sum                     232.00         283

TRADE_BROKER_JOURNAL_TBL_ARC  IDX_TDBK_JRNL_ARC_ENTRY_DT   NORMAL    168.00  -      0
        IDX_TDBK_JRNL_ARC_INSTRU_ID  NORMAL    144.00 FULL SCAN       1
        IDX_TDBK_JRNL_ARC_STOCK_CD   NORMAL    144.00 FULL SCAN       1
        IDX_TDBK_JRNL_ARC_TRADED_PRICE NORMAL    144.00 FULL SCAN       1
        PK_TRADE_BROKER_JOURNAL_ARC  NORMAL    200.00  -      0
****************************** ****************************** ************ -----------      ----------
sum                     800.00        3

TRADE_CLIENT_CHRG_TBL_ARC   IDX_TDCL_CHRG_ARC_GRP_REF_ID  NORMAL    704.00 RANGE SCAN     3,537
        PK_TRADE_CLIENT_CHRG_TBL_ARC  NORMAL   1,539.00 RANGE SCAN      24
                      SAMPLE FAST FULL SCAN   2
        UNI_TDCL_CHRG_ARC     NORMAL   1,216.00 RANGE SCAN     1,103
                      FAST FULL SCAN     3
                      SAMPLE FAST FULL SCAN   2
****************************** ****************************** ************ -----------      ----------
sum                    7,430.00        4,671

TRADE_CLIENT_DTL_TBL_ARC  IDX_TDCL_DTL_ARC_ACTION_N_STUS NORMAL    312.00  -      0
        IDX_TDCL_DTL_ARC_ACT_TD_PRICE NORMAL    184.00 FULL SCAN       1
        IDX_TDCL_DTL_ARC_REF_ID  NORMAL    344.00 RANGE SCAN     4,623
                      FAST FULL SCAN     1
                      FULL SCAN       1
        IDX_TDCL_DTL_ARC_TRADED_PRICE NORMAL    184.00  -      0
        PK_TRADE_CLIENT_DTL_TBL_ARC  NORMAL    432.00  -      0
        UNI_TDCL_DTL_ARC_TRADE_DTL_ID NORMAL    272.00  -      0
****************************** ****************************** ************ -----------      ----------
sum                    2,416.00        4,626

TRADE_CLIENT_TBL_ARC    IDX_TDCL_ARC_ACC_NUM    NORMAL    152.00 RANGE SCAN      534
        IDX_TDCL_ARC_GRP_REF_ID  NORMAL    120.00 RANGE SCAN      550
                      FAST FULL SCAN     1
        IDX_TDCL_ARC_INPUT_DATE  NORMAL    120.00 RANGE SCAN     7,231
        IDX_TDCL_ARC_PL_STK    NORMAL    144.00 SKIP SCAN      156
                      RANGE SCAN       3
                      FULL SCAN       1
        IDX_TDCL_ARC_TRADE_DATE  NORMAL    120.00 RANGE SCAN    12,778
        PK_TRADE_CLIENT_TBL_ARC  NORMAL    160.00 RANGE SCAN      37
        UNI_TDCL_ARC_REF_ID    NORMAL    112.00 UNIQUE SCAN     157
                      FAST FULL SCAN     8
                      SAMPLE FAST FULL SCAN   1
****************************** ****************************** ************ -----------      ----------
sum                    1,560.00        21,457
--Author : Robinson
--Blog  : https://blog.csdn.net/robinson_0612
"Showed only indexes in GO_ADMIN schema whose size > 100 MB in period:"
30.01.2013-07.04.2013

2、结果分析与建议
a、上面的结果列出了当前数据库中schema为GOEX_ADMIN且索引大小大于100MB的索引的使用频率。
b、由于当前的数据库为标准版，没有分区表功能，所以可以看到很多arc结尾的表，且索引很大，如ACC_POS_STOCK_TBL_ARC上索引达到19G。
c、表ACC_POS_CASH_PL_TBL_ARC上的主键PK_ACC_POS_CASH_PL_ARCH_TBL上范围扫描最多，总计被使用次数为112次。
d、对于上述列出的被使用的次数为0的那些索引，应考虑索引的设置是否合理。
e、过大的索引应考虑能否使用索引压缩。
f、最后列出的是报告的schema名称以及索引大小的过滤条件、索引被收集的日期。注，索引列的大小sum求和有些不准确。
3、获得索引使用频率脚本

--该脚本作者为Damir Vadas，感谢Damir Vadas的贡献
robin@SZDB:~/dba_scripts/custom/sql> more idx_usage_detail.sql
/* ---------------------------------------------------------------------------
 CR/TR# :
 Purpose : Shows index usage by execution (find problematic indexes)
 
 Date  : 22.01.2008.
 Author : Damir Vadas, damir.vadas@gmail.com
 
 Remarks : run as privileged user
    Must have AWR run because sql joins data from there
    works on 10g >  
    
    @index_usage SCHEMA MIN_INDEX_SIZE
    
 Changes (DD.MM.YYYY, Name, CR/TR#):   
   25.11.2010, Damir Vadas
       added index size as parameter
   30.11.2010, Damir Vadas
       fixed bug in query
         
--------------------------------------------------------------------------- */
set linesize 140
set pagesize 160
 
clear breaks
clear computes
 
break on TABLE_NAME skip 2 ON INDEX_NAME ON INDEX_TYPE ON MB
compute sum of NR_EXEC on TABLE_NAME SKIP 2
compute sum of MB on TABLE_NAME SKIP 2
 
 
SET TIMI OFF
set linesize 140
set pagesize 10000
set verify off
col OWNER noprint
col TABLE_NAME for a30 heading 'Table name'
col INDEX_NAME for a30 heading 'Index name'
col INDEX_TYPE for a15 heading 'Index type'
col INDEX_OPERATION for a21 Heading 'Index operation'
col NR_EXEC for 9G999G990 heading 'Executions'
col MB for 999G990D90 Heading 'Index|Size MB' justify right
 
  WITH Q AS (
    SELECT
      S.OWNER     A_OWNER,
      TABLE_NAME    A_TABLE_NAME,
      INDEX_NAME    A_INDEX_NAME,
      INDEX_TYPE    A_INDEX_TYPE,
      SUM(S.bytes) / 1048576  A_MB
     FROM DBA_SEGMENTS S,
      DBA_INDEXES I
     WHERE S.OWNER = '&&1'
      AND I.OWNER = '&&1'
      AND INDEX_NAME = SEGMENT_NAME
     GROUP BY S.OWNER, TABLE_NAME, INDEX_NAME, INDEX_TYPE
    HAVING SUM(S.BYTES) > 1048576 * &&2
  )
  SELECT /*+ NO_QUERY_TRANSFORMATION(S) */
    A_OWNER          OWNER,
    A_TABLE_NAME        TABLE_NAME,
    A_INDEX_NAME        INDEX_NAME,
    A_INDEX_TYPE        INDEX_TYPE,
    A_MB          MB,
    DECODE (OPTIONS, null, '  -',OPTIONS) INDEX_OPERATION,
    COUNT(OPERATION)        NR_EXEC
   FROM Q,
    DBA_HIST_SQL_PLAN d
   WHERE
    D.OBJECT_OWNER(+)= q.A_OWNER AND
    D.OBJECT_NAME(+) = q.A_INDEX_NAME
  GROUP BY
    A_OWNER,
    A_TABLE_NAME,
    A_INDEX_NAME,
    A_INDEX_TYPE,
    A_MB,
    DECODE (OPTIONS, null, '  -',OPTIONS)
  ORDER BY
    A_OWNER,
    A_TABLE_NAME,
    A_INDEX_NAME,
    A_INDEX_TYPE,
    A_MB DESC,
    NR_EXEC DESC
;
PROMPT "Showed only indexes in &&1 schema whose size > &&2 MB in period:"
 
SET HEAD OFF;
select to_char (min(BEGIN_INTERVAL_TIME), 'DD.MM.YYYY')
  || '-' ||
  to_char (max(END_INTERVAL_TIME), 'DD.MM.YYYY')
from dba_hist_snapshot;
 
SET HEAD ON
SET TIMI ON

4、补充说明
    脚本使用了2个替代变量，一个是schema，一个是索引的大小。缺省情况下，对于那些较小的索引以及仅仅运行一至两次的sql语句的历史执行计划不会被收集到DBA_HIST_SQL_PLAN。因此执行脚本时索引大小输入的建议值是100。如果需要收集所有的历史sql执行计划来判断索引是否被使用，需要修改statistics_level为all或者修改snapshot的收集策略。收集策略对系统性能有一定的影响，以及耗用大量磁盘空间，因此Prod环境应慎用(UAT和DEV则无妨)。