Re: enqueue waits for an INSERT statement

From: Ram Raman <veeeraman_at_gmail.com>
Date: Wed, 22 Nov 2006 06:07:59 -0800
Message-ID: <effc058d0611220607t1a0959e0n5f7acd71f91252cc@mail.gmail.com>

What does v$waitstat show? Is it an aggregate view?

Here is the stats from v$Waitstat, from a different session:

CLASS                   COUNT       TIME
------------------ ---------- ----------
data block             490882     684078
sort block                  0          0
save undo block             0          0
segment header             15         19
save undo header            0          0
free list                   0          0
extent map                  2          4
1st level bmb               0          0
2nd level bmb               0          0
3rd level bmb               0          0
bitmap block                0          0

more..

CLASS                   COUNT       TIME
------------------ ---------- ----------
bitmap index block          8          0
file header block        1528       3320
unused                      0          0
system undo header          0          0
system undo block           0          0
undo header              9000      15775
undo block                866         20

Meanwhile TIME_WAITED for enqueue in v$sessoin_wait is still increasing after 10 hours.

On 11/21/06, Syed Jaffar Hussain <sjaffarhussain_at_gmail.com> wrote:
>
> For a quick check, you can use v$waitstat to see the problem of free
> list, undo header, under block or what.
>
> Tell us, do you have any indexes on this table?
>
> On 11/22/06, Ram Raman <veeeraman_at_gmail.com> wrote:
> > 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.
> >
> >
> >
>
>
> --
> Best Regards,
> Syed Jaffar Hussain
> 8i,9i & 10g OCP DBA
>
> I blog at :http://jaffardba.blogspot.com/
>
> http://www.oracle.com/technology/community/oracle_ace/ace1.html#hussain
>
> ----------------------------------------------------------------------------------
> "Winners don't do different things. They do things differently."
>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Nov 22 2006 - 08:07:59 CST