enqueue waits for an INSERT statement

  • From: "Ram Raman" <veeeraman@xxxxxxxxx>
  • To: oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 21 Nov 2006 22:28:47 -0800

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: