RE: enq: TX - index contention
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 22 Mar 2005 11:18:49 -0500
I believe that the default number of initrans for a table has been raised to
two as of version 9.0
-- Mark D Powell --
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of K Gopalakrishnan
Sent: Tuesday, March 22, 2005 10:10 AM
To: stalinsk@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: enq: TX - index contention
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:
