Re: enqueue waits for an INSERT statement

If you have ITL shortage on one of the indexes on the table, that will
cause an index block split<<

interesting, I thought that index block will not need splitting as long as
available data area is not fully used...



On 11/22/06, Bobak, Mark <Mark.Bobak@xxxxxxxxxxxxxxx> wrote:

 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


Other related posts: