Re: enqueue waits for an INSERT statement

  • From: "Alberto Dell'Era" <alberto.dellera@xxxxxxxxx>
  • To: veeeraman@xxxxxxxxx
  • Date: Wed, 22 Nov 2006 15:17:50 +0100

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@xxxxxxxxx> 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@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.
>
>
>





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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 11-2006