Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: So how big is your buffer cache ?

RE: So how big is your buffer cache ?

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Mon, 30 Aug 2004 09:21:16 -0500
Message-ID: <03a101c48e9c$a003c320$6701a8c0@CVMLAP02>


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_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Gogala, Mladen
Sent: Monday, August 30, 2004 8:53 AM
To: 'oracle-l_at_freelists.org'
Subject: RE: So how big is your buffer cache ?

> -----Original Message-----
> From: April Wells [mailto:AWells_at_csedge.com]=20
> Sent: Monday, August 30, 2004 8:23 AM
> To: 'oracle-l_at_freelists.org'
> 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_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://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_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Mon Aug 30 2004 - 09:18:20 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US