Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Buffer cache statistics (ratios) and CBO SQL optimization?

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

From: Noons <wizofoz2k_at_yahoo.com.au.nospam>
Date: Fri, 26 Dec 2003 00:22:25 +1100
Message-ID: <3feae416$0$18752$afc38c87@news.optusnet.com.au>


"Geomancer" <pharfromhome_at_hotmail.com> wrote in message news:cf90fb89.0312240738.264cc573_at_posting.google.com...

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

This seems to be an "ongoing issue" with you, Geo.

Try this: "BCHR means very little to tuning nowadays". Repeat it in your mind a few thousand times until it kinda matches all the other mantras. Give it a big "hit ratio", if you prefer the terminology.

Then start looking away from the "gurus" you've relied on until now. Another thing to do: subscribe to oracle-l. Those folks know what they are talking about. Few write heaps of books, you see? Some are very theoretical with little experience of the crap out there. Others are on the coal-face. Most just provide free, good info. Kinda like this NG, but you just don't believe anyone here, do you? After all it's the Usenet, full of weirdos!

One doesn't need to write books to provide credible info. That's something you'll learn slowly.

> - Why does the CBO want to know estimates about the index buffer
> hit ratio (optimizer_index_caching parameter)?

It doesn't. That parameter has nothing to do with index hit ratio. It merely indicates to CBO that indexes have a given probability of being in cache. It's a probability, not a hit ratio.

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

Because a single buffer pool has no chance of ever coping with ALL possible patterns of usage of blocks in a cache. And there are (surprise, surprise) heaps of database instances out there running more than one application. And applications that have varying patterns of I/O rates and locations. IBM covered this in their DB2 technical documentation ages ago, do a search in their archives. Oracle just caught up with it. Finally. And not complete. We'll need buffer-to-tablespace mapping at some stage. They'll learn...

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

They don't. They place high emphasis on "logical" block reads. There is a world of difference. Reduce the logical block reads and you'll reduce the physical as well. The reverse is not true.

> 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

Thats not what he says. Read the whole lot again.

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

Like I said: read the whole lot, not just that.

Here is something for you to think about. Picture this situation: a tablespace with datafiles in a single disk. Another with datafiles in a RAID setup with heaps of cache (disk farm style).

Now, how does Oracle CBO know which blocks are cached in Oracle and which are also in the disk farm cache? How does it know that a physical read from the disk farm will very likely hit a block in its cache while one from the single disk will never hit a hardware cache?

Have you ever seen an "analysis" by the hit ratio brigade that took this into consideration? Never? Yes, they all assume "ideal" conditions. For what is in most cases a non-ideal situation: the setup the user has in its shop.

That is the problem with hit ratios and physical I/O tuning: it means NOTHING unless you replicate EXACTLY the setup of the guru.

Logical I/O tuning on the contrary eliminates all that uncertainty: it doesn't matter what the setup of the user is, an I/O to the Oracle buffer cache is NOT affected by how your disks are setup. Reduce that logical I/O with tuning and you'll get better results NO MATTER what your setup is!

Now, isn't that a much better way of doing things?

-- 
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam
Received on Thu Dec 25 2003 - 07:22:25 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US