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.
- KEEP
- RECYCLE
- 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.
Persistent Storage Solutions 