RE: enqueue waits for an INSERT statement

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]
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: