從例項看oracle的索引監控與無效索引維護

一般觀點認為oracle資料庫使用的索引不會超過設計時建立索引總數的25%,或者不以它們被期望的使用方式使用.在實際應用中,調優速度較慢的查詢時,經常發現執行的sql呼叫了垃圾索引,而不是我們設計時建立的索引.所以我們有必要通過監控資料庫索引的使用,釋放那些未被使用的索引,從而節省維護索引的開銷,優化效能.

為了檢視目前系統中索引是否有效,我從2008.09.19號開始設定了索引監控,到目前共跟蹤了4天的執行資料.下面我根據得到的索引監控資訊,分幾個角度解析bi系統的後臺資料庫索引的有效性,及維護無效索引的記憶體,io和時間花銷.

1,索引有效性統計

首先建立一個用來儲存索引在監控時間段內是否被使用的臨時表ods.jax_t2.,

CREATE TABLE ods.jax_t2(

owner VARCHAR2(100),

index_name VARCHAR2(100),

table_name VARCHAR2(100),

MONITORING VARCHAR2(10),

used VARCHAR2(10)

)TABLESPACE odsd;

然後分別使用各不同賬戶登陸,並執行下面語句,將使用者的資訊統一寫入ods.jax_t2中.

INSERT INTO ods.jax_t2(owner,index_name,table_name,monitoring,used)

SELECT USER,index_name,table_name,MONITORING,used FROM V$OBJECT_USAGE;

COMMIT;

最後通過查詢表ods.jax_t2可以得到索引有效使用率.

SELECT owner, COUNT(INDEX_NAME),

       NVL(SUM(DECODE(USED, 'YES', 1, 0)), 0) 有效索引數目,

       ROUND(100 * NVL(SUM(DECODE(USED, 'YES', 1, 0)), 0) /

             COUNT(INDEX_NAME),

             2) 索引有效率

  FROM ods.jax_t2

  GROUP BY owner

  ORDER BY 索引有效率;

Owner
 索引總數
 有效索引數
 有效索引率率(%)
 
DC
 130
 0
 0
 
OD
 31
 0
 0
 
PRICE
 6
 0
 0
 
DP22
 70
 11
 15.71
 
WAREHOUSE
 91
 19
 20.88
 
TODS
 224
 48
 21.43
 
FBI
 89
 26
 29.21
 
ODS
 355
 105
 29.58
 
DP23
 70
 28
 40
 
DW
 50
 23
 46
 
RPT
 13
 6
 46.15
 
CTL
 32
 20
 62.5
 
合計
 1161
 286
 24.63
 

2,索引佔用空間資訊統計

資料字典dba_segments中儲存有各資料庫物件的空間分配情況.我們連立dba_segments和ods.jax_t2可以查詢得到各使用者總的空間分配和有效索引,無效索引所佔用的空間大小.從統計資訊中我們看到,在總共的61G索引中,只有11G左右的索引被有效利用.其他的索引空間在監控期間未被使用,這就是說,這50G的索引只有維護開銷,而沒能起到我們所設想的增加查詢速度的功能.

SELECT DS.OWNER, SEGMENT_TYPE, ROUND(SUM(BYTES) / 1024 / 1024),

  round(SUM(decode(jt.used,'YES',ds.bytes,0))/1024/1024) 有效索引,

  round(SUM(decode(jt.used,'NO',ds.bytes,0))/1024/1024) 無效索引

  FROM DBA_SEGMENTS DS,ods.jax_t2 jt

 WHERE ds.owner = jt.owner AND ds.segment_name = jt.Index_Name

   AND DS.OWNER NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'WMSYS')

   AND DS.SEGMENT_TYPE = 'INDEX'

 GROUP BY DS.OWNER, DS.SEGMENT_TYPE

 ORDER BY 無效索引

Owner
 物件型別
 索引總空間(M)
 有效索引空間(M)
 無效索引空間(M)
 
DP23
 INDEX
 5
 2
 3
 
DP22
 INDEX
 4
 1
 4
 
OD
 INDEX
 7
 0
 7
 
RPT
 INDEX
 10
 1
 9
 
CTL
 INDEX
 34
 22
 13
 
FBI
 INDEX
 199
 2
 197
 
PRICE
 INDEX
 200
 0
 200
 
TODS
 INDEX
 1504
 270
 1235
 
DC
 INDEX
 2188
 0
 2188
 
DW
 INDEX
 5212
 2325
 2887
 
ODS
 INDEX
 22240
 8703
 13537
 
WAREHOUSE
 INDEX
 29750
 4
 29745
 
總計
 Index
 61353
 11330
 50023
 

3,部分索引維護的空間和時間花銷

在這裡,我選擇了資料抽取過程中兩個相對執行時間教程的表CR_CUSTOMER_EXPIATION_A  as CCEA和CR_ORDER_ROLE as COR表進行一下分析.

 
 CCEA
 COR
 
記錄佔用空間
 28  (M)
 2112  (M)
 
索引佔用空間
 40  (M)
 5072  (M)
 
日維護記錄行數 刪除/插入
 550138/550952
 258593/279324
 
無效索引數/索引總數
 1/1
 2/4
 
刪除所需時間
 50.20  (S)
 172   (S)
 
插入所需時間
 16.25  (S)
 39.22  (S)
 
去掉無效索引後刪除時間
 19.88  (S)
 23.77  (S)
 
去掉無效索引後插入所需時間
 2.78   (S)
 13.75  (S)
 

    根據上面的比較結果我們看到,目前系統中索引佔用的總資料大小高達60G以上,但實際有效的索引佔用空間只有10G左右,絕大多數的索引只是增加了我們的維護時間和空間開銷,而無法為系統的效能提供支援,測試資料顯示,在刪除無效索引之後,系統的維護速度得到大幅度提高.所以我建議:

1, 對一些檢索比較頻繁的表,找出系統中引用該表的查詢語句,檢視其執行計劃,檢索是否使用正確索引;

2,如果已經使用正確索引,則考慮通過重建索引等手段檢視是否能提高查詢速度;

3,如果索引確實無法增加資料檢索的速度,則清除之.

 

本文來自CSDN部落格，轉載請標明出處：http://blog.csdn.net/47522341/archive/2008/09/22/2962144.aspx

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9390331/viewspace-696725/，如需轉載，請註明出處，否則將追究法律責任。