Gudu Software: Provides Oracle tools to facilitate your daily DBA tasks

Home

Products

Downloads

Registration

Articles

Scripts

Links

About Us

Free Newsletter

Memory Tuning: Make sure the SGA is tuned correctly

Tuning the Buffer Cache
Oracle collects statistics that reflect data access and stores them in the dynamic preformance table V$SYSSTAT.These statistics are useful for tuning the buffer cache:

db block get,consistent gets:
The sum of the values of these statistics is the total number of access to buffers in memory.
physical reads:
The value of this statistic is the total number of requests for data resulting in access to datafiles on disk.

Monitor these statisitcs as follows over a period of time while your application si running:

select name,value from v$sysstat where name in ('db block gets','consistent gets','physical reads');

Calculate the hit ratio for the buffer cache with this formula:

Hit Ratio = 1-(physical reads/(db block gets + consistent gets))

The buffer cache hit ratio should be 90% or higher.
Since the size of the buffer cache is determined by:
   DB_BLOCK_SIZE * DB_BLOCK_BUFFERS, and DB_BLOCK_SIZE cannot be changed without recreating the database,you can increase DB_BLOCK_BUFFERS to improve Hit Ratio.
The relationship between cache hit ratio and number of buffers is far from a smooth distribution.When tuning the buffer pool,avoid the use of additional buffers that contribute little or nothing to the cache hit ratio.

Tuning is not a one time job but rather an iterative process which you do over and over, refining each time around.  OSM help you to monitor the hit/miss ratio for library cache,dictionary cache and buffer cache periodically,and save this useful information to file for later use.
1       2