Re: enqueue waits for an INSERT statement
- From: "Syed Jaffar Hussain" <sjaffarhussain@xxxxxxxxx>
- To: veeeraman@xxxxxxxxx
- Date: Wed, 22 Nov 2006 10:02:35 +0300
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."
--
//www.freelists.org/webpage/oracle-l
Other related posts:
