Stop!
Don't bother with INITRANS/MAXTRANS. An INSERT statement will NOT wait on
ITL. It will just go to another block on the freelist. If you have ITL
shortage on one of the indexes on the table, that will cause an index block
split, but again, will NOT wait on ITL.
First, determine what type of enqueue is being waited on, and in what
mode. Steve Adams has an excellent script for this, shows blockers and
waiters.
See:
http://www.ixora.com.au/scripts/sql/enqueue_locks.sql
Once you know the enqueue type (in this case, it's almost certainly TX)
and what mode is being waited on (in this case, probably 'S' mode), you can
determine the true root cause.
Likely cause of enqueue waits on INSERT are:
- bitmap index (is there a bitmap index on the table being inserted
into?)
- PK/UK constraint enforcement on a heap table
- PK constraint violation on an IOT
Hope that helps,
-Mark
***--*
*Mark J. Bobak*
*Senior Oracle Architect*
***P**** ro****Q**** uest Information & Learning*
There is nothing so useless as doing e fficiently that which shouldn't be
done at all. –Peter F. Drucker, 1909-2005
