RE: enq: TX - index contention

  • From: "Hollis, Les" <Les.Hollis@xxxxxx>
  • To: <mark.powell@xxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 22 Mar 2005 11:31:45 -0600

I think not.

SQL> create table junky1 (name varchar2(5));

Table created.

SQL> select ini_trans from user_tables where table_name =3D 'JUNKY1';


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Powell, Mark D
Sent: Tuesday, March 22, 2005 10:19 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: enq: TX - index contention

I believe that the default number of initrans for a table has been
raised to
two as of version 9.0  =20

-- Mark D Powell --

-----Original Message-----
From: 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


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
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
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
transaction ID in the format of USN.SLOT#.WRAP#. Every DML transaction
to acquire its own ITL space within a block before data can be
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
will reuse that ITL slot. ITL is like a building parking space. Everyone
drives to the building needs a parking space. If the parking lot is
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
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
could be because of the bitmap index issue,

Best Regards,
K Gopalakrishnan
Co-Author: Oracle Wait Interface, Oracle Press 2004



Other related posts: