Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: enqueue waits for an INSERT statement

Re: enqueue waits for an INSERT statement

From: goran bogdanovic <goran00_at_gmail.com>
Date: Sun, 26 Nov 2006 15:39:04 +0100
Message-ID: <6d0a3ba80611260639o2124fa50tbc3239897cf660b8@mail.gmail.com>


>>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_at_il.proquest.com> 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
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Sun Nov 26 2006 - 08:39:04 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US