RE: buffer busy waits
- From: John Kanagaraj <john.kanagaraj@xxxxxxx>
- To: "'kaygopal@xxxxxxxxx'" <kaygopal@xxxxxxxxx>, Brandon.Allen@xxxxxxxxxxx
- Date: Mon, 1 Aug 2005 10:49:55 -0700
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
Other related posts:
