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 documentation is here:
http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
To unsubscribe: send a blank email to oracledba-[Email Address Removed] subscribe: send a blank email to oracledba-[Email Address Removed] the list archive: http://www.LAZYDBA.com/odbareadmail.pl
Tell yer mates about http://www.farAwayJobs.com
By using this list you agree to these terms:
http://www.lazydba.com/legal.html
Oracle LazyDBA home page