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

数据库  /  管理员 发布于 6年前   116

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.sqlEnter value for 1: GO_ADMINEnter value for 2: 100     IndexTable 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   112ACC_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   195ACC_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,187ACC_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   211ACC_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   194STK_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   183STK_TBL_LOG          PK_STK_TBL_LOG         NORMAL      480.00 UNIQUE SCAN          56****************************** ****************************** ************ ---------------------sum   480.00    56TRADE_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   283TRADE_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    3TRADE_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,671TRADE_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,626TRADE_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  : http://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, [email protected]  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 140set pagesize 160 clear breaksclear computes break on TABLE_NAME skip 2 ON INDEX_NAME ON INDEX_TYPE ON MBcompute sum of NR_EXEC on TABLE_NAME SKIP 2compute sum of MB on TABLE_NAME SKIP 2  SET TIMI OFFset linesize 140set pagesize 10000set verify offcol OWNER noprintcol 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 (        SELECTS.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 ONSET TIMI ON

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