Re: enqueue waits for an INSERT statement

  • From: "Ram Raman" <veeeraman@xxxxxxxxx>
  • To: "Syed Jaffar Hussain" <sjaffarhussain@xxxxxxxxx>
  • Date: Wed, 22 Nov 2006 06:07:59 -0800

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

Other related posts:

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