RE: enqueue waits for an INSERT statement

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

ProQuest Information & Learning

There is nothing so useless as doing efficiently that which shouldn't be
done at all.  -Peter F. Drucker, 1909-2005

 

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Sam Chakkanat
Sent: Wednesday, November 22, 2006 9:36 AM
To: veeeraman@xxxxxxxxx
Cc: 'oracle-l'
Subject: RE: enqueue waits for an INSERT statement


Try increasing INITRANS 10, PCTFREE 60 both table and index.
Also, what is the NEXT extent size? Ideally this should be uniform.
If this is a large table > 100 columns? Then guestimate the avg Colum
size
and set the NEXT extent size to 50% more than col size * num of rows
inserting.
Now, the other side.  Dedicate a BIG roll back segment to this
transaction.
 
You may want to export and re-create the table/indexes to change the
INITRANS.
 
 
finally,  TEST , TEST, TEST....couple of small runs with SQL Timing on
would guide you 
if the change is working.
 
Hope this helps.
 
Thank You,
 
Sam Chakkanat
(949)-394-3355
America's C-Bench
http://www.cbenchusa.com <http://www.cbenchusa.com/> 
 
 
________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Ram Raman
Sent: Wednesday, November 22, 2006 6:14 AM
To: Sam Chakkanat
Cc: oracle-l
Subject: Re: enqueue waits for an INSERT statement


 
Inittrans for the big table is 1. Max trans 255. 
The big table has 3 indexes. The inseret is expected to be an ongoing
process. We want to tune it to run faster. PCT_FREE and PCT_USED: 10 and
40. THanks.
 
 
 
On 11/22/06, Sam Chakkanat <cvsam@xxxxxxx> wrote: 

        Ram,
         
        Please check the intitrans for the big table.  Also, does the
big table has indexes?
        Is this process is only one time or you expect to have this
insert ongoing?
        One of my client, I have done increasing the inittrans of table
and associated indexes,
        the NEXT segment space and the PCTFREE parameter.  If this is a
batch inserts
        and could manageable, then you could disable all associated
indexes and try.
         
        Sam

________________________________

        From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx <mailto:oracle-l-bounce@xxxxxxxxxxxxx> ]
On Behalf Of Ram Raman
        Sent: Tuesday, November 21, 2006 10:29 PM
        To: oracle-l
        Subject: enqueue waits for an INSERT statement
        
         
        
        Hi all,
         
          Oracle version: 9206
         
          I am trying to insert all the rows (~12 million rows) from a
small table into a bigger table (~75 million rows). I am testing it with
an 
         
                       INSERT INTO big_table SELECT * FROM small_table 
         
         statement. 
         
        The statement seem to be waiting on 'enqueue' event a lot. The
process started 2 hrs ago. 

        00:16:34 SQL> l
          1  select sid, EVENT, TOTAL_WAITS, TIME_WAITED, AVERAGE_WAIT
          2    from v$session_event
          3    where sid= 39
          4*   and AVERAGE_WAIT > 100
        00:16:34 SQL> /
        more..

               SID EVENT
        ----------
----------------------------------------------------------------
        TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
        ----------- ----------- ------------
                39 enqueue
               2194      644304          294 

                39 SQL*Net message from client
                 28      137596         4914

        
        00:16:36 SQL> /
        more..

               SID EVENT
        ----------
----------------------------------------------------------------
        TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
        ----------- ----------- ------------
                39 enqueue
               2195      644598          294 

                39 SQL*Net message from client
                 28      137596         4914

        
        The time_waited for the first row looks too high at 107 minutes.
Does this 'enqueue' represent 'ITL waits'? 

        There is lots of empty blocks below the highwater mark as I have
been doing lots of deletes and inserting using direct load insert, which
incidentally does this load under 40 minutes, but it wastes space. That
is main reason I am trying this approach without /*+APPEND*/ hint. This
database does NOT have partitioning. 

        The tablespace of the big_table is in manual SEGMENT SPACE
MANAGEMENT mode. 

        Thanks.

Other related posts: