RE: enqueue waits for an INSERT statement
- From: "Sam Chakkanat" <cvsam@xxxxxxx>
- To: <veeeraman@xxxxxxxxx>
- Date: Wed, 22 Nov 2006 06:35:52 -0800
Try increasing INITRANS 10, PCTFREE 60 both table and index.
Also, what is the NEXT extent size? Ideally this should be uniform.
If this is a large table > 100 columns? Then guestimate the avg Colum size
and set the NEXT extent size to 50% more than col size * num of rows
inserting.
Now, the other side. Dedicate a BIG roll back segment to this transaction.
You may want to export and re-create the table/indexes to change the
INITRANS.
finally, TEST , TEST, TEST....couple of small runs with SQL Timing on would
guide you
if the change is working.
Hope this helps.
Thank You,
Sam Chakkanat
(949)-394-3355
America's C-Bench
http://www.cbenchusa.com <http://www.cbenchusa.com/>
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Ram Raman
Sent: Wednesday, November 22, 2006 6:14 AM
To: Sam Chakkanat
Cc: oracle-l
Subject: Re: enqueue waits for an INSERT statement
Inittrans for the big table is 1. Max trans 255.
The big table has 3 indexes. The inseret is expected to be an ongoing
process. We want to tune it to run faster. PCT_FREE and PCT_USED: 10 and 40.
THanks.
On 11/22/06, Sam Chakkanat <cvsam@xxxxxxx> wrote:
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@xxxxxxxxxxxxx [mailto:
<mailto:oracle-l-bounce@xxxxxxxxxxxxx> oracle-l-bounce@xxxxxxxxxxxxx] 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.
Other related posts:
