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.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,187<