Re: buffer busy wait for insert session

From: K Gopalakrishnan <kaygopal_at_gmail.com>
Date: Sat, 21 May 2005 02:30:04 +0530
Message-ID: <3b0f44a105052014005c603411@mail.gmail.com>

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_at_gmail.com> 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_at_yahoo.com> 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

Received on Fri May 20 2005 - 17:04:46 CDT