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/ -- //www.freelists.org/webpage/oracle-l