| 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 |
| 的數據 |
| 我就恍然大悟 |
| 就是你了..... |
| 它就是該表的索引 |
