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_at_gmail.com> 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