Oracle 监控索引使用率脚本分享
Oracle提供了索引监控特性来判断索引是否被使用。在Oracle10g中，收集统计信息会使得索引被监控，在Oracle11g中该现象不复存在。尽管如此，该方式仅提供的是索引是否被使用。索引被使用的频率未能得以体现。下面的脚本将得到索引的使用率，可以很好的度量索引的使用情况以及根据这个值来判断当前的这些索引是否可以被移除或改进。
1、索引使用频率报告
--运行环境 SQL>select*fromv$versionwhererownum<2; BANNER ---------------------------------------------------------------- OracleDatabase10gRelease10.2.0.3.0-64bitProduction --获得当前数据库索引的使用频率 SQL>@idx_usage_detail.sql Entervaluefor1:GO_ADMIN Entervaluefor2:100 Index TablenameIndexnameIndextypeSizeMBIndexoperationExecutions ------------------------------------------------------------------------------------------------------------------ ACC_POS_CASH_PL_TBL_ARCPK_ACC_POS_CASH_PL_ARCH_TBLNORMAL3,328.00RANGESCAN99 SAMPLEFASTFULLSCAN8 UNIQUESCAN3 SKIPSCAN2 ************************************************************************--------------------- sum13,312.00112 ACC_POS_CASH_TBL_ARCPK_ACC_POS_CASH_ARCH_TBLNORMAL2,560.00RANGESCAN168 UNIQUESCAN14 SAMPLEFASTFULLSCAN12 SKIPSCAN1 ************************************************************************--------------------- sum10,240.00195 ACC_POS_HIST_TBLACC_HIST_TRANS_DATE_IDXNORMAL384.00RANGESCAN917 SKIPSCAN210 SAMPLEFASTFULLSCAN4 FASTFULLSCAN1 PK_ACC_POS_HIST_TBLNORMAL192.00UNIQUESCAN7 SAMPLEFASTFULLSCAN3 TRANS_NUM_IDXNORMAL232.00RANGESCAN41 SAMPLEFASTFULLSCAN3 FASTFULLSCAN1 ************************************************************************--------------------- sum2,616.001,187 ACC_POS_INT_TBLACC_POS_INT_10DIG_IDXFUNCTION-2,622.00RANGESCAN59 BASEDNORMAL SAMPLEFASTFULLSCAN4 FASTFULLSCAN2 PK_ACC_POS_INT_TBLNORMAL2,496.00RANGESCAN65 FASTFULLSCAN53 UNIQUESCAN14 SKIPSCAN13 SAMPLEFASTFULLSCAN1 ************************************************************************--------------------- sum20,346.00211 ACC_POS_STOCK_TBL_ARCPK_ACC_POS_STOCK_ARCH_TBLNORMAL18,977.00RANGESCAN177 SAMPLEFASTFULLSCAN10 UNIQUESCAN4 SKIPSCAN3 ************************************************************************--------------------- sum75,908.00194 STK_TBL_ARCPK_STK_ARCH_TBLNORMAL920.00RANGESCAN126 UNIQUESCAN38 SKIPSCAN17 SAMPLEFASTFULLSCAN2 ************************************************************************--------------------- sum3,680.00183 STK_TBL_LOGPK_STK_TBL_LOGNORMAL480.00UNIQUESCAN56 ************************************************************************--------------------- sum480.0056 TRADE_BROKER_CHRG_TBL_ARCPK_TRADE_BROKER_CHRG_TBL_ARCNORMAL128.00-0 UNI_TDBK_CHRG_ARCNORMAL104.00RANGESCAN283 ************************************************************************--------------------- sum232.00283 TRADE_BROKER_JOURNAL_TBL_ARCIDX_TDBK_JRNL_ARC_ENTRY_DTNORMAL168.00-0 IDX_TDBK_JRNL_ARC_INSTRU_IDNORMAL144.00FULLSCAN1 IDX_TDBK_JRNL_ARC_STOCK_CDNORMAL144.00FULLSCAN1 IDX_TDBK_JRNL_ARC_TRADED_PRICENORMAL144.00FULLSCAN1 PK_TRADE_BROKER_JOURNAL_ARCNORMAL200.00-0 ************************************************************************--------------------- sum800.003 TRADE_CLIENT_CHRG_TBL_ARCIDX_TDCL_CHRG_ARC_GRP_REF_IDNORMAL704.00RANGESCAN3,537 PK_TRADE_CLIENT_CHRG_TBL_ARCNORMAL1,539.00RANGESCAN24 SAMPLEFASTFULLSCAN2 UNI_TDCL_CHRG_ARCNORMAL1,216.00RANGESCAN1,103 FASTFULLSCAN3 SAMPLEFASTFULLSCAN2 ************************************************************************--------------------- sum7,430.004,671 TRADE_CLIENT_DTL_TBL_ARCIDX_TDCL_DTL_ARC_ACTION_N_STUSNORMAL312.00-0 IDX_TDCL_DTL_ARC_ACT_TD_PRICENORMAL184.00FULLSCAN1 IDX_TDCL_DTL_ARC_REF_IDNORMAL344.00RANGESCAN4,623 FASTFULLSCAN1 FULLSCAN1 IDX_TDCL_DTL_ARC_TRADED_PRICENORMAL184.00-0 PK_TRADE_CLIENT_DTL_TBL_ARCNORMAL432.00-0 UNI_TDCL_DTL_ARC_TRADE_DTL_IDNORMAL272.00-0 ************************************************************************--------------------- sum2,416.004,626 TRADE_CLIENT_TBL_ARCIDX_TDCL_ARC_ACC_NUMNORMAL152.00RANGESCAN534 IDX_TDCL_ARC_GRP_REF_IDNORMAL120.00RANGESCAN550 FASTFULLSCAN1 IDX_TDCL_ARC_INPUT_DATENORMAL120.00RANGESCAN7,231 IDX_TDCL_ARC_PL_STKNORMAL144.00SKIPSCAN156 RANGESCAN3 FULLSCAN1 IDX_TDCL_ARC_TRADE_DATENORMAL120.00RANGESCAN12,778 PK_TRADE_CLIENT_TBL_ARCNORMAL160.00RANGESCAN37 UNI_TDCL_ARC_REF_IDNORMAL112.00UNIQUESCAN157 FASTFULLSCAN8 SAMPLEFASTFULLSCAN1 ************************************************************************--------------------- sum1,560.0021,457 --Author:Robinson --Blog:http://blog.csdn.net/robinson_0612 "ShowedonlyindexesinGO_ADMINschemawhosesize>100MBinperiod:" 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、获得索引使用频率脚本
--该脚本作者为DamirVadas，感谢DamirVadas的贡献 robin@SZDB:~/dba_scripts/custom/sql>moreidx_usage_detail.sql /*--------------------------------------------------------------------------- CR/TR#: Purpose:Showsindexusagebyexecution(findproblematicindexes) Date:22.01.2008. Author:DamirVadas,damir.vadas@gmail.com Remarks:runasprivilegeduser MusthaveAWRrunbecausesqljoinsdatafromthere workson10g> @index_usageSCHEMAMIN_INDEX_SIZE Changes(DD.MM.YYYY,Name,CR/TR#): 25.11.2010,DamirVadas addedindexsizeasparameter 30.11.2010,DamirVadas fixedbuginquery ---------------------------------------------------------------------------*/ setlinesize140 setpagesize160 clearbreaks clearcomputes breakonTABLE_NAMEskip2ONINDEX_NAMEONINDEX_TYPEONMB computesumofNR_EXEConTABLE_NAMESKIP2 computesumofMBonTABLE_NAMESKIP2 SETTIMIOFF setlinesize140 setpagesize10000 setverifyoff colOWNERnoprint colTABLE_NAMEfora30heading'Tablename' colINDEX_NAMEfora30heading'Indexname' colINDEX_TYPEfora15heading'Indextype' colINDEX_OPERATIONfora21Heading'Indexoperation' colNR_EXECfor9G999G990heading'Executions' colMBfor999G990D90Heading'Index|SizeMB'justifyright WITHQAS( SELECT S.OWNERA_OWNER, TABLE_NAMEA_TABLE_NAME, INDEX_NAMEA_INDEX_NAME, INDEX_TYPEA_INDEX_TYPE, SUM(S.bytes)/1048576A_MB FROMDBA_SEGMENTSS, DBA_INDEXESI WHERES.OWNER='&&1' ANDI.OWNER='&&1' ANDINDEX_NAME=SEGMENT_NAME GROUPBYS.OWNER,TABLE_NAME,INDEX_NAME,INDEX_TYPE HAVINGSUM(S.BYTES)>1048576*&&2 ) SELECT/*+NO_QUERY_TRANSFORMATION(S)*/ A_OWNEROWNER, A_TABLE_NAMETABLE_NAME, A_INDEX_NAMEINDEX_NAME, A_INDEX_TYPEINDEX_TYPE, A_MBMB, DECODE(OPTIONS,null,'-',OPTIONS)INDEX_OPERATION, COUNT(OPERATION)NR_EXEC FROMQ, DBA_HIST_SQL_PLANd WHERE D.OBJECT_OWNER(+)=q.A_OWNERAND D.OBJECT_NAME(+)=q.A_INDEX_NAME GROUPBY A_OWNER, A_TABLE_NAME, A_INDEX_NAME, A_INDEX_TYPE, A_MB, DECODE(OPTIONS,null,'-',OPTIONS) ORDERBY A_OWNER, A_TABLE_NAME, A_INDEX_NAME, A_INDEX_TYPE, A_MBDESC, NR_EXECDESC ; PROMPT"Showedonlyindexesin&&1schemawhosesize>&&2MBinperiod:" SETHEADOFF; selectto_char(min(BEGIN_INTERVAL_TIME),'DD.MM.YYYY') ||'-'|| to_char(max(END_INTERVAL_TIME),'DD.MM.YYYY') fromdba_hist_snapshot; SETHEADON SETTIMION
4、补充说明
脚本使用了2个替代变量，一个是schema，一个是索引的大小。缺省情况下，对于那些较小的索引以及仅仅运行一至两次的sql语句的历史执行计划不会被收集到DBA_HIST_SQL_PLAN。因此执行脚本时索引大小输入的建议值是100。如果需要收集所有的历史sql执行计划来判断索引是否被使用，需要修改statistics_level为all或者修改snapshot的收集策略。收集策略对系统性能有一定的影响，以及耗用大量磁盘空间，因此Prod环境应慎用(UAT和DEV则无妨)。