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

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

   Buffer hit ratios are not useless, but they should not be seen as the solution to all the database performance problems, as was believed by some experts in the past. For example, in a data warehouse application, the reading of a large table with a full table scan might very well produce a hit ratio of 0%, which in that case is probably acceptable (the FTS is acceptable because no records are left out, and therefore no index could help). The table has to be read only once, and there is therefore no point in trying to leave it in the buffer cache after it was read. In another OLTP application, if a small reference table is consistently aged out of the buffer pool, giving a hit ratio of say 50%, I agree that we should not be happy (I would recommend in that case to store that table in a separate buffer pool). That's why hit ratios have to be looked at as part a whole set of statistics in order to be able to reach conclusions.

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

    The CBO uses many parameters in order to make decisions (look at the output of event 10053 if you want to see them all). optimizer_index_caching is used by Oracle to take into consideration the extra work necessary when (index) data is not cached, and therefore a physical IO has to be performed to get the data. This is much more work than a logical IO, when the data is cached. For example, if optimizer_index_caching is 0, you tell Oracle that all the index data will need to be retrieved with physical IO. In that case, Oracle tends to try to avoid the use of indices, in favor of full-table-scans, for example.

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

    The use of different buffer pools is a way used to segregate tables according to their desired availability. For example, if you have a small table heavily accessed in your OLTP system, put it in your properly-sized KEEP buffer pool, and your users won't need to perform any expensive physical IO in order to read its data. Similarly, if you have a large table that you need to read very seldomly, assign it to your RECYCLE buffer pool, and it won't push out all the useful data in your DEFAULT pool. Hope you get the picture. As for the different sizes, different buffer pool sizes have different goals. To give you an example, if you have a large table which is part of a data warehouse, and you usually use a FTS when accessing that table, that table should probably be part of a tablespace with a large block size, to minimize the IO necessary when dealing with it. Similarly, if you have a table as part of an OLTP system, and its data is usually accessed with an index, then the best option is to use a small block size for the table data (since you access the data with the RowID anyway).   

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

   Oracle experts generally tend to try to minimize the number of physical block reads because it's well known that they are much more "expensive" than logical ones (I heard all kinds of numbers on how much more expensive they are, ranging from 1400 to 10000. The real number depends on each OS and config. It came down these past years, with the advent of RAID's). More and more people, including me, think that the goal should be to minimize the total number of block reads, and not only the physical ones. Logical reads, even if they are more efficient, also have a cost, especially when there are too many of them.

HTH Daniel (remove JUNK from my username to reach me if you want more details). Received on Wed Dec 24 2003 - 16:21:04 CST