Re: buffer busy waits PUZZLES

From: K Gopalakrishnan <kaygopal_at_gmail.com>
Date: Tue, 10 May 2005 15:42:33 +0200
Message-ID: <3b0f44a1050510064269932cb5@mail.gmail.com>

Jaffar:

You are waiting on the 'buffer busy waits' WAIT, not 'buffer busy' WAIT. That is the good news.

But you should not just increase the freelists by looking V$SEGMENT_STATS. What happes if there is a contention for some set of blocks (not for the segement header blocks) like login tables or master tables. Increasing freelists will not help in this case. You need to tweak the block level storage parameters (like PCTFREE, MINIMIZE NUMBER OF ROWS PER BLOCK) etc.

To start with just attach 10046^8 for couple of sessions and look in to the p3 values. If you still want to dig deeper, there is a detailed discussion abot BBW in OWI, Chapter 6.

Or run the follwoing query (from chapter 8) , and this will tell you the list of places (system calls) causing buffer busy waits.

select wh.kcbwhdes "module",       sw.why0 "calls",       sw.why2
"waits",       sw.other_wait "caused waits"from   x$kcbwh wh,     =20

x$kcbsw swwhere wh.indx =3D sw.indx and sw.other_wait > 0order by sw.other_wait;

--=20
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 Tue May 10 2005 - 09:47:15 CDT