Re: buffer busy waits PUZZLES
- From: K Gopalakrishnan <kaygopal@xxxxxxxxx>
- To: The Human Fly <sjaffarhussain@xxxxxxxxx>
- Date: Tue, 10 May 2005 15:42:33 +0200
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/
--
//www.freelists.org/webpage/oracle-l
Other related posts:
