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: Darrell Landrum <darrell_at_landrum.com>
Date: Sat, 28 Aug 2004 11:03:06 -0500
Message-ID: <00af01c48d18$819120c0$6401a8c0@otherrealm>


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.

> 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_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 Sat Aug 28 2004 - 10:58:46 CDT

Original text of this message

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