Oracle 监控索引的使用率

怼烎@ 2022-03-18 03:52 290阅读 0赞

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, 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 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 (                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 ONSET TIMI ON

4、补充说明  
脚本使用了2个替代变量，一个是schema，一个是索引的大小。缺省情况下，对于那些较小的索引以及仅仅运行一至两次的sql语句的历史执行计划不会被收集到DBA\_HIST\_SQL\_PLAN。因此执行脚本时索引大小输入的建议值是100。如果需要收集所有的历史sql执行计划来判断索引是否被使用，需要修改statistics\_level为all或者修改snapshot的收集策略。收集策略对系统性能有一定的影响，以及耗用大量磁盘空间，因此Prod环境应慎用(UAT和DEV则无妨)。  
修改系统收集策略，可以参考:[Oracle AWR 阙值影响历史执行计划  
][Oracle AWR _] Reference: [http://damir-vadas.blogspot.hk/2010/11/how-to-see-index-usage-without-alter.html][http_damir-vadas.blogspot.hk_2010_11_how-to-see-index-usage-without-alter.html]

更多参考

[PL/SQL --> 游标][PL_SQL --_]

[PL/SQL --> 隐式游标(SQL%FOUND)][PL_SQL --_ _SQL_FOUND]

[批量SQL之 FORALL 语句][SQL_ FORALL]

[批量SQL之 BULK COLLECT 子句][SQL_ BULK COLLECT]

[PL/SQL 集合的初始化与赋值  
][PL_SQL _]

[PL/SQL 联合数组与嵌套表][PL_SQL _]  
[PL/SQL 变长数组][PL_SQL]  
[PL/SQL --> PL/SQL记录][PL_SQL --_ PL_SQL]

[SQL tuning 步骤][SQL tuning]

[高效SQL语句必杀技  
][SQL_]

[父游标、子游标及共享游标][Link 1]

[绑定变量及其优缺点][Link 2]

[dbms\_xplan之display\_cursor函数的使用][dbms_xplan_display_cursor]

[dbms\_xplan之display函数的使用][dbms_xplan_display]

[执行计划中各字段各模块描述][Link 3]

[使用 EXPLAIN PLAN 获取SQL语句执行计划][EXPLAIN PLAN _SQL]

[Oracle ROWID][]

[NULL 值与索引(一)][SQL tuning]

[NULL 值与索引(二)  
][NULL _]

[启用 AUTOTRACE 功能][AUTOTRACE]

[函数使得索引列失效][Link 4]

[Oracle 绑定变量窥探][Oracle]

[Oracle 自适应共享游标][Oracle 1]

[Oracle 表空间与数据文件  
][Oracle _][Oracle 密码文件  
][Oracle _ 1][Oracle 参数文件  
][Oracle _ 2][Oracle 联机重做日志文件(ONLINE LOG FILE)  
][Oracle _ONLINE LOG FILE_][Oracle 控制文件(CONTROLFILE)  
][Oracle _CONTROLFILE_][Oracle 归档日志  
][Oracle _ 3][Oracle 回滚(ROLLBACK)和撤销(UNDO)  
][Oracle _ROLLBACK_UNDO_][Oracle 数据库实例启动关闭过程  
][Oracle _ 4][Oracle 10g SGA 的自动化管理  
][Oracle 10g SGA _][Oracle 实例和Oracle数据库(Oracle体系结构)][Oracle _Oracle_Oracle]

再分享一下我老师大神的人工智能教程吧。零基础！通俗易懂！风趣幽默！还带黄段子！希望你也加入到我们人工智能的队伍中来！[https://blog.csdn.net/jiangjunshow][https_blog.csdn.net_jiangjunshow]

[Oracle AWR _]: http://blog.csdn.net/robinson_0612/article/details/8732708
[http_damir-vadas.blogspot.hk_2010_11_how-to-see-index-usage-without-alter.html]: http://damir-vadas.blogspot.hk/2010/11/how-to-see-index-usage-without-alter.html
[PL_SQL --_]: http://blog.csdn.net/robinson_0612/archive/2010/12/15/6078622.aspx
[PL_SQL --_ _SQL_FOUND]: http://blog.csdn.net/robinson_0612/archive/2010/12/22/6092066.aspx
[SQL_ FORALL]: http://blog.csdn.net/robinson_0612/article/details/7536926
[SQL_ BULK COLLECT]: http://blog.csdn.net/robinson_0612/article/details/7545597
[PL_SQL _]: http://blog.csdn.net/robinson_0612/article/details/7372061
[PL_SQL]: http://blog.csdn.net/robinson_0612/article/details/7387647
[PL_SQL --_ PL_SQL]: http://blog.csdn.net/robinson_0612/article/details/6084390
[SQL tuning]: http://blog.csdn.net/robinson_0612/article/details/7422254
[SQL_]: http://blog.csdn.net/robinson_0612/article/details/7406672
[Link 1]: http://blog.csdn.net/robinson_0612/article/details/6893477
[Link 2]: http://blog.csdn.net/robinson_0612/article/details/6904229
[dbms_xplan_display_cursor]: http://blog.csdn.net/robinson_0612/article/details/6866925
[dbms_xplan_display]: http://blog.csdn.net/robinson_0612/article/details/6866870
[Link 3]: http://blog.csdn.net/robinson_0612/article/details/6860007
[EXPLAIN PLAN _SQL]: http://blog.csdn.net/robinson_0612/article/details/6837771
[Oracle ROWID]: http://blog.csdn.net/robinson_0612/article/details/6931886
[NULL _]: http://blog.csdn.net/robinson_0612/article/details/7438397
[AUTOTRACE]: http://blog.csdn.net/robinson_0612/article/details/6030407
[Link 4]: http://blog.csdn.net/robinson_0612/article/details/6851973
[Oracle]: http://blog.csdn.net/robinson_0612/article/details/6923627
[Oracle 1]: http://blog.csdn.net/robinson_0612/article/details/6923670
[Oracle _]: http://blog.csdn.net/robinson_0612/article/details/5611738
[Oracle _ 1]: http://blog.csdn.net/robinson_0612/article/details/5611672
[Oracle _ 2]: http://blog.csdn.net/robinson_0612/article/details/5559174
[Oracle _ONLINE LOG FILE_]: http://blog.csdn.net/robinson_0612/article/details/5749556
[Oracle _CONTROLFILE_]: http://blog.csdn.net/robinson_0612/article/details/5737527
[Oracle _ 3]: http://blog.csdn.net/robinson_0612/article/details/5784713
[Oracle _ROLLBACK_UNDO_]: http://blog.csdn.net/robinson_0612/article/details/5731158
[Oracle _ 4]: http://blog.csdn.net/robinson_0612/article/details/5542983
[Oracle 10g SGA _]: http://blog.csdn.net/robinson_0612/article/details/5534832
[Oracle _Oracle_Oracle]: http://blog.csdn.net/robinson_0612/article/details/5529239
[https_blog.csdn.net_jiangjunshow]: https://blog.csdn.net/jiangjunshow/article/details/77338485