instance 1 等了 84秒 |
instance 2 等了 52秒(inst 2的圖我就不貼了) |
我初看就覺得納悶,怎麼會insert 造成lock |
去metalink找到了Doc ID 873243.1 |
Troubleshooting 'enq: TX - index contention' Waits |
它描述了在RAC環境下,大量insert會有什麼影響 |
原文如下 : |
|
When running an OLTP systems, it is possible to see high TX enqueue contention on index associated with tables, which |
are having high concurrency from the application. This usually happens when the application performs lot of INSERTs and |
DELETEs concurrently. For RAC system, the concurrent INSERTs and DELETEs could happen from all the instances . |
The reason for this is the index block splits while inserting a new row into the index. The transactions will have to wait for |
TX lock in mode 4, until the session that is doing the block splits completes the operations. |
A session will initiate a index block split, when it can't find space in an index block where it needs to insert a new row. |
Before starting the split, it would clean out all the keys in the block to check whether there is enough sufficient space in |
the block.deleted |
Splitter has to do the following activities: |
o Allocate a new block. |
o Copy a percentage of rows to the new buffer. |
o Add the new buffer to the index structure and commit the operation. |
In RAC environments, this could be an expensive operation, due to the global cache operations included. The impact will |
be more if the split is happening at a branch or root block level. |
|
可是我的狀況是row lock contention |
但依照這篇文件的指示 |
我再去看它所要求的AWR報表內 |
Segments by Row Lock Waits |
的數據 |
我就恍然大悟 |
就是你了..... |
它就是該表的索引 |