18.1 Database Buffer Cache Hit Ratio

---

This ratio measures a ratio between finding blocks in the buffer and on disk. It is a ratio of logical I/O to disk or physical I/O, commonly known as LIOs and PIOs, respectively.

This ratio should not be used as an indicator of database performance health. Much Oracle software documentation touts the database buffer cache hit ratio as being one of the most important tuning metrics. In my opinion and that of many others this statement is often a complete and absolute falsity. LIOs are not "free". LIOs are generally faster than PIOs but not nearly as much as most Oracle software documentation would lead us to believe. Buffers in memory are much more complex to manage than simple disk I/O due to latch contention, ordered free lists, high concurrency, fragmentation, and many other factors. In short, reading from a buffer can sometimes be much more complex than performing I/O.

If the database buffer cache hit ratio is high then it means that data is being found in memory. If the ratio is low then queries are executing a fair amount of I/O. It is very difficult to state with certainty what causes this ratio to be high or low. The problem is that most existing documentation assumes that a high hit ratio is good. In fact sometimes a not-so-high hit ratio is also an indicator of good performance health. For instance, many full table scans are sometimes necessary, both on large and small tables, depending on application and database type. Full table scans perform I/O and bypass the database buffer cache. This can artificially lower the ratio. However, very poorly tuned SQL code, particularly joins, can cause unwanted full table scans. This also lowers the ratio. In the opposite direction lack of use of SQL code bind variables can cause a very high hit ratio, and as a result the database buffer is often sized much too high.

In general, do not simply attempt to tune the database buffer cache hit ratio. Do not jump to the conclusion that the database buffer cache must always be made larger. Even consider reducing the database buffer cache if the hit ratio is high, potentially freeing up memory for other requirements. Rather attempt to isolate the cause, then decide if there is a problem, finally tuning a real problem and not a ratio.

Tip 

Calculating the database buffer cache hit ratio is the same for both the Oracle8i Database DB_BLOCK_BUFFERS and the Oracle9i Database DB_CACHE SIZE parameters.

Database read buffer cache hit ratio =

1 - (physical reads/(db block gets + consistent gets))

These are the values I am interested in to calculate the read hit ratio.

SELECT value, name FROM V$SYSSTAT WHERE name IN       ('physical reads', 'db block gets', 'consistent gets');     VALUE   NAME -----   -----------------  2031   physical reads  3861   db block gets 85832   consistent gets

Database buffer cache hit ratio =

1 - (physical reads/(db block gets + consistent gets))

Here is a query to calculate the database buffer cache hit ratio.

SELECT 'Database Buffer Cache Hit Ratio ' "Ratio" , ROUND((1 - ((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'physical     reads') / ((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'db block     gets') + (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'consistent    gets') ))) * 100)||'%' "Percentage" FROM DUAL;

Tip 

All of the ratio queries in this chapter can be executed using joins but we would not be able to see immediate visual comparison of different ratios. There is a method to my madness in this respect.

This is the result of the query. This ratio is possibly healthy because it is not too high, but not necessarily. This value does not really tell me anything.

Ratio                                Percentage --------------------------------     ---------- Database Buffer Cache Hit Ratio             95% 

18.1.1 Default, Keep, and Recycle Pools

Inspecting database buffer cache hit ratios for the three different pools can be done as shown. The subquery is used for the ratio calculation to avoid divisions by zero, which is mathematically undefined.

COL pool FORMAT a10; SELECT a.name "Pool", a.physical_reads, a.db_block_gets       , a.consistent_gets ,(SELECT ROUND( (1-(physical_reads/(db_block_gets + consistent_gets)))*100)       FROM v$buffer_pool_statistics       WHERE db_block_gets+consistent_gets ! = 0       AND name = a.name) "Ratio" FROM v$buffer_pool_statistics a;

The keep pool should be high. The recycle pool should probably be low.

Pool    PHYSICAL_READS   DB_BLOCK_GETS   CONSISTENT_GETS   Ratio -----   --------------   -------------   ---------------   ----- KEEP                40          246570             12816     100 RECYCLE          75755         2874944            120748      97 DEFAULT         822495        17304843          10330119      97

Various tables and indexes were pushed into the keep and recycle pools in Chapter 15. Small static tables were pushed into the keep pool, excluding their indexes, assuming that the Optimizer would usually full table scan them. The Customer and Supplier tables are large semi-static tables and thus only their indexes were placed into the keep pool. The two largest tables, the GeneralLedger and StockMovement tables, were placed into the recycle pool. The results of 100%, 97%, and 97% on the keep, recycle, and default pools, respectively could tell a number of things but these ratios could also hide a lot of information. Let's cover a few points to drive home the point about potentially misleading information obtainable from database buffer ratios. Most importantly note that even though ratios are high for the keep and recycle pools, the physical and logical read numbers are low enough to be negligible.

Adjusting the Keep Pool for the Accounts Schema Changes in Chapter 15.

Small static tables are often full table scanned by the Optimizer. Since full table scans are read physically from disk there is no point putting them into the keep pool; they may never be retrieved from the buffer. Should their indexes be placed in the keep pool? Yes if those small tables are used in large joins. The Optimizer will actually use index reads, even for small static tables, in very well tuned SQL code joins. So I will push small static tables back into the default pool and place their indexes in the keep pool.

ALTER TABLE category STORAGE(BUFFER_POOL DEFAULT); ALTER TABLE coa STORAGE(BUFFER_POOL DEFAULT); ALTER TABLE customer STORAGE(BUFFER_POOL DEFAULT); ALTER TABLE period STORAGE(BUFFER_POOL DEFAULT); ALTER TABLE posting STORAGE(BUFFER_POOL DEFAULT); ALTER TABLE subtype STORAGE(BUFFER_POOL DEFAULT); ALTER TABLE supplier STORAGE(BUFFER_POOL DEFAULT); ALTER TABLE type STORAGE(BUFFER_POOL DEFAULT);     ALTER INDEX xak_categ_text STORAGE(BUFFER_POOL KEEP); ALTER INDEX xfk_coa_subtype STORAGE(BUFFER_POOL KEEP); ALTER INDEX xfk_coa_type STORAGE(BUFFER_POOL KEEP); ALTER INDEX xfk_post_crcoa STORAGE(BUFFER_POOL KEEP); ALTER INDEX xfk_post_drcoa STORAGE(BUFFER_POOL KEEP); ALTER INDEX xpk_category STORAGE(BUFFER_POOL KEEP); ALTER INDEX xpk_coa STORAGE(BUFFER_POOL KEEP); ALTER INDEX xpk_period STORAGE(BUFFER_POOL KEEP); ALTER INDEX xpk_posting STORAGE(BUFFER_POOL KEEP); ALTER INDEX xpk_subtype STORAGE(BUFFER_POOL KEEP); ALTER INDEX xpk_type STORAGE(BUFFER_POOL KEEP);

The keep pool could be used to avoid removal from cache of static data by larger tables and indexes.

Adjusting the Recycle Pool for the Accounts Schema Changes in Chapter 15.

In my highly active Accounts schema the two largest tables, the GeneralLedger and StockMovement tables, are only ever inserted into. Referential Integrity activity checks on primary and foreign keys might make it useful to place the Referential Integrity indexes into the keep pool and simply ignore the tables, pushing them back into the default pool.

ALTER TABLE generalledger STORAGE(BUFFER_POOL DEFAULT); ALTER TABLE stockmovement STORAGE(BUFFER_POOL DEFAULT); ALTER INDEX xfk_coa# STORAGE(BUFFER_POOL KEEP); ALTER INDEX xfk_sm_stock STORAGE(BUFFER_POOL KEEP); ALTER INDEX xpk_generalledger STORAGE(BUFFER_POOL KEEP); ALTER INDEX xpk_stockmovement STORAGE(BUFFER_POOL KEEP);

After a period of time the V$BUFFER_POOL_STATISTICS view contains values shown in the following query. We can see that use of all pools is the same as before. Use of the keep pool remains negligible. My active Accounts schema by the very nature of its activity would not make much use of the keep or recycle pools anyway. Only use specific database buffer pools when applications involve a mixture of OLTP and reporting activity, particularly where large properly tuned SQL code joins are concerned. And remember that full table scans bypass the database buffer cache. There is no point in pushing tables into the keep pool unless they are commonly read using index ROWID pointers.

Pool    PHYSICAL_READS   DB_BLOCK_GETS   CONSISTENT_GETS   Ratio -----   --------------   -------------   ---------------   ----- KEEP               233          274857             13305     100 RECYCLE          77446         2958975            124690      97 DEFAULT         869020        18045781          10803769      97

Tip 

Using the keep and recycle pools is subjective and unpredictable unless knowledge of application activity is well known. Some applications behave differently such as OLTP activity during the day and batch activity at night. Different buffer pool settings would be advisable for different activity cycles. Is it really worth using the keep and recycle pools? Perhaps not!

---