| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: buffer busy waits
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 CAUSEDWAITS
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 067452
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of John Kanagaraj
Sent: Monday, August 01, 2005 12:50 PM
To: 'kaygopal_at_gmail.com'; Brandon.Allen_at_oneneck.com
Cc: oracle-l_at_freelists.org
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of K Gopalakrishnan
Sent: Friday, July 29, 2005 3:57 PM
To: Brandon.Allen_at_oneneck.com
Cc: oracle-l_at_freelists.org
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/
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Aug 01 2005 - 13:18:50 CDT
 |
 |