Even when the buffer cache seems to be tuned properly, database administrators can still improve performance by targeting physical read reductions. Let’s take a look at several examples.
In the example below, data extracted from a database performance report (from spreport or AWR) reveals very good buffer cache efficiency for this instance, which is not 100% OLTP. For a true OLTP environment, the buffer hit percentage should be closer to the 100% goal.
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.99
Buffer Hit %: 98.76
In our second example, we see Buffer Cache Advisory information found in a database performance report (from spreport or AWR). This data shows the current buffer cache size to be 60GB (*) with estimated physical reads of 337 million. Above that, with a buffer cache of 12 GB (#), the database would have to do five times more physical reads to meet the data demand. Further down, it reveals that a 102 GB (+) buffer cache should reduce physical reads to 252 million.
Size for Size Buffers for Est Physical Estimated P Estimate (M) Factr Estimate Read Factor Physical Reads --- ------------ ----- ---------------- ------------- ------------------ D 6,016 .1 719,288 9.24 3,118,737,625 # D 12,032 .2 1,438,576 4.97 1,675,411,920 # D 18,048 .3 2,157,864 2.27 764,240,471 D 24,064 .4 2,877,152 1.71 578,034,614 D 30,080 .5 3,596,440 1.53 515,338,408 D 36,096 .6 4,315,728 1.35 454,163,441 D 42,112 .7 5,035,016 1.23 414,696,756 D 48,128 .8 5,754,304 1.14 384,585,214 D 54,144 .9 6,473,592 1.06 358,487,603 * D 60,112 1.0 7,187,141 1.00 337,345,517 * * D 60,160 1.0 7,192,880 1.00 337,218,573 * D 66,176 1.1 7,912,168 0.94 317,924,246 D 72,192 1.2 8,631,456 0.90 303,484,236 D 78,208 1.3 9,350,744 0.87 294,082,123 D 84,224 1.4 10,070,032 0.84 282,769,160 D 90,240 1.5 10,789,320 0.81 271,771,069 D 96,256 1.6 11,508,608 0.78 262,694,208 + D 102,272 1.7 12,227,896 0.75 252,623,775 + D 108,288 1.8 12,947,184 0.73 246,289,861 D 114,304 1.9 13,666,472 0.72 241,684,608 D 120,320 2.0 14,385,760 0.71 238,677,926
Physical reads from a database perspective is a slow process; therefore, reducing physical reads will improve overall database performance.There will be a point of diminishing returns, however, as it’s not possible to eliminate all physical reads. But allocating additional memory to hold more data blocks should improve database responsiveness for OLTP transactions and reduce job durations for batch and reporting. Remember to coordinate this expansion with the system administration to ensure kernel parameters, like shmmax, support the needed memory allocation.
Yes, memory is a more expensive resource than disk, but the cost of the memory is less expensive than the cost of lost productivity for the business. Go ahead, give it a shot and post your findings as a comment!
Leave a Reply