Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

Viewing Information about SGA Performance

The following Oracle Database10g views provide information about the SGA components and their dynamic resizing:

 

VIEW

DESCRIPTION

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.

Table 14.2: Oracle Database 10g Views

 

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.

 

column c1   heading 'Cache Size (m)'        format 999,999,999,999

column c2   heading 'Buffers'               format 999,999,999

column c3   heading 'Estd Phys|Read Factor' format 999.90

column c4   heading 'Estd Phys| Reads'      format 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.  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.

 

Figure 14.3: A plot from the output of 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.

 

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

 

 

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 
 
email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.