try to find out the resource being competeted for...the resource id you can find in v$lock.id1 column for TM lock, but also this info is available in v$session.row_wait_obj# for the waiting session. On 11/22/06, Ram Raman <veeeraman@xxxxxxxxx> wrote:
Thanks everyone who answered. I had issued a similar statement from another session on the table before I kicked off this SQL statement. I had not issued a commit on the older session, which was holding the lock on the table. Once I committed it, the newer SQL statement started working and enqueue waits are gone. I will have to see how the statement actually performs now that it has started executing. Sam, I will try to implement your advise too. On 11/22/06, Sam Chakkanat <cvsam@xxxxxxx> wrote: > > 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 > > > ------------------------------ > *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: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. > > > > > > > > > >