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