Viewing Information about SGA Performance
The following Oracle Database10g views provide information about the SGA components and their dynamic resizing:
Determining the optimal size for the data buffers is a critical task for very large databases. It is economically prohibitive to cache an entire database in RAM as databases grow ever larger, perhaps reaching sizes in the hundreds of billions of bytes. The difficulty Oracle professionals face is finding the point of diminishing marginal returns as additional RAM resources are allocated to the database.
Successfully determining the point of diminishing marginal return and effectively optimizing RAM can save a company hundreds of thousands, if not millions, of dollars in RAM expenses.
Among the features that Oracle10g has automated within AMM is the v$db_cache_advice view. This view can help predict the benefit of adding buffers to the data buffer cache. It estimates the miss rate for twenty potential buffer cache sizes, ranging from 10 percent of the current size to 200 percent of the current size. This tool allows the Oracle DBA to accurately predict the optimal size for each RAM data buffer. A few examples will help illustrate the process.
In order to use the new view, RAM memory must be pre-allocated to the data buffers, just as it was in the Oracle7 x$kcbcbh utility. Setting the init.ora parameter, db_cache_advice, to the value of ON or READY enables the cache advice feature. The DBA can set these values while the database is running by using the ALTER SYSTEM command, taking advantage of the predictive feature dynamically.
However, since the additional RAM buffers must be allocated before the db_cache_size can use v$db_cache_advice , the DBA may wish to use the utility only once to determine the optimal size.
The v$db_cache_advice view is similar to an Oracle7 utility that also predicted the benefit of adding data buffers. The Oracle7 utility used the x$kcbrbh view to track buffer hits and the x$kcbcbh view to track buffer misses. Also, there is no way to get cache advice on Oracle8 since db_block_lru_statistics was made obsolete.
The data buffer hit ratio can provide data similar to v$db_cache_advice , and most Oracle tuning professionals use both tools to monitor the effectiveness of data buffers and monitor how AMM adjusts the sizes of the buffer pools.
The following query can be used to perform the cache advice function, once the db_cache_advice has been enabled and the database has run long enough to give representative results.
The output from the script is shown below. The values range from 10 percent of the current size to double the current size of the db_cache_size
Estd Phys Estd Phys
Cache Size (MB) Buffers Read Factor Reads
---------------- ------------ ----------- ------------
30 3,802 18.70 192,317,943 ç 10% size
60 7,604 12.83 131,949,536
91 11,406 7.38 75,865,861
121 15,208 4.97 51,111,658
152 19,010 3.64 37,460,786
182 22,812 2.50 25,668,196
212 26,614 1.74 17,850,847
243 30,416 1.33 13,720,149
273 34,218 1.13 11,583,180
304 38,020 1.00 10,282,475 Current Size
334 41,822 .93 9,515,878
364 45,624 .87 8,909,026
395 49,426 .83 8,495,039
424 53,228 .79 8,116,496
456 57,030 .76 7,824,764
486 60,832 .74 7,563,180
517 64,634 .71 7,311,729
547 68,436 .69 7,104,280
577 72,238 .67 6,895,122
608 76,040 .66 6,739,731 ç 2x size
The output shows neither a peak in total disk I/O nor a marginal trend with additional buffer RAM. This result is typical of a data warehouse database that reads large tables with full-table scans. In this case, there is no specific optimal setting for the db_cache_size parameter. Oracle will devour as much data buffer RAM as is fed to it, and disk I/O will continue to decline. However, there is no tangential line that indicates a point of diminishing returns for this application.
This predictive model is the basis for Oracle10g AMM. When the data from Oracle’s buffer caching advisory is plotted, the tradeoff is clearly visible as shown in Figure 14.3.
The v$db_cache_advice view is now run dynamically in the sysaux_xxx views. It is similar to an Oracle7 utility that also predicted the benefit of adding data buffers. The Oracle7 utility used the x$kcbrbh view to track buffer hits and the x$kcbcbh view to track buffer misses.
The DBHR can provide data similar to v$db_cache_advice , and most Oracle tuning professionals use both tools to monitor the effectiveness of data buffers.
If the advisory output shows neither a peak in total disk I/O nor a marginal trend with additional buffer RAM, the advisory utility may not apply, and the DBA might consider disabling AMM. Taking the above into account, Oracle10g will apply this simple rule: db_cache_size should be increased if spare memory is available and marginal gains can be achieved by adding buffers.
The main point of this relationship between RAM buffering and physical reads is that all Oracle databases have data that is accessed with differing popularity. In sum, the larger the working set of frequently referenced data blocks, the greater the benefit from speeding up block access.
The next section provides insight into the internal mechanism of Oracle AMM and how it reacts to changes in buffer demands.