Re: enq: TX - index contention
- From: K Gopalakrishnan <kaygopal@xxxxxxxxx>
- To: stalinsk@xxxxxxxxx
- Date: Tue, 22 Mar 2005 20:39:40 +0530
Stalin:
TX enqueue waits with LMODE 4 can happen for the following reasons.
Quoting from 'Oracle Wait Interface'
"A wait for the TX enqueue in mode 4 is normally due to one of the
following reasons:
ITL (interested transaction list) shortage
Unique key enforcement
Bitmap index entry
Here, we will talk about the ITL, which is a transaction slot in a
data block. The initial number of ITL slots is defined by the INITRANS
clause and is limited by the MAXTRANS clause. By default, a table has
1 ITL and an index has 2 ITLs. Each ITL takes up 24 bytes and contains
the transaction ID in the format of USN.SLOT#.WRAP#. Every DML
transaction needs to acquire its own ITL space within a block before
data can be manipulated. Contention for ITL occurs when all the
available ITLs within a block are currently in use and there is not
enough space in the PCTFREE area for Oracle to dynamically allocate a
new ITL slot. In this case, the session will wait until one of the
transactions is committed or rolled back, and it will reuse that ITL
slot. ITL is like a building parking space. Everyone who drives to the
building needs a parking space. If the parking lot is full, you have
to circle the lot until someone leaves the building. "
So I would expect any of the above conditions in your case? You may
want to query the V$segment_statistics for ITL waits to eliminate the
ITL issue. If you have bitmap indexes, that should be one of the
casues for those excesive waits. The wait time is close to 3 seconds
and I suspect that could be because of the bitmap index issue,
--
Best Regards,
K Gopalakrishnan
Co-Author: Oracle Wait Interface, Oracle Press 2004
http://www.amazon.com/exec/obidos/tg/detail/-/007222729X/
--
//www.freelists.org/webpage/oracle-l
Other related posts:
