Re: enqueue waits for an INSERT statement

From: Alberto Dell'Era <alberto.dellera_at_gmail.com>
Date: Wed, 22 Nov 2006 15:17:50 +0100
Message-ID: <4ef2fbf50611220617o78fb937t1a0b28572eab32e1@mail.gmail.com>

PK constraints or UNIQUE indexes ?

Normally "enqueue" shows up for collision on index entries on insert, eg

SQL> create table t (x int PRIMARY KEY);

Table created.

SQL> insert into t(x) values (10);

1 row created.

ANOTHER SESSION> select sid from v$mystat where rownum=1;

       SID

---

        10

ANOTHER SESSION> insert into t(x) select rownum from dual connect by level <= 11;
(hangs)

SQL> select EVENT from v$session_wait where sid = 10;

EVENT

---

enqueue

On 11/22/06, Ram Raman <veeeraman_at_gmail.com> wrote:
>
> 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.
> >
> >
> >
>
>

-- 
Alberto Dell'Era
"Per aspera ad astra"
--
http://www.freelists.org/webpage/oracle-l

Received on Wed Nov 22 2006 - 08:17:50 CST