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   : 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, 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则无妨)。     修改系统收集策略,可以参考:Oracle AWR 阙值影响历史执行计划     Reference: http://damir-vadas.blogspot.hk/2010/11/how-to-see-index-usage-without-alter.html

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

执行计划中的COLLECTION ITERATOR PICKLER FETCH导致的性能问题 (r5笔记第49天)

今天开发的同事找到我,让我评估一个sql语句。因为这条语句被应用监控组给抓取出来了,需要尽快进行性能调优。 sql语句比较长,是由几个Union连接起来的子查询...

3735
来自专栏乐沙弥的世界

NULL 值与索引(一)

    NULL值是关系数据库系统布尔型(true,false,unknown)中比较特殊类型的一种值,通常称为UNKNOWN或空值,即是未知的,不确定的。由...

3182
来自专栏杨建荣的学习笔记

通过使用hint unnest调优sql语句(r4笔记第38天)

生产环境中有一条sql语句通过sql_monitor看到执行的时间实在是太惊人了,竟然达到了13个小时,而且还没有执行完。 SessionAPPC (20015...

3198
来自专栏杨建荣的学习笔记

关于查询转换的一些简单分析(二) (r3笔记第68天)

关于查询转换的作用,在之前的一篇博文里面也大概说了视图合并和子查询嵌套的部分。http://blog.itpub.net/23718752/viewspace-...

28411
来自专栏杨建荣的学习笔记

sql_profile的使用(一) (r2笔记29天)

今天看了老熊关于sql_profile的讲解,受益匪浅,自己在本机也做了一通,感觉好记性不如烂笔头还是得多总结总测试才能真正理解。 准备的数据如下,创建两个表,...

2829
来自专栏黑泽君的专栏

day43_Oracle学习笔记_02

712
来自专栏乐沙弥的世界

SQL 基础--> 子查询

ORA-01427: single-row subquery returns more than one row

652
来自专栏杨建荣的学习笔记

关于查询转换的一些简单分析(一) (r3笔记第37天)

在sql解析器中,在生成执行计划的时候,会在多个执行计划中选择最优的计划,在这个过程中,查询转换就是一个很重要的过程。 虽然最终的执行结果没有变化,但是从优化器...

3045
来自专栏数据库新发现

使用dbms_rectifier_diff解决高级复制中的数据冲突问题

« Oracle基于时间点的恢复 | Blog首页 | 关于Oracle的冲突解决机制的研究 »

633
来自专栏乐沙弥的世界

当心外部连接中的ON子句

       在SQL tuning中,不良写法导致SQL执行效率比比皆是。最近的SQL tuning中一个外部连接写法不当导致过SQL执行时间超过15分钟左右...

1634

扫码关注云+社区

领取腾讯云代金券