Re: enqueue waits for an INSERT statement

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_at_cox.net> 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_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]
> > *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