How to tune database buffer cache?

Mechanism of database buffer cache:

Before tuning lets understand the mechanism of database buffer cache.Database buffer cache is portion of SGA that holds block images read from data files.All users who are concurrently connected to the instance share access to database buffer cache.

When user requires particular piece of data,it searches for the data in database buffer cache.If the process finds data already in cache then it’s called cache hit and it can read data directly from memory.If the process cannot find the data in cache then it’s called cache miss and must copy the data block from data file on disk to a buffer in cache.

Buffers are managed by LRU algorithm.It helps most recently used block tend to stay in memory to minimize disk access.

If u used automatic memory management while configuring SGA components then there is no need to manually tune the buffer cache.

The buffer cache hit ratio calculates how often a requested block has been found in the buffer cache without requiring disk access. This ratio is computed using data selected from the V$SYSSTAT performance view. Use the buffer cache hit ratio to verify the physical I/O as predicted by the V$DB_CACHE_ADVICE view.

Hit the below query to find below parameters:

> SELECT name, value FROM V$SYSSTAT WHERE name IN (‘db block gets from cache’, ‘consistent gets from cache’, ‘physical reads cache’);

Using values from output of this query calculate hit ratio by using formula :

1 – ((‘physical reads cache’) / (‘consistent gets from cache’ +
‘db block gets from cache’))

To set the size of database buffer cache hit the below command:
> alter system set sga_target=3000M scope=spfile sid=’*’;Continuous increases of the buffer cache size have no effect if the database is performing full table scans or operations that do not use the buffer cache. Database blocks accessed during a long full table scan are placed on the tail end of the Least Recently Used (LRU) list and not on the head of the list. Therefore, the blocks age out faster than blocks read when performing indexed lookups or small table scans. KEEP buffer pool and RECYCLE buffer pool are user for specialized buffer pool tuning.Keep buffer pool is designed to retain buffers in memory longer than LRU would normally retain them.RECYCLE buffer pool is designed to flush buffers from memory faster than LRU would normally do so. For sizing KEEP buffer pool hit below command:>alter system set db_keep_cache_size =1048765 scope=both;A good candidate for a segment to put into the KEEP pool is a segment that is smaller than 10% of the size of the DEFAULT buffer pool and has incurred at least 1% of the total I/Os in the system.”. Small table stands for any table that has 2% size of database buffer cache size.More concisely, any small table that is in high demand is a good candidate for KEEP caching. Depending on your database, caching important tables and indexes can increase performance by up to 50x;

Author Profile

Tejas

Passionate traveller,Reviewer of restaurants and bars,tech lover,everything about data processing,analyzing,SQL,PLSQL,pig,hive,zookeeper,mahout,kafka,neo4j