RE: enqueue waits for an INSERT statement

Stop!  

Don't bother with INITRANS/MAXTRANS. An INSERT statement will NOT wait on ITL. It will just go to another block on the freelist. If you have ITL shortage on one of the indexes on the table, that will cause an index block split, but again, will NOT wait on ITL.  

First, determine what type of enqueue is being waited on, and in what mode. Steve Adams has an excellent script for this, shows blockers and waiters.
See:
http://www.ixora.com.au/scripts/sql/enqueue_locks.sql  

Once you know the enqueue type (in this case, it's almost certainly TX) and what mode is being waited on (in this case, probably 'S' mode), you can determine the true root cause.  

Likely cause of enqueue waits on INSERT are:

• bitmap index (is there a bitmap index on the table being inserted into?)
• PK/UK constraint enforcement on a heap table
• PK constraint violation on an IOT

Hope that helps,  

-Mark  

--

Mark J. Bobak

Senior Oracle Architect

ProQuest Information & Learning

There is nothing so useless as doing efficiently that which shouldn't be
done at all.  -Peter F. Drucker, 1909-2005

 

________________________________

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sam Chakkanat
Sent: Wednesday, November 22, 2006 9:36 AM
To: veeeraman_at_gmail.com
Cc: 'oracle-l'
Subject: RE: enqueue waits for an INSERT statement


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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] 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_at_cox.net> 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_at_freelists.org [mailto:
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