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