Re: buffer busy wait for insert session

From: zhu chao <zhuchao_at_gmail.com>
Date: Fri, 20 May 2005 09:55:53 +0800
Message-ID: <962cf44b05051918553b5534b3@mail.gmail.com>

Hi, Gopal,
The result of your SQL is like:

module                                   ADDR                   INDX =20
 INST_ID       WHY0       WHY1       WHY2 OTHER WAIT
---------------------------------------- ---------------- ----------
---------- ---------- ---------- ---------- ----------
ktswh85: ktsfblink                       0000000FDC2482D0         93 =20
       1     844661          0          0        303
kduwh02: kdusru                          0000000FDC249C90        299 =20
       1    7403209          0          0        351
kduwh01: kdusru                          0000000FDC249C70        298 =20
       1  160094785          0          0        369
ktswh14: ktsnbk                          0000000FDC247B30         32 =20
       1     846449          0          0        441
kdiwh127: kdislink                       0000000FDC24A7D0        389 =20
       1    6512919          0          0        584
kdiwh133: kdisdelete                     0000000FDC24A890        395 =20
       1    3919793          0          0        649
kdiwh07: kdifbk                          0000000FDC249EF0        318 =20
       1 1.2173E+11          0          0        798
kdowh00: kdoiur                          0000000FDC249A70        282 =20
       1    8076703          0          0       1228
ktswh37: ktsufl                          0000000FDC247D70         50 =20
       1      15550          0          0       1233
kdiwh23: kdiins                          0000000FDC24A0F0        334 =20
       1    4915161          0          0       2772
kdswh02: kdsgrp                          0000000FDC249AD0        285 =20
       1 3.9589E+10          0          0       2939

module                                   ADDR                   INDX =20
 INST_ID       WHY0       WHY1       WHY2 OTHER WAIT
---------------------------------------- ---------------- ----------
---------- ---------- ---------- ---------- ----------
ktuwh03: ktugnb                          0000000FDC248AF0        158 =20
       1  251757436          0          0       3749
ktbwh00: ktbgtl                          0000000FDC247970         18 =20
       1     671746          0          0       4984
kdiwh126: kdisparent                     0000000FDC24A7B0        388 =20
       1          0    6689768      29850       5985
kcbwh1: kcbchg1                          0000000FDC247770          2 =20
       1  596397933          0          0      11275
kdiwh161: kdifind                        0000000FDC24AA30        408 =20
       1    6214341          0          0      21099
ktswh34: ktsgsp                          0000000FDC247D30         48 =20
       1      70742          0          0      59984
kdiwh130: kdisle                         0000000FDC24A830        392 =20
       1    6689771          0          0      72246
kdtwh01: kdtgrs                          0000000FDC249B90        291 =20
       1  545508036          0          0      99979
ktswh72: ktsbget                         0000000FDC248130         80 =20
       1  281638372          0          0     121225
ktuwh01: ktugus                          0000000FDC248AB0        156 =20
       1  848098452          0          0     191407
ktuwh02: ktugus                          0000000FDC248AD0        157 =20
       1  652447066          0          0     587564

module                                   ADDR                   INDX =20
 INST_ID       WHY0       WHY1       WHY2 OTHER WAIT
---------------------------------------- ---------------- ----------
---------- ---------- ---------- ---------- ----------
ktswh39: ktsrsp                          0000000FDC247DB0         52 =20
       1  216480406          0          0     923447
kdiwh22: kdifind                         0000000FDC24A0D0        333 =20
       1 1.0040E+10          0          0    1525588
kdswh05: kdsgrp                          0000000FDC249B30        288 =20
       1 2.0280E+10          0          0   16670027
ktswh28: ktsgsp                          0000000FDC247C70         42 =20
       1  281634373          0          0   20941585
ktswh07: ktsmfl                          0000000FDC247A70         26 =20
       1  128646167          0          0   64707802
ktswh06: ktsmfl                          0000000FDC247A50         25 =20
       1  128488810          0          0   70708363
ktswh05: ktsmfl                          0000000FDC247A30         24 =20
       1  637034019          0          0  467257374

458 rows selected.

Elapsed: 00:00:00.28
18:48:40 SQL> l
  1 select wh.kcbwhdes "module",
  2 sw.*
  3 from x$kcbwh wh,
  4 x$kcbsw sw
  5 where wh.indx =3D sw.indx
  6 --and sw."OTHER WAITS" > 0
  7* order by 8

The result is difficult to understand. Can you interprte it? Thanks

On 5/20/05, K Gopalakrishnan <kaygopal_at_yahoo.com> wrote:
> Zhu:

>=20

> Is that table partitioned? I would go for HASH partitioning as you need
> to throw the incoming rows in to multiple blocks and this will reduce
> the contention for 'concurrnet write' BBW.

>=20

> Can you run the following query and identify which functions cause the
> buffer busy waits?

>=20

> select wh.kcbwhdes "module",
> sw.why0 "calls",
> sw.why2 "waits",
> sw.other_wait "caused waits"
> from x$kcbwh wh,
> x$kcbsw sw
> where wh.indx =3D sw.indx
> and sw.other_wait > 0
> order by sw.other_wait;

>=20
>=20
>=20

> Have a nice day !!
> ------------------------------------------------------------
> Best Regards,
> K Gopalakrishnan,
> Co-Author: Oracle Wait Interface: Oracle Press 2004.
> http://www.amazon.com/exec/obidos/tg/detail/-/007222729X/
>=20

--=20
Regards
Zhu Chao
www.cnoug.org

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

Received on Thu May 19 2005 - 22:23:26 CDT