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

the best place to post this is oracle-l_at_fatcity.com. go to fatcity and check messages from the last few days. Cary Milsap and others have discussed this.

go to www.hotsos.com

read Why You Should Focus on LIOs Instead of PIOs

PIOs are important. You run a query once and the blocks are in memory. Memory is seeded. So you run it again and its faster. HOWEVER, the hit ratio is misleading. The highest cache ratios are their because you have VERY bad SQL that is doing way too much work in memory. This inflates the ratio because you only read the blocks from disk once, but then access it way too many times in memory. Its in the article above.

See my answers inline.

"Geomancer" <pharfromhome_at_hotmail.com> wrote in message news:cf90fb89.0312240738.264cc573_at_posting.google.com...
> 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)?
this has nothing to do with a 'ratio'. See tom kytes new tuning book. This has to do with understanding your application. Can you assume that your index blocks are more likely to be in memory? Nothing to do with a 'ratio'.

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

sybrand explained this well. PIOs are more expensive than LIOs and sometimes its important to keep small tables in memory. Key is 'sometimes'. Has nothing to do with a ratio. Sometimes PIOs are not much more expensive than LIOs and you waste tons of times eliminating them for no gain.

>
> - Why do Oracle experts place such high emphasis on "physical"
> block reads when optimizing SQL?

who? Give names. Niemic took it out of his new book(the 1998 version has it. new one does not). The only book Ive seen BCHR is the new tuning book from Rampant press. The rampant books appear to be fluffed up garbage. Little info in large type to make it look like there is more info in it.

this one? Its trash. Burn it if you have it.

http://www.bookpool.com/.x/67onteh2t0/sm/0972751343

>
> 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.
>
> http://www.dbazine.com/tropashko3.html

I skimmed the article. I do not see anything on ratios. Seems relatively well written. Minimizing physical I/Os does NOT mean improving your hit ratio. Since you generally jack up your ratio by writing bad SQL. Do you realize there are 4 different BHCRs in 9i? I dont remember all the different ones. Jonathan Lewis posted it Oracle-L yesterday.

BHCR could have a small value 'if and only if' you have a high transaction system and you do the same thing over and over again AND you have a ridiculously low hit ratio. It 'may' help point you to the real error. This does NOT necessarily mean you should increase your buffer cache. Could be very bad SQL. Could be unecessary large full table scans that bump your blocks out of memory.

I generally ignore it. There are better ways to tell if your cache is too small. Run statspack and look at the top 5 wait events. Easiest way. You can then upload your statspack report at www.oraperf.com and get a pretty good analysis(its still not finished, so you dont get a full analysis).

>
> 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.

I dont understand #3. Your goal is to reduce ALL I/O. Typically Physical I/O takes care of itself. For example today, I ran statspack and realized my PGA was too small. Got this because my top 5 wait events indicate an unnecessarily large amount of writes to my temp tablespace(sorts). So Ill make that bigger. What else do I do? Not much.

So you get most of your improvement by writing better queries AND answering this question 'Do I really have to do it this way?' Is there a better 'process'. That comes from analyzing your application. Received on Wed Dec 24 2003 - 14:34:16 CST