Re: enqueue waits for an INSERT statement

For a quick check, you can use v$waitstat to see the problem of free
list, undo header, under block or what.

Tell us, do you have any indexes on this table?

On 11/22/06, Ram Raman <veeeraman@xxxxxxxxx> wrote:

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.






--
Best Regards,
Syed Jaffar Hussain
8i,9i & 10g OCP DBA

I blog at :http://jaffardba.blogspot.com/

http://www.oracle.com/technology/community/oracle_ace/ace1.html#hussain
----------------------------------------------------------------------------------
"Winners don't do different things. They do things differently."
--
http://www.freelists.org/webpage/oracle-l

Other related posts: