索引分裂造成的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@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25362835/viewspace-1058612/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25362835/viewspace-1058612/