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’))
> alter system set sga_target=3000M scope=spfile sid=’*’;