Oracle提供了索引監控特性來判斷索引是否被使用。在Oracle 10g中，收集統計信息會使得索引被監控，在Oracle 11g中該現象不復存在。盡管如此，該方式僅提供的是索引是否被使用。索引被使用的頻率未能得以體現。下面的腳本將得到索引的使用率，可以很好的度量索引的使用情況以及根據這個值來判斷當前的這些索引是否可以被移除或改進。
1、索引使用頻率報告
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
|
--運行環境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、獲得索引使用頻率腳本
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
|
--該腳本作者為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, [email protected] 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則無妨)。
