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