RE: enq: TX - index contention

I think the confusion here IS that Oracle allocates 1 (ONE) initrans
(ITL) for EACH BLOCK that is allocated to the table. By default,
oracle allocates 2 BLOCKS (Database blocks) for every table (5 for
every index) that gets created. So, I can well see where you
would/could get 2 ITL for a newly created table based on the 2 data
blocks allocated.=20

Now, if you were using LMT, I'm sure that number would be greater based
on the larger number of DB blocks for the uniform size of the LMT.

However, it still only allocates ONE ILT for each block unless you
specify OTHERWISE. Hence the documentation WOULD be right.

Addendum.....this is cut from the Database Fundamentals II training
course
from Oracle Education....Power Point Slides

INITRANS: Guarantees a minimum level of concurrency. It defaults to 1
for a
data segment and 2 for an index segment, guarantees a minimum level of
concurrency. For example, if set to 3, INITRANS ensures that at least
three
transactions can concurrently make changes to the block. If necessary,
additional transaction slots can be allocated from the free space in the
block to permit more concurrent transactions to modify rows in the
block.

True in both the 9.0.1 release documentation AND in the 9.2.0.x
Documentation.

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,
--=20