enq: TX - index contention

看到論壇上有人討論，研究了下

enq: TX - index contention
     Lock held on an index during a split to prevent other operations on it
--------------------------------------------------------------------------
DROP TABLE test;

CREATE TABLE test(ID CHAR(1000));

CREATE INDEX idx_test ON test(ID);

[oracle10@WESTZQ ~]$ cat run.sh
sqlplus ctais2/oracle <BEGIN
  FOR i IN 1..100000 LOOP
    INSERT INTO test VALUES(to_char(i));
    COMMIT;
  END LOOP;
END;
/
EOF

nohup ./run.sh &
nohup ./run.sh &
nohup ./run.sh &
nohup ./run.sh &
nohup ./run.sh &
nohup ./run.sh &
nohup ./run.sh &

SQL> select event from v$session_wait;

EVENT
----------------------------------------------------------------
SQL*Net message from client
free buffer waits
enq: TX - index contention
SQL*Net message to client
enq: TX - index contention
SQL*Net message from client
enq: TX - index contention
free buffer waits
free buffer waits
free buffer waits
enq: TX - index contention
free buffer waits
SQL*Net message from client
SQL*Net message from client
wait for unread message on broadcast channel
SQL*Net message from client
SQL*Net message from client
SQL*Net message from client
SQL*Net message from client
SQL*Net message from client

EVENT
----------------------------------------------------------------
Streams AQ: qmn slave idle wait
SQL*Net message from client
latch: library cache
SQL*Net message from client
Streams AQ: waiting for messages in the queue
Streams AQ: qmn coordinator idle wait
jobq slave wait
Log archive I/O
rdbms ipc message
SQL*Net message from client
free buffer waits
rdbms ipc message
rdbms ipc message
rdbms ipc message
rdbms ipc message
smon timer
rdbms ipc message
rdbms ipc message
latch: shared pool
rdbms ipc message
rdbms ipc message

EVENT
----------------------------------------------------------------
pmon timer

處理的方法就是反轉索引就可以了

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