Oracle10g Informations About Sga And Buffer Cache

Sharing Memory—Automatically By Kimberly Floss Put away your scripts and let Oracle automatically resize your memory pools. It used to be a challenge to size the various memory pools that comprise the Oracle system global area (SGA) for optimal performance. But that was before Oracle Database 10g. For example, the buffer cache had to be large enough to keep frequently used blocks readily available for fast retrieval—but not so large that the database couldn't allocate memory in one of the other SGA pools when needed. Since various types of applications and usage patterns tax the assorted pools differently, and since the workload can vary minute by minute, manually resizing the SGA components could feel like a never-ending task. If there wasn't enough free SGA to allocate memory to a specific pool when needed, the database would raise an outof-memory error, such as ORA-04031: unable to allocate ...

Also, before Oracle9i Database Release 2, adjusting the pool sizes required bouncing the server—hardly practical in a production environment. That's why Oracle Database 10g's Automatic Shared Memory Management (ASMM) is such a welcome improvement. First introduced in Oracle Database 10g and further enhanced in Oracle Database 10g Release 2, ASMM automatically sizes many of the memory pools while the database is running, allocating and de-allocating memory as needed. As the workload composition changes, Oracle Database 10g enlarges the appropriate pools and reduces the sizes of other automatically sized pools accordingly. In short, ASMM can save you a lot of trouble—and improve overall performance as well. Let's take a closer look at how it works and how to use it.

SGA Memory Components The linchpin of Oracle Database 10g's memory management scheme is the new SGA_TARGET initialization parameter. The value of this setting determines the total amount of SGA memory that can be allocated across both manually and automatically sized pools. (See Table 1, in the online version of this article at oracle.com/technology/oramag/oracle/ 05-sep/o55tuning.html.) The SGA_TARGET value imposes a ceiling on the amount of RAM devoted to the Oracle SGA. Oracle Database 10g Release 2 can automatically tune the size of the shared pool, buffer cache, Java pool, large pool, and streams pool. However, you must still manually tune several SGA components, including the log buffer (at startup time only), the keep buffer cache, the recycle buffer cache, and all of the nonstandard block-size buffer caches (for example, the 32K and 16K buffer caches set by the db_32K_cache_size and db_16K_cache_size parameters). Manually sized components consume SGA from the target value first, and then the remainder of SGA memory is spread across the various autosized pools. In other words, if you set parameter values for any of these manually tuned pools, Oracle Database 10g Release 2 subtracts their sizes from SGA_TARGET first, before allocating memory to the automatically allocated pools. The SGA_TARGET setting value also includes a small amount of fixed-size SGA. Oracle Database 10g Release 2 sets the fixed size based on the operating system and other criteria. You can see the amount (in bytes) of the fixed-size SGA and totals of other major elements that occupy the SGA by querying the V$SGA view, as follows:

SQL> select * from v$sga; NAME VALUE --------------------------Fixed Size 1247780 Variable Size 124319196 Database Buffers 41943040 Redo Buffers 262144

You can query this same view to determine an initial size for SGA_TARGET when you switch from manual to autotuning, by summing all the components as follows: SQL> select sum(value)/1024/1024 "Megabytes" from v$sga; Megabytes -----------------160

Listing 1 shows an example of total real memory allocation for the current SGA from the V$SGA_DYNAMIC_COMPONENTS view (introduced in Oracle9i Database), which contains both manual and autotuned SGA components. Code Listing 1: Query of V$SGA_DYNAMIC_COMPONENTS SQL> select component, current_size from v$sga_dynamic_components; COMPONENT ------------------------------shared pool large pool java pool streams pool DEFAULT buffer cache KEEP buffer cache RECYCLE buffer cache DEFAULT 2K buffer cache DEFAULT 4K buffer cache DEFAULT 8K buffer cache DEFAULT 16K buffer cache DEFAULT 32K buffer cache ASM Buffer Cache

CURRENT_SIZE ------------------------92274688 8388608 8388608 12582912 33554432 4194304 4194304 0 0 0 0 0 0

13 rows selected.

Using ASMM As with the other manageability features of Oracle Database 10g, ASMM requires you to set the STATISTICS_LEVEL parameter to at least TYPICAL (the default), so make sure this setting is correct before you try to enable ASMM. You can enable it in the following ways: • •

Set SGA_TARGET to a nonzero value in the initialization parameter file (pfile). Use Oracle Enterprise Manager (or Oracle Grid Control) or ALTER SYSTEM to dynamically set a nonzero value for SGA_TARGET in the server parameter file (spfile).

To use the command line (ALTER SYSTEM), set a value for SGA_TARGET and then set the parameters for all of the autotuned pools to 0, as follows:

SQL> alter system set sga_target=160M scope=both; System altered. SQL> alter system set db_cache_size=0; System altered. SQL> alter system set shared_pool_size=0; System altered.

If you don't set the values for each of the autotuned pools to zero after switching to ASMM, whatever value you had set for a parameter will function as a lower threshold value for that pool—ASMM won't go below a nonzero value, even if it needs the memory for another autotuned pool. Rather than entering each of these ALTER SYSTEM commands for each of the autotuned pools as shown above, you can use Oracle Enterprise Manager to accomplish the same thing in one step, as part of the switch from Manual to Automatic tuning, by clicking the Enable button on the Memory Parameters page. This also shows you at a glance the various memory allocations at any time. To open the Memory Parameters page, from Database Control's Administration page, click the Advisor Central link (under the Related Links heading near the bottom of the page), and then click the Memory Advisor link. The Memory Parameters page shows the memory allocations across all major components over time, since the last restart of the database, as shown in Figure 1. Lower down on the page you'll see a pie chart and a table displaying the Current Allocations. The chart shows the allocations as percentages of the total SGA that can be allocated, while the table shows the component and a value, in megabytes, of the allocation.

Figure 1: Memory Parameters page Also on the Memory Parameter page, adjacent to the Total SGA Size field, is an Advice button—new in Oracle Database 10g Release 2. This lets you assess the impact of increasing (or decreasing) the system's target SGA. The v$sga_target_advice view also provides this information, as shown in Listing 2. The v$sga_target_advice view gives you the information you need to modify the SGA_TARGET parameter (up to the size of the SGA_MAX_SIZE, if necessary). For the example in Listing 2, we can increase the SGA_TARGET to 200 and thereby reduce physical reads by about 10%. Code Listing 2: Query of V$SGA_TARGET_ADVICE SQL> select sga_size, sga_size_factor, estd_db_time, estd_db_time_factor, estd_physical_reads from v$sga_target_advice order by sga_size_factor; SGA_SIZE --------120 160 240 280 200 320

SGA_SIZE_FACTOR ----------------.75 1 1.5 1.75 1.25 2

6 rows selected.

ESTD_DB_TIME -------------421 421 421 421 421 421

ESTD_DB_TIME_FACTOR ------------------1 1 1 1 1 1

ESTD_PHYSICAL_READS ------------------26042 8223 7340 7340 7340 7340

SHARED_POOL_SIZE Value in Oracle Database 10g If you're going to manually tune Oracle Database 10g SGA pools, note one key difference in how it interprets the shared pool value. In releases prior to Oracle Database 10g, the shared pool size was set equal to the shared_pool_size initialization parameter value, plus the amount of internal SGA overhead—memory that the database allocated during startup to maintain state for various server components of the SGA. So if you set the shared_pool_size parameter to 48M in Oracle9i Database, for example, the actual shared pool size allocated by the database might have been 60MB, assuming a startup overhead amount of 12MB. In Oracle Database 10g, the shared pool size is the actual value of the parameter only—yet the overhead must still be accounted for in the shared pool. This means that if you're migrating to Oracle Database 10g from a prior release and you plan to manually size the various memory pools, you must make the shared_pool_size parameter value slightly larger in Oracle Database 10g than in your prior release to account for the startup overhead. In Oracle Database 10g, you can see precisely how much this startup overhead is by querying the v$sgainfo dynamic view—you'll find a value for "Startup overhead in shared pool." Conclusion It doesn't matter how much memory the system has if it's not properly allocated across the appropriate SGA pools at runtime. Yet since the system requirements can change from one minute to the next, these pools must be constantly adjusted. Oracle Database 10g's ASMM is a huge time-saver, and it also reduces the chances of ORA-04031 errors. The result? Your system will perform better—and you'll save time and trouble.

Oracle10g: Viewing Information About the SGA Oracle Tips by Burleson The following Oracle Database 10g views provide information about the SGA components and their dynamic resizing: v$sga - Displays summary information about the system global area (SGA). v$sgainfo - Displays size information about the SGA, including the sizes of different SGA components, the granule size, and free memory. v$sgastat - Displays detailed information about the SGA. v$sga_dynamic_components - Displays information about the dynamic SGA components. This view summarizes information based on all completed SGA resize operations since instance startup. v$sga_dynamic_free_memory - Displays information about the amount of SGA memory available for future dynamic SGA resize operations. v$sga_resize_ops - Displays information about the last 100 completed SGA resize operations. v$sga_current_resize_ops - Displays information about SGA resize operations which are currently in progress. An operation can be a grow or a shrink of a dynamic SGA component.

Oracle10g v$db_cache_advice 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. Bear in mind that 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 their data buffers. The following query can be used to perform the cache advice function, once the v$db_cache_advice has been enabled and the database has run long enough to give representative results. In Oracle Database 10g, you can also OEM interface to see these results in real-time. The output from the script is shown below. Note that the values range from 10 percent of the current size to double the current size of the db_cache_size (Figure 1.6). Estd Phys Estd Phys Cache Size (MB) Buffers Read Factor Reads ---------------- ------------ ----------- ------------

30 60 91 121 152 182 212 243 273 304 334 364 395 424 456 486 517 547 577 608

3,802 7,604 11,406 15,208 19,010 22,812 26,614 30,416 34,218 38,020 41,822 45,624 49,426 53,228 57,030 60,832 64,634 68,436 72,238 76,040

18.70 12.83 7.38 4.97 3.64 2.50 1.74 1.33 1.13 1.00 .93 .87 .83 .79 .76 .74 .71 .69 .67 .66

192,317,943 ç 10% size 131,949,536 75,865,861 51,111,658 37,460,786 25,668,196 17,850,847 13,720,149 11,583,180 10,282,475 Current Size 9,515,878 8,909,026 8,495,039 8,116,496 7,824,764 7,563,180 7,311,729 7,104,280 6,895,122 6,739,731 ç 2x

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 Database 10g will devour as much data buffer RAM as we feed to it, based on the maximum value set by sga_target, and disk I/O will continue to decline. However, there is no tangential line that indicates a point of diminishing returns for this application. Taking the above into account, Oracle Database 10g 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. Next let’s see how Oracle Database 10g automatically adjusts storage within the shared_pool_size RAM region.

Using Oracle data buffer cache Advice The ability to compute the optimal size of the data buffers is a critical task for large databases. As databases grow to hundreds of billions of bytes, it becomes economically impractical to cache the entire database in RAM. So Oracle professionals must find the point of diminishing marginal returns for the addition of RAM resources. being able to do this can save the company hundreds of thousands of dollars in RAM expenses. Oracle9i introduces a new view, v$db_cache_advice, that can predict the benefit of additional data buffers in the data buffer cache. There has been some question about whether the data buffer cache advisory is a ratio-based tool (and hence is invaluable as the data buffer hit ratio). Metalink Note:148511.1 says that the estd_physical_read_factor is computed as the "ratio of the number of estimated physical reads to the number of reads in the real cache." See how to predict I/O reduction from a larger data cache. This v$db_cache_advice view shows the estimated miss rates for twenty potential buffer cache sizes, ranging from 10 percent of the current size to 200 percent of the current size. This allows Oracle DBAs to accurately predict the optimal size for the RAM data buffer. Let’s look at some examples to illustrate this process.

How does it work? With the data buffer set to a very small size, a small increase to the size of the RAM data buffers results in a large reduction in Disk I/O:

However, the high reduction in Disk I/O does not continue ad infinitum. As the RAM size approaches the database size, the marginal reduction in Disk I/O is smaller because all databases have infrequently accessed data.

As a general rule, all available memory on the host should be tuned, and RAM resources should be given to db_cache_size up to a point of diminishing returns. There is a point where the addition of buffer blocks will not significantly improve the buffer hit ratio, and this gives the Oracle DBA the ability to find the optimal amount of buffers.

This new DB cache advice feature is very similar to the Oracle7 utility that predicted the benefit from adding

additional data buffers. This utility used a view called x$kcbrbh to track buffer hits and the x$kcbcbh to track buffer misses. Just like the Oracle7 model, you must pre-allocate the RAM memory for the data buffers to use this functionality. The cache advice feature is enabled by setting the init.ora parameter, db_cache_advice, to the value on or ready. These values are set dynamically with the alter system command, so the DBA can turn on the predictive model while the database is running. Since you must pre-allocate the additional RAM data buffers for the db_cache_size to use v$db_cache_advice, you may only want to use this utility once to determine an optimal size. Remember, you can also use the data buffer cache hit ratio to gather similar data. Once the v$db_cache_advice is enabled and the database has run for a representative time period, the query in Listing A can be run to perform the prediction. column column column column

c1 c2 c3 c4

heading heading heading heading

'Cache Size (m)' 'Buffers' 'Estd Phys|Read Factor' 'Estd Phys| Reads'

format format format format

999,999,999,999 999,999,999 999.90 999,999,999

select size_for_estimate c1, buffers_for_estimate c2, estd_physical_read_factor c3, estd_physical_reads c4 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';

The output shows that the range of values is from 10 percent of the current size up to double the current 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

Here, you can see no peak in total Disk I/O and no marginal trends with the addition of more RAM buffers. This is very typical of data warehouse databases that read large tables with full-table scans. Consequently, there is no specific optimal setting for the db_cache_size parameter. In other words, Oracle has an insatiable appetite for data buffer RAM, and the more you give to db_cache_size, the less Disk I/O will occur.

The general rule for adding blocks to db_cache_size is simple: As long as marginal gains can be achieved from adding buffers and you have the memory to spare, you should increase the value of db_cache_size. Increases in buffer blocks increase the amount of required RAM memory for the database, and it is not always possible to hog all the memory on a processor for the database management system. So a DBA should carefully review the amount of available memory and determine the optimal amount of buffer blocks. A word of warning: When the DBA sets dba_cache_advice=on, Oracle will steal RAM pages from the shared pool RAM area, often with disastrous result to the objects inside the library cache. For example, if the existing setting for db_cache_size is 500 MB, Oracle will steal a significant amount of RAM from the shared pool. To avoid this problem, the DBA should set db_cache_advice=ready in the init.ora file. When this is done, Oracle will pre-allocate the RAM memory at database startup time. For more sophisticated Oracle databases, you can control not only the number of buffer blocks but also the block size for each data buffer. For example, you might want to make some of the buffer blocks very large so that you can minimize I/O contention. Remember, the cost for an I/O for a 32 KB block is not significantly more expensive than an I/O for 4 KB block. A database designer might choose to make specific data buffers large to minimize I/O if the application clusters records on a database block, while keeping other data blocks small.

Make a prediction With the increasing flexibility and sophistication of Oracle, the database administrator is challenged to determine the optimal sizes of all SGA regions. Making these decisions properly can mean up to millions of dollars in savings of RAM resources. The DBA cache advice facility is just one more way that the DBA can leverage upon the intelligence of the Oracle database to predict the optimal size for the RAM data caches. However, there are some imitation to the Oracle data buffer cache advisor. Setting the dba_cache_advice=on while the database is running will cause Oracle to grab RAM pages from the shared pool RAM area, and the buffer cache advisory uses this RAM in a simple simulation to help capture elapsed time data for logical I/O and physical disk reads, using different buffer sizes. While the internals of the utility are not disclosed, we see these parameters that effect the execution of the v$db_cache_advice. (Note: These may not be used within the historical data buffer reports): • • •

_db_cache_advice_batch_size _db_cache_advice_sample_factor dba_cache_advice=on or ready

First, there are several versions of the data buffer cache advisor: •

Real-time - The v$db_cache_advice utility samples data from the SGA in real-time, and creates a limited elapsed-time report from the SGA accumulators and addition memory from the dba_cache_advice settings.

•

Historical with AWR and STATSPACK - It appears that the AWR and STATSPACK data buffer advisories use the DBA_HIST and STATSPACK table data for the report.

We also see some limitations to the accuracy of the data buffer advisor because of the architecture:

•

Limited deltas - Using only two observations for logical reads and physical I/O are not enough data for a meaningful prediction. The "current workload" assumption has a wide variance, and the numbers for a one minute report will be quite different from a one hour report.

•

Limited metrics - All of the advice from the data buffer cache advisory is limited to logical I/O and physical I/O at the system-wide level.

•

Assumption of optimization? - The AWR data buffer cache advisor (and possibly the related v$db_cache_advice utility), only uses limited data points and some experts suggest that it assumes that the existing data buffer size is already optimal (the point at which the working set of frequently-used data blocks are cached, and additions to the data buffer result in marginally declining reductions in physical reads).

Hence, on the margin, the data buffer cache advisory is inaccurate for database with an undersized db_cache_size (and db_keep_cache_size, etc.). With the data buffer set to a very small size, a small increase to the size of the RAM data buffers results in a large reduction in Disk I/O.

The data buffer cache advisory may underestimate the benefits with a too-small cache

However, the high reduction in Disk I/O does not continue ad infinitum. As the RAM size approaches the database size, the marginal reduction in Disk I/O is smaller because all databases have infrequently accessed data. Using v$db_cache_advice You enable the v$db_cache_advice with the db_cache_advice parameter. The values are ready, on and off, and they can be set in the init.ora file, or dynamically enabled with an "alter system set db_cache_advice" command. Metalink Note:148511.1 says that the v$db_cache_advice view contains these columns, and the the main predictor (estimated physical read factor), is the ratio of estimated disk reads for each of the listed cache sizes: •

id - This is the ID number for the buffer pool, (values from 1 to 8).

•

name - Oracle allows for multiple data buffer pools as set by (db_cache_size, db_keep_cache_size, db_recycle_cache_size, and the instantiated blocksize buffers db_2k_cache_size, db_4k_cache_size, db_8k_cache_size, db_16k_cache_size and on non-Windows platforms, db_32k_cache_size).

•

block_size - The blocksize for the data buffer (2k, 4k, 8k, 16k and 32k).

•

advice_status - On of Off.

•

size_for_estimate - This is the baseline cache size for the prediction. (e.g. 100m)

•

buffers for estimate - The number of data buffers (e.g. 512).

•

estd_physical_read_factor - The ratio of the number of estimated physical reads to the number of reads in the real cache.

•

estd_physical_reads - This is the guess about the number of disk reads for each listed cache size (from 0.1 to 2.0).

Data buffer cache advisory usage notes When using the v$db_cache_advice report, the DBA sets db_cache_advice=on, Oracle will steal RAM pages from the shared pool RAM area, often with disastrous result to the objects inside the library cache. For example, if the existing setting for db_cache_size is 500 MB, Oracle will steal a significant amount of RAM from the shared pool. To avoid this problem, the DBA should set db_cache_advice=ready in the init.ora file, but only when using the advisory (always turn off the RAM overhead by setting (db_cache_advice=off) when your data buffer optimization is complete. When this is done, Oracle will pre-allocate the RAM memory at database startup time. Internals of the AWR report for the data buffer advisor The DBA invokes the standard awrrpt.sql in the $ORACLE_HOME/rdbms/admin directory. The awrrpt.sql script, in turn, calls awrrpti.sql, which actually pulls the report. Unfortunately, they use an internal query, so the internal machinations are hidden: select output from table(dbms_workload_repository.&fn_name( :dbid, :inst_num, :bid, :eid, :rpt_options ));

Overhead of v$db_cache_advice Metalink Note:148511.1 says that the there will be additional CPU overhead when running the data buffer cache advisor, and that the largest resource overhead would be the 100 bytes of additional RAM overhead that is applied to each buffer whenever the utility is invoked (or when you have set db_cache_advice=on or db_cache_advice= ready). "The advisory requires memory to be allocated from the shared pool (of the order of 100 bytes per buffer)."

In sum, the usefulness of a data buffer cache advisory is undisputed, but the true way to a successful predictive model is to use the scripts from my book "Oracle Tuning: The Definitive Reference". These provide a valid time-series analysis since the single delta values in the advisory are not sufficient. Using STATSPACK data for consistent gets and physical reads, statistically significant trends can be established.

How to predict I/O reduction from a larger data cache The predictive models for Oracle RAM areas began with the v$db_cache_advice utility in Oracle9i. As of Oracle9i release 2, we also see the data buffer cache advisory in STATSPACK reports, and in the AWR reports within 10g and beyond. The new 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. The utility is the foundation of the Oracle10g Automatic Memory Management (AMM) utility that monitors and adjusts the sizes of the Oracle data buffers.\ Oracle9i r2 now has three predictive utilities •

PGA advice - Oracle 9i has introduced a new advisory utility dubbed v$pga_target_advice. This utility will show the marginal changes in optimal, one-pass, and multipass PGA execution for different sizes of pga_aggregate_target, ranging from 10% to 200% of the current value.

•

Shared Pool advice - This advisory functionality has been extended in Oracle9i release 2 to include a new advice called v$shared_pool_advice, and there is talk to expending the advice facility to all SGA RAM areas in future releases of Oracle.

•

Data Cache advice - The v$db_cache_advice utility show the marginal changes in physical data block reads for different sizes of db_cache_size. Bear in mind that the data from STATSPACK can provide similar data as v$db_cache_advice, and most Oracle tuning professionals use STATSPACK and v$db_cache_advice to monitor the effectiveness of their data buffers.

Inside the buffer cache advisor These advisory utilities are extremely important for the Oracle DBA who must adjust the sizes of the RAM areas to meet current processing demands. 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. -- *********************************************************** -- Display cache advice -- *********************************************************** column c1

heading 'Cache Size (meg)'

format 999,999,999,999

select size_for_estimate c1, buffers_for_estimate c2, estd_physical_read_factor c3, estd_physical_reads c4 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';

The output from the script is shown below. Note that 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 (meg) 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

From the above listing we see that increasing the db_cache_size from 304 meg to 334 meg would result in approximately 700,000 less physical reads. This can be plotted as a 1/x function and the exact optimal point computed as the second derivative of the function:

These advisory utilities are very important for the Oracle DBA who must adjust their SGA regions to meet current processing demands. Remember, SGA tuning is an iterative process and busy shops continually monitor and adjust the size of their data cache, PGA and shared pool. However, there are some imitation to the Oracle data buffer cache advisor. Setting the dba_cache_advice=on while the database is running will cause Oracle to grab RAM pages from the shared pool RAM area, and the buffer cache advisory uses this RAM in a simple simulation to help capture elapsed time data for logical I/O and physical disk reads, using different buffer sizes. While the internals of the utility are not disclosed, we see these parameters that effect the execution of the v$db_cache_advice. (Note: These may not be used within the historical data buffer reports): • • •

_db_cache_advice_batch_size _db_cache_advice_sample_factor dba_cache_advice=on or ready

First, there are several versions of the data buffer cache advisor:

•

Real-time - The v$db_cache_advice utility samples data from the SGA in real-time, and creates a limited elapsed-time report from the SGA accumulators and addition memory from the dba_cache_advice settings.

•

Historical with AWR and STATSPACK - It appears that the AWR and STATSPACK data buffer advisories use the DBA_HIST and STATSPACK table data for the report. The awrrpt.sql script (used to generate the report) calls awrrpti.sql, which actually pulls the report. Unfortunately, they use an internal query, so the internal machinations are hidden: select output from table(dbms_workload_repository.&fn_name( :dbid, :inst_num, :bid, :eid, :rpt_options ));

We also see some limitations to the accuracy of the data buffer advisor because of the architecture: •

Limited deltas - Using only two observations for logical reads and physical I/O are not enough data for a meaningful prediction.

•

Limited metrics - All of the advice from the data buffer cache advisory is limited to logical I/O and physical I/O at the system-wide level.

•

Assumption of optimization? - Some suggest that the v$db_cache_advice utility assumes that the existing data buffer size is optimal, the point at which the working set of frequently-used data blocks are cached, and additions to the data buffer result in marginally declining reductions in physical reads.

Hence, on the margin, the data buffer cache advisory is inaccurate for database with an undersized db_cache_size (and db_keep_cache_size, etc.). With the data buffer set to a very small size, a small increase to the size of the RAM data buffers results in a large reduction in Disk I/O.

The data buffer cache advisory may underestimate the benefits with a too-small cache

However, the high reduction in Disk I/O does not continue ad infinitum. As the RAM size approaches the database size, the marginal reduction in Disk I/O is smaller because all databases have infrequently accessed data.

The data buffer cache advisory does not know that a cache is oversized

In sum, the usefulness of a data buffer cache advisory is undisputed, but the true way to a successful predictive model is to use the scripts from my book "Oracle Tuning: The Definitive Reference". These provide a valid time-series analysis since the single delta values in the advisory are not sufficient. Using STATSPACK data for consistent gets and physical reads, statistically significant trends can be established. See my related notes on v$db_cache_advice: