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
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