索引分裂造成的index contention等待事件的診斷

SQL> select event,count(*) from v$session group by event;

EVENT COUNT(*)

---------------------------------------------------------------- ----------

SQL*Net message from client 44

latch: cache buffers chains 1

rdbms ipc message 9

smon timer 1

pmon timer 1

SQL*Net message to client 1

enq: TX - index contention 665

7 rows selected.

SQL> show parameter processes;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

aq_tm_processes integer 0

db_writer_processes integer 1

gcs_server_processes integer 0

job_queue_processes integer 0

log_archive_max_processes integer 2

processes integer 1000

SQL> select * from v$version where rownum<2;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi

系統中出現了大量的索引分裂造成了系統的process連線得不到合適的釋放，導致後續連線數越來越多，最終達到了processes的值導致系統不得不需要重啟，剛開始我覺得可能是中介軟體的連線池沒有釋放導致。

透過下列指令碼

SELECT c.sid,

c.event,

a.sql_text,

a.hash_value

FROM v$sql a, v$session b, v$session_Wait c

WHERE c.wait_class <> 'Idle'

AND c.sid = b.sid

AND b.sql_hash_value = a.hash_value

order by c.event desc,a.sql_text desc

發現index contention等待都發生是下列sql語句中：

INSERT INTO ROBOTS_SUBJECTVISIT (ID,SUBJECT,BEGINTIME,ENDTIME,LINKS,BBSITEMS,NEWITEMS) VALUES (:1,:2,:3,:4,:5,:6,:7)

由於系統中併發較高導致了資料插入頻繁導致了索引分裂，該等待事件並沒有過多的buffer busy wait、latch cache buffer chain等的事件，而且系統正常情況下processes連線在200到300應該是正常連線數，看來真的是頻繁插入導致的索引分裂連線不能釋放，最後新的連線連入最終導致系統只有重啟。

可以考慮的解決方法：

1 重建index為反轉索引，這個可以有效的分散索引的鍵值，不過可能導致部分sql語句的執行計劃改變，index range scan無法執行。

2 當然可以考慮增大pctfree引數，讓空間使用較少，從而減少index分裂的競爭。

透過maclean的網站中給出了下列兩種方法：

對索引進行coalesce是合併同一個branch的leaf block，並不需要重新排序，而rebuild需要重新排序index，並且需要兩個index的空間。如果對索引rebuild也會變相的壓縮索引的空間，導致後續的insert同樣出現競爭。

Global hash index

建立全域性hash的分割槽索引是可以有效減低index contention的競爭的，該功能在10g中允許，在9i僅僅只能建立range的全域性分割槽索引。

create index pk001 on t_samp01(object_id) global partition by hash(object_id) partitions 8

這裡自己採用建立全域性分割槽hash index的方式，建立分割槽索引後再沒有出現大量的index contention等待事件。

[@more@]