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: Buffer Hit ratios - Oracle still hasn't got it

Re: Buffer Hit ratios - Oracle still hasn't got it

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Thu, 26 Apr 2007 12:24:22 +0100
Message-ID: <7765c8970704260424s1be6b096s2ec285c56349b1ff@mail.gmail.com>


I don't really have too much trouble with that snippet to be honest. Especially given the context that it is in, the quote below being the immediately preceding text

> There are many factors to examine before considering whether to increase
> or decrease the buffer cache size. For example, you should examine
> V$DB_CACHE_ADVICE data and the buffer cache hit ratio.
>
> A low cache hit ratio does not imply that increasing the size of the cache
> would be beneficial for performance. A good cache hit ratio could wrongly
> indicate that the cache is adequately sized for the workload.
>
> To interpret the buffer cache hit ratio, you should consider the
> following:
>
> -
>
> Repeated scanning of the same large table or index can artificially
> inflate a poor cache hit ratio. Examine frequently executed SQL statements
> with a large number of buffer gets, to ensure that the execution plan for
> such SQL statements is optimal. If possible, avoid repeated scanning of
> frequently accessed data by performing all of the processing in a single
> pass or by optimizing the SQL statement.
> -
>
> If possible, avoid requerying the same data, by caching frequently
> accessed data in the client program or middle tier.
> -
>
> Oracle blocks accessed during a long full table scan are put on the
> tail end of the least recently used (LRU) list and not on the head of the
> list. Therefore, the blocks are aged out faster than blocks read when
> performing indexed lookups or small table scans. When interpreting the
> buffer cache data, poor hit ratios when valid large full table scans are
> occurring should also be considered.
>
> Note:
> Short table scans are scans performed on tables under a certain size
> threshold. The definition of a small table is the maximum of 2% of the
> buffer cache and 20, whichever is bigger.
>
>
>
> -
>
> In any large database running OLTP applications in any given unit of
> time, most rows are accessed either one or zero times. On this basis, there
> might be little purpose in keeping the block in memory for very long
> following its use.
> -
>
> A common mistake is to continue increasing the buffer cache size.
> Such increases have no effect if you are doing full table scans or
> operations that do not use the buffer cache.
>
>

If you do have a "few gets/per execution" style app then a low cache hit ratio *is* someting to be *investigated*. This is a world away from the sort of advice that was around in the later 90s along the lines of 'if hit ratio X is < 95% (or 99 or whatever)' then increase the buffer cache until you exceed this target figure. I think there's a real danger now in the Oracle world of *over* reacting to the hit ratio target myths and of saying that there are no circumstances under which we should care whether data is found in memory or has to come from disk - I realize you didn't say that, I'm just hijacking the thread a bit :( .

On 4/26/07, Peter McLarty <p.mclarty_at_cqu.edu.au> wrote:
>
> I was just reading the 10.2 Performance Tuning Guide and found this little
> gem. I thought it should have been written out by now
>
> "7.2.3.1 Increasing Memory Allocated to the Buffer Cache
>
> As a general rule, investigate increasing the size of the cache if the
> cache hit ratio is low and your application has been tuned to avoid
> performing full table scans."
>
> Clearly no one in Oracle has updated the Tech Writers for some time
>
> Cheers
>
> --
> Peter McLarty
> Database Administrator
> Student System Upgrade Project
> Central Queensland University
>
> Email: p.mclarty_at_cqu.edu.au
> Phone: 07 4923 2876
> Fax: 07 4923 2721
> Mobile: 04 0209 4238
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Apr 26 2007 - 06:24:22 CDT

Original text of this message

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