I agree :)

Thanks.

- Kirti Deshpande
Verizon Information Services
http://www.superpages.com

> -----Original Message-----
> From: [Email Address Removed] [SMTP:[Email Address Removed] Sent: Tuesday, July 31, 2001 10:49 AM
> To: LazyDBA.com Discussion
> Subject: Re: my buffer cache hit ratio is 63.63? any recommendation?
>
>
> A buffer cache hit ratio of 63% is not all bad. In fact, I would be
> worried
> if the ratio was always at 99%. Here is an excerpt from Oracle Performance
> Tuning 101:
>
> Myth:
> "A cache hit ratio in the database buffer cache of 99% or more means that
> the database is performing at it's peak levels."
>
> Fact:
> "A very hit cache-hit ratio in the database buffer cache can be
> misleading.
> Frequently executed sql statements that perform full table scans of the
> same small table or correlated subqueries (that read the same set of
> blocks
> over and over) can elevate the CHR to artificialy high levels. This can
> make you beleive that Oracle is working at peak efficiency when trouble is
> brewing. From the users' perspective, if they are waiting for blocks to be
> read from disk, waiting for free buffers, or waiting on the LRU chain in
> the database buffer cache, it doesn't matter if the CHR is 99% - you
> should
> recognize that you have a performance problem on your hands. We will go so
> far as to say that on most "real systems" a high CHR usually indicates
> exremely inefficient sql in the applications. You need to troubleshoot and
> tune those offending sql statements to get acceptable responce times"
>
> So, before you go re-sizing your SGA parameters, I would look for bad SQL
> statements, and check out any batch jobs that may be running that are
> requesting new blocks from the datafiles. That may be causing a lower hit
> ratio. There are also some great papers out there about this topic, but
> the
> best is "Why a 99% database Buffer Cache Hit Ratio in not OK" by Cary
> Millsap. You can get it at www.hotsos.com
>
>
>
>
> "Khan, Majid"
>
> <majid.[Email Address Removed] To: "LazyDBA.com Discussion"
> <[Email Address Removed]
> s.com> cc:
>
> Subject: my buffer cache hit
> ratio is 63.63? any recommendation?
> 07/31/2001
>
> 08:42 AM
>
>
>
>
>
>
>
>
>
> hi dba's
> my buffer hit ratio is 63.63 but my user hit ratio is about 99%
> I need to tune my database is there any recommendation from you guys
>
>
>
> SELECT Sum(DECODE(name, 'consistent gets', value, 0)) AS
> "consistent_gets",
> Sum(DECODE(name, 'db block gets', value, 0)) AS "db_block_gets",
> Sum(DECODE(name, 'physical reads', value, 0)) AS "physical_reads",
> TO_CHAR(
> (
> Sum(DECODE(name, 'consistent gets', value, 0))
> + Sum(DECODE(name, 'db block gets', value, 0))
> - Sum(DECODE(name, 'physical reads', value, 0))
> )
> /
> (
> Sum(DECODE(name, 'consistent gets', value, 0))
> + Sum(DECODE(name, 'db block gets', value, 0))
> )
> * 100, '99999999.99' ) AS "hit_ratio, %"
> FROM v$sysstat;
>
> --------------------------------------------------------------------------
>
> consistent_gets db_block_gets physical_reads hit_ratio, %
> --------------- ------------- -------------- ------------
> 15744863 783286 6011121 63.63
>
> --------
>
Oracle LazyDBA home page