For tuning buffer cache, we need to understand following point very closely.

Physical reads: These are the data blocks that Oracle reads from disk. Reading data from disk is much more expensive than reading data that’s already in Oracle’s memory. When you issue a query, Oracle always first tries to retrieve the data from memory—the database buffer cache—and not disk.

DB block gets: This is a read of the buffer cache, to retrieve a block in current mode. This most often happens during data modification when Oracle has to be sure that it’s updating the most recent version of the block. So, when Oracle finds the required data in the database buffer cache, it checks whether the data in the blocks is up to date. If a user changes the data in the buffer cache but hasn’t committed those changes yet, new requests for the same data can’t show these interim changes. If the data in the buffer blocks is up to date, each such data block retrieved is counted as a DB block get.
Consistent gets: This is a read of the buffer cache, to retrieve a block in consistent mode. This may include a read of undo segments to maintain the read consistency principle. If Oracle finds that another session has updated the data in that block since the read began, then it will apply the new information from the undo segments.

Logical reads: Every time Oracle is able to satisfy a request for data by reading it from the database buffer cache, you get a logical read. Thus logical reads include both DB block gets and consistent gets.

Buffer gets: This term refers to the number of database cache buffers retrieved. This value is the same as the logical reads described earlier.

So basically buffer cache hit ratio is all about the rate at which you get information in your memory cache and less accessing the disk.

so in short we can say from above defination,

Buffer cache hit ratio = 1 – (physical read/logical reads)

Here logical reads means reading from memory.

SQL> SELECT name, value FROM v$sysstat
2  where name IN (‘physical reads cache’,
3  ‘consistent gets from cache’,
4  ‘db block gets from cache’);          

NAME                                                                  VALUE
—————————————————————- ———-
db block gets from cache                                          164998308
consistent gets from cache                                       2136448944
physical reads cache                                                2787422

Here physical reads are ‘physical reads cache’ stored in v$sysstat.
logical reads are, sum of ‘consistent gets from cache’ and ‘db block gets from cache’.

so our buffer cache hit ratio will be 1 – (2787422 / (2136448944 + 164998308)) = 0.9987

Another way to calculate the same buffer cache hit ratio is our query on v$sysstat

SQL> SELECT 1- ((p.value – l.value – d.value) / s.value)
AS “Buffer Cache Hit Ratio”
FROM v$sysstat s, v$sysstat l, v$sysstat d, v$sysstat p
WHERE s.name = ‘session logical reads’
AND d.name = ‘physical reads direct’
AND l.name = ‘physical reads direct (lob)’
AND p.name = ‘physical reads’

The above query will also give almost the same result. Here actually,

physical reads cache = physical reads – (physical reads direct (lob) + physical reads direct) and session logical reads = consistent gets from cache + db block gets from cache

LRU Algorithm or Touch Count Algorithm ??

Since version 8.1, Oracle has used a concept called touch count to measure how many times an object is accessed in the buffer cache. This algorithm of using touch counts for managing the buffer cache is somewhat different from the traditional modified LRU algorithm that Oracle used to employ for managing the cache. Each time a buffer is accessed, the touch count is incremented.

low touch count means that the block isn’t being reused frequently, and therefore is wasting database buffer cache space. If you have large objects that have a low touch count but occupy a significant proportion of the buffer cache, you can consider them ideal candidates for the recycle pool.

TCH (Touch count) is a column present in x$bh table of data dictionary, which keeps track of touch counts of objects.

Following query will give you the objects which are consuming reasonable amount of memory and are the candidates for getting flushed out of buffer cache.

SELECT
obj object,
count(1) buffers,
(count(1)/totsize) * 100 percent_cache
FROM x$bh,
(select value totsize
FROM v$parameter
WHERE name =’db_block_buffers’)
WHERE tch=1
OR (tch = 0 and lru_flag <10)
GROUP BY obj, totsize
HAVING (count(1)/totsize) * 100 > 5

Remember here that, this explaination is just for understanding. We dont have to do anything in buffer cache for getting these objects flushed out. Removal of this objects are handled automatically by database engine.

From this query you can get object_id and from that you can find object_name (using dba_objects).

using Multiple Pools for the Buffer Cache

As you already know we can have multiple pool for buffer cache, so I wont be describing the same here, else we will loose the focus on current discussion.
We have basically 3 types of buffer pools.

1. KEEP
2. RECYCLE
3. DEFAULT

Default pool will always be there. However depending on the situation we can create keep and recycle pools. If there are some objects, which are accessed frequently, then will want to keep such objects in database cache. For such objects we can use keep buffer pool. Objects which are big and not accessed frequently can be put in recycle pool. By default if we dont specify buffer pool, object will always go to default pool.

V$BUFFER_POOL_STATISTICS will give statistics for all pools.

Determining Candidates for the Recycle Buffer Pool

Candidates which are having low TCH value as given by above query are best to put in recycle pool.

Determining Candidates for the Keep Buffer Cache

SELECT obj object,
count(1) buffers,
AVG(tch) average_touch_count
FROM x$bh
WHERE lru_flag = 8
GROUP BY obj
HAVING avg(tch) > 5
AND count(1) > 25;

Above query will give the candidates which are having avg TCH value of more then 5 and number of buffers occupied in buffer cache as more then 25. Such objects can be placed in KEEP buffer cache. You can place an object in a perticular pool using alter table command.

ALTER TABLE test1 STORAGE (buffer_pool keep);

Sizing buffer cache

For sizing buffer cache, you can check V$DB_CACHE_ADVICE view. This view will provide you information about various buffer cache sizes and estimated physical read, when we use those sizes for buffer cache. Based on this result you should be able to decide the correct size of database buffer cache.

SQL> select NAME, SIZE_FOR_ESTIMATE, SIZE_FACTOR, ESTD_PHYSICAL_READS, ESTD_PCT_OF_DB_TIME_FOR_READS
2  from v$db_cache_advice;

NAME       SIZE_FOR_ESTIMATE SIZE_FACTOR ESTD_PHYSICAL_READS ESTD_PCT_OF_DB_TIME_FOR_READS
———  —————– ———– ——————- —————————–
DEFAULT       112             .0946      6871847             2.4
DEFAULT       224             .1892      5435019             1.8
DEFAULT       336             .2838      4600629             1.5
DEFAULT       448             .3784      4125422             1.3
DEFAULT       560              .473      3831101             1.1
DEFAULT       672             .5676      3598589             1
DEFAULT       784             .6622      3381913             .9
DEFAULT       896             .7568      3154726             .8
DEFAULT      1008             .8514      2957398             .8
DEFAULT      1120             .9459      2841502             .7
DEFAULT      1184                 1      2791921             .7
DEFAULT      1232            1.0405      2757728             .7
DEFAULT      1344            1.1351      2689955             .6
DEFAULT      1456            1.2297      2653143             .6
DEFAULT      1568            1.3243      2631218             .6
DEFAULT      1680            1.4189      2608447             .6
DEFAULT      1792            1.5135      2588899             .6
DEFAULT      1904            1.6081      2573463             .6
DEFAULT      2016            1.7027      2561587             .6
DEFAULT      2128            1.7973      2549937             .6
DEFAULT      2240            1.8919      2535972             .6

21 rows selected.     

Based on ESTD_PCT_OF_DB_TIME_FOR_READS and ESTD_PHYSICAL_READS, you can decide the correct size for db buffer cache.