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.

--

http://www.freelists.org/webpage/oracle-l Received on Wed Nov 22 2006 - 00:28:47 CST