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.

This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference".  You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm