Tuning the DB_CACHE_SIZE

DB_CACHE_SIZE is the first parameter to look at in the initialization parameter file because it’s the most crucial parameter in Oracle.The data cache hit ratio is the percentage of the data block accesses that occur without requiring a physical  read from disk.

DB_CACHE_SIZE

it is the first parameter to look at in the initialization parameter file because it’s the most crucial parameter in Oracle. If the DB_CACHE_SIZE is set too low, Oracle won’t have enough memory to operate efficiently and the system may run poorly, no matter what else you do to it. If DB_CACHE_SIZE is too high, your system may begin to swap and may come to a halt.Having enough memory allocated to store data in memory depends on the value used for DB_CACHE_SIZE.

The following query can be used to view the data cache hit ratio:

select sum(decode(name,’physical reads’,value,0)) phys,
sum(decode(name,’db block gets’,value,0)) gets,
sum(decode(name,’consistent gets’, value,0)) con_gets,
(1 – (sum(decode(name,’physical reads’,value,0)) /
(sum(decode(name,’db block gets’,value,0)) +
sum(decode(name,’consistent gets’,value,0))))) * 100 hitratio
from v$sysstat;

Physical Reads DB Block Gets Consistent Gets Hit Ratio
————– ————– ————— ———–
1,671                   39,561 71,142                                              98.49
a data cache hit ratio of 95 percent or greater should be achievable for a well-tuned transactional application with the appropriate amount of memory. Because there is such a performance difference between some disk devices and memory access, improving the data cache hit ratio from 90 to 95 percent can nearly double system performance when reading disk devices that are extremely slow. Improving the cache hit ratio from 90 to 98 percent could yield nearly a 500 percent improvement where disks are extremely slow and under the right architectural setup.

Poor joins and poor indexing can also yield very high hit ratios due to reading many index blocks, so make sure that your hit ratio isn’t high for a reason other than a well-tuned system. An unusually high hit ratio may indicate the introduction of code that is poorly indexed or includes
join issues.

Written by admin on December 3rd, 2010 with comments disabled.
Read more articles on Oracle Tuning.

Related articles

Comments disabled

Comments on this article have been disabled.