RE: buffer busy waits
- From: "Bellows, Bambi" <bbellows@xxxxxxx>
- To: <john.kanagaraj@xxxxxxx>, <kaygopal@xxxxxxxxx>, <Brandon.Allen@xxxxxxxxxxx>
- Date: Mon, 1 Aug 2005 13:16:34 -0500
Thanks for the nifty scripts! You guys are really on top of your games
with the x$ calls. But... Is there some kind of doc that tells you what
all the modules are? Cuz some of this is pretty obvious, but then
there's the rest.
TIA,
Bambi.
============================
MODULE CALLS WAITS CAUSED
WAITS
---------------------------------------- ---------- ----------
------------
ktswh27: ktsgsp 155 0
1
ktswh28: ktsgsp 34300 0
1
ktuwh27: kturbk 748334 0
1
ktswh32: ktsgsp 86327 0
1
ktewh78: kteopdelete 218 0
1
kdiwh18: kdifind 17189 0
1
kdiwh126: kdisparent 0 49
1
kdiwh131: kdisdelete 3633 0
1
kdiwh32: kdiuge 405 0
1
kdiwh23: kdiins 8464 0
2
kdiwh161: kdifind 8624 0
3
ktswh30: ktsgsp 43 0
6
kdcwh08: kdcrmk 20 0
6
ktswh102: ktsbvini 187487 0
7
kdcwh09: kdcrmk 147 0
7
ktswh100: ktsbgsp 97926 0
10
ktswh125: ktsbvmap 1278 0
10
ktswh108: ktsbbsrch 97762 0
11
ktfbwh0d: ktfbsearch 741 0
11
ktswh06: ktsmfl 1937 0
12
kdcwh10: kdcrmk 147 0
12
kdiwh130: kdisle 9682 0
21
kdswh06: kdscgr 876654 0
29
ktuwh02: ktugus 781702 0
34
ktuwh01: ktugus 1049129 0
37
ktswh05: ktsmfl 5804 0
38
kdcwh07: kdcifk 140 0
38
ktewh25: kteinicnt 3719885 0
40
kdiwh06: kdifbk 11752489 0
69
kdiwh08: kdiixs 11709858 0
72
kdiwh46: kdidgb 5008 0
85
kdtwh01: kdtgrs 425889 0
86
kdiwh42: kdiixs 10803900 0
91
ktswh72: ktsbget 120513 0
104
kdiwh17: kdifind 1847539 0
118
kdiwh22: kdifind 3979892 0
335
kdiwh07: kdifbk 48003925 0
512
kduwh01: kdusru 764612 0
594
kdiwh09: kdiixs 184931832 0
1455
kdswh02: kdsgrp 44737633 0
3401
kddwh03: kddlkr 811614 0
3771
kdiwh15: kdifxs 5247486 0
8863
kdswh01: kdstgr 42103706 0
32007
kdswh05: kdsgrp 144822545 0
67452
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of John Kanagaraj
Sent: Monday, August 01, 2005 12:50 PM
To: 'kaygopal@xxxxxxxxx'; Brandon.Allen@xxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: buffer busy waits
Aaaand, for those of you still at Oracle 8i, the "OTHER_WAIT" column in
9i/10g has a space in 8i, so the correct SQL for *8i* databases is:
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 = SW.indx
AND SW."OTHER WAIT" > 0 ORDER BY SW."OTHER WAIT"
Sorry KG - Couldn't resist :( [See my notes in the OWI book AR]
John
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of K Gopalakrishnan
Sent: Friday, July 29, 2005 3:57 PM
To: Brandon.Allen@xxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: buffer busy waits
Allen:
You can findout what are the _exact_ system calls causing the buffer
busy waits using the following SQL. This will give a fair idea about
the operations causing the bbw and you can narrow down the cause/action
from the result.
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 = SW.indx
AND SW.OTHER_WAIT > 0
ORDER BY SW.OTHER_WAIT;
Basically by reducing the concurrency at block level, you can easily
combat the BBWs.
--
Best Regards,
K Gopalakrishnan
Co-Author: Oracle Wait Interface, Oracle Press 2004
http://www.amazon.com/exec/obidos/tg/detail/-/007222729X/
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
