But, whoa, wait! Seeing 'free buffer waits' waits in your session is an indication that your session is waiting for DBWR to do its job. How does increasing the database buffer cache size help the DBWR do its job more efficiently?! It doesn't. What increasing the db buffer cache size /can/ do in this situation is = mask the symptom, giving some temporary relief to the DBWR /if/ you can make = the buffer cache so big that the transactions modifying blocks can't fill = all the buffers that are available. But in the presence of a big enough transaction rate sustained over a long enough period of time, you'll = never win. The "make your db buffer cache bigger" solution in this case is = exactly analogous to the proposition about giving your RAID level 5 array a big enough cache. The efficient solution is often (read "almost always")--would you = believe it?--SQL optimization. It's common, for example, to find bad SQL in the = form of UPDATE statements that update rows to values they already have. Stuff like this: update t set a=3D'X' where b between :v1 and :v2 /* without anything specifying "and a!=3D'X'" */ Of course, things like this directly generate workload for DBWR (and = LGWR, and ARCH, ...) that has absolutely zero value to your business. But even = a bad SELECT statement can have the same effect. A SQL statement that = reads 10,000x as many PIO blocks as it needs, executed thousands of times per = day, results in a much busier I/O subsystem than you need. A busy I/O = subsystem makes it more difficult for DBWR to keep up, and this causes 'free = buffer waits' waits. I won't express my opinion on the complete CF that is CDOS, but I would estimate confidently that of all performance problems I've /ever/ seen = since 1989, over 95% can be fixed most efficiently by modifying either the SQL = or the application code that calls the SQL. The idea that the vast majority = of performance problems can have their situation improved by the = manipulation of Oracle parameters or hardware horsepower is /completely/ alien within = the bounds of my experience. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com * Nullius in verba * Upcoming events: - Performance Diagnosis 101: 9/14 San Francisco, 10/5 Charlotte, 10/26 Toronto - SQL Optimization 101: 8/16 Minneapolis, 9/20 Hartford, 10/18 New = Orleans - Hotsos Symposium 2005: March 6-10 Dallas - Visit www.hotsos.com for schedule details... -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx = [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Gogala, Mladen Sent: Monday, August 30, 2004 8:53 AM To: 'oracle-l@xxxxxxxxxxxxx' Subject: RE: So how big is your buffer cache ? > -----Original Message----- > From: April Wells [mailto:AWells@xxxxxxxxxx]=20 > Sent: Monday, August 30, 2004 8:23 AM > To: 'oracle-l@xxxxxxxxxxxxx' > Subject: RE: So how big is your buffer cache ? >=20 >=20 > But really people... >=20 > What does it matter in the long run... size doesn't matter =20 > anyway, right? That's the first time I see a lady say that. I thought it was a consolation of us, middle-aged, chubby DBA males. Joking aside, the buffer cache should be increased using the same principle as for everything else: you increase the buffer size when you start seeing free buffer waits in your V$SYSTEM_EVENT. The basic principle=20 of the event-based tuning methodology is to try fixing that what=20 sessions are actually waiting for, instead of computing BCHR,=20 horoscope or your personal lucky number. -- Mladen Gogala A & E TV Network Ext. 1216 ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------