Skip to content

How Much Benefit will be Gained after Increasing Database Buffer Cache

  • by
For avoiding reading same blocks over and over again, Oracle database puts them in memory called database buffer cache (i.e. DB_CACHE_SIZE) which is for retaining hot blocks in memory and maintains two lists through LRU algorithm. Now the question is, how much memory should we allocate to it and how much benefit we can expect to gain from the changing.

Luckily, the Oracle database knows and provides a dynamic view V$DB_CACHE_ADVICE for predicting reduced reads in several levels of database buffer cache, we can take advantage of it to tune our memory.
SQL> COLUMN size_for_estimate          FORMAT 999,999,999,999 heading 'Cache Size (MB)'
SQL> COLUMN buffers_for_estimate       FORMAT 999,999,999 heading 'Buffers'
SQL> COLUMN estd_physical_read_factor  FORMAT 999.90 heading 'Estd Phys|Read Factor'
SQL> COLUMN estd_physical_reads        FORMAT 999,999,999 heading 'Estd Phys| Reads'
SQL> SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor, estd_physical_reads FROM V$DB_CACHE_ADVICE WHERE name = 'DEFAULT' AND block_size  = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size') AND advice_status = 'ON';

                                Estd Phys    Estd Phys
 Cache Size (MB)      Buffers Read Factor        Reads
---------------- ------------ ----------- ------------
           2,560      317,600        2.76  425,022,515  <- 10% of Current Value
           5,120      635,200        2.20  338,612,300
           7,680      952,800        1.95  300,778,110
          10,240    1,270,400        1.61  247,796,533
          12,800    1,588,000        1.44  221,037,373  <- 50% of Current Value
          15,360    1,905,600        1.29  198,713,679
          17,920    2,223,200        1.19  183,528,430
          20,480    2,540,800        1.12  172,210,070
          23,040    2,858,400        1.06  162,397,078
          25,600    3,176,000        1.00  153,919,306  <-- Current Value
          28,160    3,493,600         .95  146,925,796
          30,720    3,811,200         .92  141,153,427
          33,280    4,128,800         .89  136,476,613
          35,840    4,446,400         .86  132,170,970
          38,400    4,764,000         .83  128,272,152  <-- 150% of Current Value
          40,960    5,081,600         .81  124,990,005
          43,520    5,399,200         .79  121,990,111
          46,080    5,716,800         .78  119,349,351
          48,640    6,034,400         .76  116,883,846
          51,200    6,352,000         .74  114,329,465  <-- 200% of Current Value

20 rows selected.

According to the above result, if we double (200%) the database buffer cache, we can reduce the reads to 0.74 times of current reads, which means we can expect to reduce about 26% of current reads. This is our expected benefit gains.

Do you have more enough memory to allocate larger db buffer? If not, you might want to choose a moderate value for your database, say, 150% or so. For more information, you can visit the official document: Tuning the Database Buffer Cache

Leave a Reply

Your email address will not be published.