RE: enqueue waits for an INSERT statement

From: Sam Chakkanat <cvsam_at_cox.net>
Date: Wed, 22 Nov 2006 05:58:26 -0800
Message-ID: <002a01c70e3e$486fdbc0$6d01a8c0@IBM839YQP>

Ram,

Please check the intitrans for the big table. Also, does the big table has indexes?
Is this process is only one time or you expect to have this insert ongoing? One of my client, I have done increasing the inittrans of table and associated indexes,
the NEXT segment space and the PCTFREE parameter. If this is a batch inserts
and could manageable, then you could disable all associated indexes and try.

Sam

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ram Raman
Sent: Tuesday, November 21, 2006 10:29 PM To: oracle-l
Subject: 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 - 07:58:26 CST