Re: enqueue waits for an INSERT statement

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_at_gmail.com> 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_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