Re: buffer hit cache ratio

From: Brian Peasland <dba_at_remove_spam.peasland.com>
Date: Tue, 23 Dec 2003 19:08:11 GMT
Message-ID: <3FE8929B.19205473@remove_spam.peasland.com>

Geomancer wrote:
>
> > Tuning by the BCHR is not done by today's top Oracle professionals.
>
> Depends on who you consider a "top" professional.
>
> Tom Kyte and Cary Millsap both argue that the BHR is not a panacea,
> but is IS useful for detecting when the data buffers are way too
> small.

I'm not sure that anyone can successfully argue anything based on the BCHR. Can you point to quotes from Kyte and Millsap (as for Millsap, get a copy of his latest book), in the current century, that indicate that they use the BCHR to detect when the buffer cache is sized too small (or too large)? Similar to Connor's SQL script to falsely inflate the BCHR, one could easily create a script which falsely deflates the BCHR. Just run the following:

set heading off
set pagesize 0
spool deflate_bchr.sql
select 'SELECT * FROM '||owner||'.'||table_name||';' from dba_tables; spool off

@deflate_bchr.sql
@deflate_bchr.sql
@deflate_bchr.sql

{and repeat as needed}

If you have any decent sized database, this will surely deflate the BCHR. The whole point of these exercises is to show that some poorly formed SQL statements issued to the database can have increasing and decreasing affects on the BCHR. SQL statements that unnecessarily cause full table scans can lower the BCHR. But there are SQL statements that *necessarily* cause full table scans. While this may lower the BCHR, it may be more optimal for that SQL statement and for the overall database performance! Similar arguments can be posed for those SQL statements that induce a high number of logical reads (like Connor's script) and increase the BCHR, both good and bad. With all of these "good" and "bad" SQL statements having an influence on the BCHR, how can it accurately reflect the health of the buffer cache as it relates database performance?  

Cheers,
Brian

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"

Received on Tue Dec 23 2003 - 13:08:11 CST