DB_BLOCK_BUFFERS
----------------
This parameter is available in all versions of Oracle, and is measured in Oracle blocks. The value of this parameter is very important for storing data into memory as users are requesting information from the system.
DB_Block_Buffers are the number of buffers in the SGA cache that will be available for user data to be stored in memory. The size of the buffer cache, which is used to cache db blocks in the SGA, is specified by this parameter. Because the data is cached, this reduces the amount of physical I/O.
In turn, the setting of this parameter has a large effect on the buffer cache hit ratio, which you generally want to be above 90%. The hit ratio can be dynamically determined based on the following query:
select round(((1-(sum(decode(name,
'physical reads', value,0))/
(sum(decode(name, 'db block gets', value,0))+
(sum(decode(name, 'consistent gets', value, 0))))))*100),2)
|| '%' "Buffer Cache Hit Ratio"
from v$sysstat;
The result of this query would be similar to the following:
Buffer Cache Hit Ratio:
97.63%
If the hit ratio is below 90%, then it would be advisable to increase
DB_Block_Buffers until you obtain a hit ration above 90%. The implications of the DB_Block_Buffers being set too low are that the least recently used data will be flushed from memory. So guess what? If another query needs this data, back out to disk we go. This will cause I/O and CPU resources to be used. If the value is set too high, swapping will began to occur on the OS and the system may come to a halt.
See <Note.62172.1> for further information on Tuning the Database Buffer Cache.
|