Database Buffer Cache Size

Oracle Certification, Database Administration, SQL, Programming Reference Books Database Buffer Cache Size

The Database Buffer Cache is part of the Shared Global Area (SGA) in memory for a single database instance (SID) and holds the blocks of data and indexes that you and everyone else is currently using. It may even contain multiple copies of the same data block if, for example, more than one transaction is making changes to it but not yet committed, or, if you are looking at the original copy (select) and someone else is looking at their modified but uncommitted copy (insert, update, or delete). The parameters db_block_buffers and db_block_size in your init.ora file determine the size of the buffer cache. db_block_size, in bytes, is set at database creation, and cannot be changed (unless you recreate the database from scratch), so, the only thing that you can adjust is the number of blocks in db_block_buffers (one buffer holds one block).

The Cache Hit Ratio shows how many blocks were already in memory (logical reads, which include "db block gets" for blocks you are using and "consistent gets" of original blocks from rollback segments that others are updating) versus how many blocks had to be read from disk ("physical reads"). Oracle recommends that this ratio be at least 80%, but, I like at least 90% myself. The ratio can be obtained from values in v$sysstat, which are constantly being updated and show statistics since database startup (it is only accessable from a DBA user account). You will get a more representative sample if the database has been running several hours with normal user transactions taking place. The Cache Hit Ratio is determined as follows:

select (1-(pr.value/(dbg.value+cg.value)))*100
     from v$sysstat pr, v$sysstat dbg, v$sysstat cg
     where pr.name = 'physical reads'
          and dbg.name = 'db block gets'
          and cg.name = 'consistent gets';

If you have a low Cache Hit Ratio, you can test to see what the effect of adding buffers would be by putting "db_block_lru_extended_statistics = 1000" in the init.ora file, doing a shutdown and startup of the database, and waiting a few hours to get a representative sample. Oracle determines how many Additional Cache Hits (ACH) would occur for each query and transaction for each of the 1000 buffer increments (or whatever other maximum value you might want to try out), and places them into the x$kcbrbh table, which is only accessable from user "sys". To measure the new Cache Hit Ratio with, for example, 100 extra buffers, determine ACH as follows:

select sum(count) "ACH" from x$kcbrbh where indx < 100;

and plug that value into the Cache Hit Ratio formula as follows:

select (1-((pr.value-&ACH)/(dbg.value+cg.value)))*100
     from v$sysstat pr, v$sysstat dbg, v$sysstat cg
     where pr.name = 'physical reads'
          and dbg.name = 'db block gets'
          and cg.name = 'consistent gets';

If the ratio originally was lower than 80% and is now higher with ACH, you may want to increase db_block_buffers by that number of extra buffers, restarting your database to put the increase into effect. Be sure to try several values for the number of extra buffers to find an optimum for your work load. Also, remove db_block_lru_extended_statistics from your init.ora file before restarting your database to stop gathering statistics, which tends to slow down the transaction time. (Removing that clears the x$kcbrbh table.) Also, make sure that your server has enough memory to accomodate the increase!

If you are running really tight on memory, and the Cache Hit Ratio is running well above 80%, you might want to check the effect of lowering the number of buffers, which would release Oracle memory that could then be used by other processes, but would also potentially slow down database transactions. To test this, put "db_block_lru_statistics = true" in your init.ora file and restart your database. This gathers statistics for Additional Cache Misses (ACM) that would occur for each query and transaction for each of the buffer decrements up to the current db_block_buffers value, placing them into the x$kcbcbh table, also only accessable from user "sys". To measure the new Cache Hit Ratio with, for example, 100 fewer buffers, determine ACM as follows:

select sum(count) "ACM" from x$kcbcbh
where indx >= (select max(indx)+1-100 from x$kcbcbh);

and plug that value into the Cache Hit Ratio formula as follows:

select (1-((pr.value+&ACM)/(dbg.value+cg.value)))*100
     from v$sysstat pr, v$sysstat dbg, v$sysstat cg
     where pr.name = 'physical reads'
          and dbg.name = 'db block gets'
          and cg.name = 'consistent gets';

If the ratio is still above 80%, you may want to decrease db_block_buffers by that number of fewer buffers, restarting your database to put the decrease into effect. Be sure to try several values for the number of fewer buffers to find an optimum for your work load. Also, remove db_block_lru_statistics from your init.ora file before restarting your database to stop gathering statistics, which tends to slow down the transaction time. (Removing that clears the x$kcbcbh table.)

I have three scripts which you can use to figure your instance's optimum number of db_block_buffers. The cache_hit_ratio.sql script computes the current ratio for the database buffer cache, and can be run from any DBA account. The adding_buffers.sql script computes the resulting ratio for an increase in the buffer cache size of the given number of buffer blocks (figuring ACH itself). It must be run from user "sys", after a representative sampling time with db_block_lru_extended_statistics in place. The removing_buffers.sql script computes the resulting ratio for a decrease in the buffer cache size of the given number of buffer blocks (figuring ACM itself). It must be run from user "sys", after a representative sampling time with db_block_lru_statistics in place.

Exit to : Oracle Hints and Tips