Re: So how big is your buffer cache ?
- From: "Darrell Landrum" <darrell@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 28 Aug 2004 11:03:06 -0500
I've always tried to refrain from bashing others publicly on this list, but
I must share a bit of an experience in this area.
Last June, we had someone from Burleson Consulting on site to teach an
Oracle tuning class and there were several myths that were still being
heavily propagated:
1) Bigger buffer cache is the answer to most query performance problems.
(This is not their words verbatim, but my description of the concept being
taught.)
2) Never (yes, he used the word never) index small tables. (This one was
particularly great because he used a lookup table of U.S. states as an
example and I used his exact scenario to show that an index on that table
made the workload less and the example queries faster.)
3) For partitioned tables, global indexes are better. (Not in a million
years would I adopt this. If for no other reason, than ease of
administration, if you can't show definitive, repeatable performance results
from a global index, then local should be your standard.)
4) From direct and indirect statements, our folks left class on some days
feeling like we had to rid our world of all full table scans immediately.
(We on this list know this is not a valid tuning goal, especially by
itself.)
That being said, we have 2 data warehouse type databases (on hp-ux 64 bit),
one at 300 GB and another around 700 GB. Both of these have several large
tables that are in the 75 to 100 GB range per table (these are partitioned).
Some user reports gather data from short, recent date ranges, but others
span the last 3 years. Each of these databases has just less than a 2 GB
buffer cache. Most performance problems we encounter are due to bad sql or
good sql that is getting a bad plan. These, respectively, are normally
fixed by rewriting the sql and by analyzing one or more tables.
I've never seen a buffer cache problem or more importantly a problem get
corrected by a larger buffer cache. I'm sure these cases arise, but it can
be proven in advance, that buffer cache is the bottleneck and more memory is
what is needed.
Just be leary of the those who throw more hardware at a logic problem.
----- Original Message -----
From: "Richard Foote" <richard.foote@xxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Saturday, August 28, 2004 6:56 AM
Subject: So how big is your buffer cache ?
> Hi All,
>
> In an interesting insight into how Don Burleson performs tuning at the
> c.d.o.s newsgroup
>
(http://groups.google.com/groups?dq=&hl=en&lr=&ie=UTF-8&th=73f606eef5e7e99f)
> . Don suggests he has "no problem throwing hardware at crappy code when
the
> client doesn't want to tune it". He's also basically recommending using
AWE
> and utilising all available RAM on 32bit windows, whether you need to or
> not. I mean, AWE has no disadvantages right ... :)
----------------------------------------------------------------
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
-----------------------------------------------------------------
Other related posts:
