Tuning – Buffer Cache

The buffer cache is a reserved memory space to keep data blocks read from disks. If you have a small buffer cache, oracle will often overwrite the LRU list (Least Recently Used) which will result in more I/O operations. So basically you will have to worry to not leave the buffer cache too small as well as not too big, because if you oversize the buffer, you are wasting memory of your server.

We use the V$DB_CACHE_ADVICE view to get the recomendation of what should be the size of the buffer cache, however, this view will be populated only if you have the profile parameter db_cache_advice enabled.

--Check if the parameter is enabled
show parameter db_cache_advice
--Query the view to get recomendation
SELECT size_for_estimate "Cache in MB",
estd_physical_read_factor "Read Factor", estd_physical_reads "Physical Reads"
AND block_size = (SELECT value
WHERE name = 'db_block_size')
AND advice_status = 'ON'
AND estd_physical_read_factor <= 1;
You will see something like:

Cache in MB Read Factor Physical Reads
----------- ----------- --------------
64          1           7270516
68          .9948       7232692
72          .9906       7202135
76          .985        7161738
80          .9617       6992007

According to my query, the first row should be the current value of db_cache_size (in the example above, 64M). The other results shows estimatives, for instance if I increase my db_cache_size to 80M the gain is not so good, so I would not change my db_cache_size in this case.


About Bruno Carvalho

Coffee addicted tech guy.
This entry was posted in ORACLE Database and tagged , , , , , , , , , . Bookmark the permalink.

One Response to Tuning – Buffer Cache

  1. Someone says:

    Good one!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s