enq: TX – index contention
Waits for TX in mode 4 also occur when a transaction inserting a row in an index has to wait for the end of an index block split being done by another transaction.
This type of TX enqueue wait corresponds to the wait event enq: TX – index contention. This wait event shows one process waiting for another to complete an index block split.
If there is an index on a column that is being populated from a call to sequence.nextval on insert then you will most likely see many of these waits if your system inserts into the table very frequently from a number of concurrent sessions.
One solution for this problem is to identify which index is waiting for and try to create reverse key index if it is a single instance. If it is RAC then one has to increase the sequence cache value to few hundreds to thousands so that it reduces the competition for the same lead node across participating instances in RAC.
In our case we could able to get rid of this by increasing the Cache value for sequences where we have indexes.
No comments:
Post a Comment