Re: buffer busy wait for insert session

Zhu:

How many freelists you have defined for the segment? Most of the waits
are coming from Freelist merge operations and get/record free space
operations. If I am right, you have defined default freelists or less
freelists for that segment.

You can counter this in two ways,=20
Increase the PCTFREE for those segments and lowering PCTUSED so that
blocks are rarely come back to the freelist.
Increase the freelists/groups so that the additional freelist blocks
reduces the header contention
Convert the segment to ASSM, where the BMBs manage the link/unlink
(there is no such thing in ASSM btw) operations, thus reducing the
contention for header blocks.

Let me know offline how that goes.

Regards,
Gopal


On 5/20/05, zhu chao <zhuchao@xxxxxxxxx> wrote:
> Hi, Gopal,
>  The result of your SQL is like:
> module                                   ADDR                   INDX =3D2=
0
>  INST_ID       WHY0       WHY1       WHY2 OTHER WAIT
> ---------------------------------------- ---------------- ----------
> ---------- ---------- ---------- ---------- ----------
> ktswh85: ktsfblink                       0000000FDC2482D0         93 =3D2=
0
>       1     844661          0          0        303
> kduwh02: kdusru                          0000000FDC249C90        299 =3D2=
0
>       1    7403209          0          0        351
> kduwh01: kdusru                          0000000FDC249C70        298 =3D2=
0
>       1  160094785          0          0        369
> ktswh14: ktsnbk                          0000000FDC247B30         32 =3D2=
0
>       1     846449          0          0        441
> kdiwh127: kdislink                       0000000FDC24A7D0        389 =3D2=
0
>       1    6512919          0          0        584
> kdiwh133: kdisdelete                     0000000FDC24A890        395 =3D2=
0
>       1    3919793          0          0        649
> kdiwh07: kdifbk                          0000000FDC249EF0        318 =3D2=
0
>       1 1.2173E+11          0          0        798
> kdowh00: kdoiur                          0000000FDC249A70        282 =3D2=
0
>       1    8076703          0          0       1228
> ktswh37: ktsufl                          0000000FDC247D70         50 =3D2=
0
>       1      15550          0          0       1233
> kdiwh23: kdiins                          0000000FDC24A0F0        334 =3D2=
0
>       1    4915161          0          0       2772
> kdswh02: kdsgrp                          0000000FDC249AD0        285 =3D2=
0
>       1 3.9589E+10          0          0       2939
>=20
> module                                   ADDR                   INDX =3D2=
0
>  INST_ID       WHY0       WHY1       WHY2 OTHER WAIT
> ---------------------------------------- ---------------- ----------
> ---------- ---------- ---------- ---------- ----------
> ktuwh03: ktugnb                          0000000FDC248AF0        158 =3D2=
0
>       1  251757436          0          0       3749
> ktbwh00: ktbgtl                          0000000FDC247970         18 =3D2=
0
>       1     671746          0          0       4984
> kdiwh126: kdisparent                     0000000FDC24A7B0        388 =3D2=
0
>       1          0    6689768      29850       5985
> kcbwh1: kcbchg1                          0000000FDC247770          2 =3D2=
0
>       1  596397933          0          0      11275
> kdiwh161: kdifind                        0000000FDC24AA30        408 =3D2=
0
>       1    6214341          0          0      21099
> ktswh34: ktsgsp                          0000000FDC247D30         48 =3D2=
0
>       1      70742          0          0      59984
> kdiwh130: kdisle                         0000000FDC24A830        392 =3D2=
0
>       1    6689771          0          0      72246
> kdtwh01: kdtgrs                          0000000FDC249B90        291 =3D2=
0
>       1  545508036          0          0      99979
> ktswh72: ktsbget                         0000000FDC248130         80 =3D2=
0
>       1  281638372          0          0     121225
> ktuwh01: ktugus                          0000000FDC248AB0        156 =3D2=
0
>       1  848098452          0          0     191407
> ktuwh02: ktugus                          0000000FDC248AD0        157 =3D2=
0
>       1  652447066          0          0     587564
>=20
> module                                   ADDR                   INDX =3D2=
0
>  INST_ID       WHY0       WHY1       WHY2 OTHER WAIT
> ---------------------------------------- ---------------- ----------
> ---------- ---------- ---------- ---------- ----------
> ktswh39: ktsrsp                          0000000FDC247DB0         52 =3D2=
0
>       1  216480406          0          0     923447
> kdiwh22: kdifind                         0000000FDC24A0D0        333 =3D2=
0
>       1 1.0040E+10          0          0    1525588
> kdswh05: kdsgrp                          0000000FDC249B30        288 =3D2=
0
>       1 2.0280E+10          0          0   16670027
> ktswh28: ktsgsp                          0000000FDC247C70         42 =3D2=
0
>       1  281634373          0          0   20941585
> ktswh07: ktsmfl                          0000000FDC247A70         26 =3D2=
0
>       1  128646167          0          0   64707802
> ktswh06: ktsmfl                          0000000FDC247A50         25 =3D2=
0
>       1  128488810          0          0   70708363
> ktswh05: ktsmfl                          0000000FDC247A30         24 =3D2=
0
>       1  637034019          0          0  467257374
>=20
> 458 rows selected.
>=20
> 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 =3D3D sw.indx
>  6  --and sw."OTHER WAITS" > 0
>  7* order by 8
>=20
> The result is difficult to understand. Can you interprte it?
> Thanks
>=20
> On 5/20/05, K Gopalakrishnan <kaygopal@xxxxxxxxx> wrote:
> > Zhu:
> >=3D20
> > 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.
> >=3D20
> > Can you run the following query and identify which functions cause the
> > buffer busy waits?
> >=3D20
> > 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 =3D3D sw.indx
> > and sw.other_wait > 0
> > order by sw.other_wait;
> >=3D20
> >=3D20
> >=3D20
> > Have a nice day !!
> > ------------------------------------------------------------


Best Regards,
K Gopalakrishnan=20
Co-Author: Oracle Wait Interface, Oracle Press 2004
http://www.amazon.com/exec/obidos/tg/detail/-/007222729X/
--
http://www.freelists.org/webpage/oracle-l

Other related posts: