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

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

enqueue waits for an INSERT statement

From: Ram Raman <veeeraman_at_gmail.com>
Date: Tue, 21 Nov 2006 22:28:47 -0800
Message-ID: <effc058d0611212228n11c5f288ocf3d6c7959b7475c@mail.gmail.com>


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

Original text of this message

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