Buffer Cache Usage

[default]

I'm wondering if having a very large - 1.5G - buffer cache can effect cpu usage. One of our production servers has 80000 db_block_buffers with a block size 0f 8k. I used the follwoing query to find out the status of the buffers.

select decode(state, 0, 'Free', 1,
decode(lrba_seq,0,'Available','Being Modified'),
2, 'Not Modified',
3, 'Being Read', 'Other') "BLOCK STATUS",
count(*) cntfrom sys.x$bh
group by decode(state, 0, 'Free',1, decode(lrba_seq,0,'Available','Being Modified'),
2, 'Not Modified',3, 'Being Read','Other')

BLOCK STATUS CNT
-------------- ----------
Available 79430
Being Modified 331
Being Read 239

If i reduce the db_block_buffers from 80000 to 60000 will this have an impaceon the performance. Based upon the count from the above it looks like the buffer cache is oversized.

Any help would be greatly appreciated.

Thanks in Advance.

[stmontgo]

read

Are you still using cache hit ratios? — Cary Millsap's follow-up to his “99.9% paper” goes even further to explain why the buffer cache hit ratio is an illegitimate measure of SQL efficiency

at http://www.hotsos.com

[Shestakov]

Originally posted by stmontgo

Are you still using cache hit ratios? ...

Steve
Why we have not to use HR (for cache or not only).
Another question: We should not use HR ONLY.
HR is information about a db too. And with another measures,
such as wait statistics, os statistics and so on.
HR can show more data about perfomance and health of db.
We need use HR in right way.

[TimHall]

If you read the following articles you'll see that hit ratios are not a very good indicator of system performance. In some cases very high hit ratios can indicate poor performance due to endless spins for latches.

You can still use hit ratios if it makes you happy, just make sure you follow them up with some real tests or you may find yourself out of a job.

http://www.oracle-base.com/Links/yapp_anjo_kolk.pdf
http://www.oracle-base.com/Links/oraperf_bw.pdf
http://www.hotsos.com/dnloads/1.Mill...CacheRatio.pdf
http://www.hotsos.com/dnloads/1.Mill....11.14-LIO.pdf

Cheers