Re: Buffer cache statistics (ratios) and CBO SQL optimization?

On 24 Dec 2003 07:38:47 -0800, pharfromhome_at_hotmail.com (Geomancer) wrote:

>Can some guru please enlighten me?
>
>I have been reading the threads about why buffer hit ratios are
>useless and yet I'm having trouble reconciling this with the Oracle
>documentation and my limited understanding of Oracle tuning.
>
> - Why does the CBO want to know estimates about the index buffer
>hit ratio (optimizer_index_caching parameter)?
>

The optimizer_index_caching parameter doesn't necessarily equate with the BCHR

> - Why did Oracle offer the KEEP pool and the other blocksize buffer
>pools?

The KEEP pool is used only when the table is subject to FTS. It's primary function is to make sure lookup tables are not being aged out the buffer cache immediately
>
> - Why do Oracle experts place such high emphasis on "physical"
>block reads when optimizing SQL?
>

Which Oracle experts do you imply, apart from Rich Niemic and Don Burleson? Oracle experts place emphasis on reducing *logical* IO

>For example, Vadim Tropashko, who works for Real World Performance
>group at Oracle says caching ratios are an important part of CBO
>internals because the CBO's goal is to minimize "physical" reads.

Which is just utterly untrue. The CBO calculates logical IO

>
>http://www.dbazine.com/tropashko3.html
>
>3) Data Caching. With caching a simplistic model where the cost is
>based upon the number of logical IOs is no longer valid: cost model
>adjustment and caching statistics is necessary.

--
Sybrand Bakker, Senior Oracle DBA