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

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Moving Toward a Self-Tuning
Oracle Database

Oracle Tips by Burleson

With these new dynamic SGA features in Oracle, we are moving toward an architecture where the Oracle DBA can monitor UNIX RAM memory usage and reconfigure the SGA and PGA regions according to existing usage patterns.

Oracle offers a degree of self-tuning capability with the new pga_aggregate_target parameter. By allowing Oracle to manage RAM memory demands according to the demands of each task, Oracle has been able to use sophisticated algorithms to improve the speed of RAM intensive tasks such as hash joins and large sorts.

However, the Oracle DBA is now able to dynamically deallocate RAM memory from one area and reallocate the RAM to another area of the SGA.

Changing RAM Configuration with UNIX Scripts

In a UNIX environment, it is very easy to schedule a task to change the RAM memory configuration when the processing needs change. For example, many Oracle databases operate in OLTP mode during normal work hours, and the database services memory-intensive batch reports at night.

As we have noted, an OLTP database should have a large value for db_cache_size. Conversely, memory-intensive batch tasks require additional RAM in the pga_aggregate_target.

The UNIX scripts that follow can be used to reconfigure the SGA between OLTP and DSS without stopping the instance. In this example, we assume that you have an isolated Oracle server with 8 gigabytes of RAM. We also assume that you reserve 20 percent of RAM for UNIX overhead, leaving a total of 6 gigabytes for Oracle and Oracle connections. These scripts are for HP-UX or Solaris, and accept the $ORACLE_SID as an argument.

The dss_config.ksh script will be run at 6:00 p.m. each evening to reconfigure Oracle for the memory-intensive batch tasks that run each night:



# First, we must set the environment . . . .
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
#ORACLE_HOME=`cat /var/opt/oracle/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
export PATH

$ORACLE_HOME/bin/sqlplus –s /nologin<<!
connect system/manager as sysdba;
alter system set db_cache_size=1500m;
alter system set shared_pool_size=500m;
alter system set pga_aggregate_target=4000m;

The oltp_config.ksh script will be run at 6:00 a.m. each morning to reconfigure Oracle for the OLTP usage during the day:



# First, we must set the environment . . . .
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
#ORACLE_HOME=`cat /var/opt/oracle/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
export PATH

$ORACLE_HOME/bin/sqlplus –s /nologin<<!
connect system/manager as sysdba;

alter system set db_cache_size=4000m;
alter system set shared_pool_size=500m;
alter system set pga_aggregate_target=1500m;


Note: You can also use the dbms_job package to schedule these types of reconfiguration events.

Now that you see a generic way to change the Oracle configuration, it should be clear that you can develop a mechanism to constantly monitor the processing demands on Oracle and issue the alter system commands according to existing database demands.

Approaches to Self-tuning Oracle Databases

Until Oracle evolves into a complete self-tuning architecture, the Oracle DBA is responsible for adjusting the RAM memory configuration according to the types of connections. In general, you can use queries against the v$ structures and STATSPACK to locate those times when Oracle connections change their processing characteristics. There are three types of approaches to automated tuning:

  • Normal scheduled reconfiguration A bimodal instance that performs OLTP and DSS during regular hours will benefit from a scheduled task to reconfigure the SGA and PGA.

  • Trend-based dynamic reconfiguration You can use STATSPACK to predict those times when the processing characteristics change and use the dbms_job package to fire ad-hoc SGA and PGA changes.

  • Dynamic reconfiguration Just as Oracle dynamically redistributes RAM memory for tasks within the pga_aggregate_target region, the Oracle DBA can write scripts that steal RAM from an underutilized area and reallocate these RAM pages to another RAM area.

Rules for Changing Memory Sizes

There are three conditions that affect the decision to resize the Oracle RAM regions: one for the data buffer cache, another for the shared pool, and the third for PGA memory usage.

  • db_cache_size You may want to add RAM to the data buffer cache when the data buffer hit ratio falls below a predefined threshold.

  • shared_pool_size A high value for any of the library cache miss rations may signal the need to allocate more memory to the shared pool.

  • pga_aggregate_target When you see high values for multi-pass executions, you may want to increase the available PGA memory.

Let’s take a close look at each of these conditions.

Adjusting the pga_aggregate_target Parameter

You may want to dynamically change the pga_aggregate_target parameter when any one of the following conditions is true:

  • Whenever the value of the v$sysstat statistic “estimated PGA memory for one-pass” exceeds pga_aggregate_target, you want to increase pga_aggregate_target.

  • Whenever the value of the v$sysstat statistic “workarea executions – multipass” is greater than 1 percent, the database may benefit from additional RAM memory.

  • It is possible to over-allocate PGA memory, and you may consider reducing the value of pga_aggregate_target whenever the value of the v$sysstat row “workarea executions—optimal” consistently measures 100 percent.

Changing the shared_pool_size Parameter

We all know from Oracle8 that Oracle offers several queries for determining when the Oracle shared pool is too small. The library cache miss ratio tells the DBA whether to add space to the shared pool, and it represents the ratio of the sum of library cache reloads to the sum of pins.

In general, if the library cache ratio is over 1, you should consider adding to the shared_pool_size. Library cache misses occur during the parsing and preparation of the execution plans for SQL statements. The compilation of an SQL statement consists of two phases: the parse phase and the execute phase. When the time comes to parse an SQL statement,

Oracle first checks to see if the parsed representation of the statement already exists in the library cache. If not, Oracle will allocate a shared SQL area within the library cache and then parse the SQL statement. At execution time, Oracle checks to see if a parsed representation of the SQL statement already exists in the library cache. If not, Oracle will reparse and execute the statement.

The following STATSPACK script will compute the library cache miss ratio. Note that the script sums all of the values for the individual components within the library cache and provides an instance-wide view of the health of the library cache.


set lines 80;
set pages 999;

column mydate heading 'Yr.  Mo Dy  Hr.' format a16
column c1 heading "execs"    format 9,999,999
column c2 heading "Cache Misses|While Executing"    format 9,999,999
column c3 heading "Library Cache|Miss Ratio"     format 999.99999

break on mydate skip 2;

   to_char(snap_time,'yyyy-mm-dd HH24')  mydate,
   sum(new.pins-old.pins)                c1,
   sum(new.reloads-old.reloads)          c2,
   sum(new.pins-old.pins)                library_cache_miss_ratio
   stats$librarycache old,
   stats$librarycache new,
   stats$snapshot     sn
   new.snap_id = sn.snap_id
   old.snap_id = new.snap_id-1
   old.namespace = new.namespace
group by
   to_char(snap_time,'yyyy-mm-dd HH24')

Here is the output. This report can easily be customized to alert the DBA when there are excessive executions or library cache misses.

                               Cache Misses
Mo Dy  Hr.       execs While Executing LIBRARY_CACHE_MISS_RATIO
---------------- ---------- --------------- ------------------------
2001-12-11 10        10,338               3                   .00029
2001-12-12 10       182,477             134                   .00073
2001-12-14 10       190,707             202                   .00106
2001-12-16 10         2,803              11                   .00392

Once this report identifies a time period where there may be a problem, STATSPACK provides the ability to run detailed reports to show the behavior of the objects within the library cache. In the preceding example, you see a clear RAM shortage in the shared pool between 10:00 a.m. and 11:00 a.m. each day. In this case, you could dynamically reconfigure the shared pool with additional RAM memory from the db_cache_size during this period.

Adjusting the Data Buffer Cache Size

The following STATSPACK report alerts the DBA to those times when the data buffer hit ratio falls below the preset threshold. It is very useful for locating times when decision support type queries are being run, since a large number of large-table full-table scans will make the data buffer hit ratio drop. This script also reports on all three data buffers, including the KEEP and RECYCLE pools, and it can be customized to report on individual pools. 

Remember, the KEEP pool should always have enough data blocks to cache all table rows, while the RECYCLE pool should get a very low buffer hit ratio, since it seldom re-reads data blocks. If the data buffer hit ratio is less than 90 percent, you may want to increase db_cache_size (db_block_buffers in Oracle8i and earlier).

When the data buffer hit ratio falls below 90%, you
should consider adding to the db_cache_size parameter

yr.  mo dy Hr.   Name    bhr
------------- --------  -----
2001-01-27 09 DEFAULT    45
2001-01-28 09 RECYCLE    41
2001-01-29 10 DEFAULT    36
2001-01-30 09 DEFAULT    28
2001-02-02 10 DEFAULT    83
2001-02-02 09 RECYCLE    81
2001-02-03 10 DEFAULT    69
2001-02-03 09 DEFAULT    69

Here you will note those times when you might want to dynamically increase the value of the db_cache_size parameter. In the case of the preceding output, you could increase the db_cache_size each day between 8:00 a.m. and 10:00 a.m., stealing RAM memory from pga_aggregate_target.

Using the Oracle v$db_cache_advice View

Starting in Oracle 9i, there is a new view that can predict the benefit of additional data buffers in the data buffer cache. This view shows the estimated miss rates for 20 potential buffer cache sizes, ranging from 10 percent of the current size to 200 percent of the current size.

This new feature is very similar to the Oracle7 utility to predict 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 preallocate the RAM memory for the data buffers in order to use this Oracle utility. The cache advice feature is enabled by setting the Oracle parameter db_cache_advice to the values of ON or READY. These values can be set dynamically with the alter system command, so the DBA can turn on the predictive model while the database is running.

Caution: When the DBA sets dba_cache_advice=on, Oracle will steal RAM pages from the shared pool, often with disastrous result to the library cache. For example, if the existing setting for db_cache_size is 500m, 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 Oracle file. When this is done, Oracle will preallocate the RAM memory at database startup time.

Once the db_cache_advice is enabled and the database has run for a representative time period, the following query can be run to perform the prediction:

column size_for_estimate    
   format 999,999,999,999
   heading 'Cache Size (m)'
column buffers_for_estimate 
   format 999,999,999
   heading 'Buffers'
column estd_physical_read_factor
   format 999.90
   heading 'Estd Phys|Read Factor'
column estd_physical_reads      
   format 999,999,999
   heading 'Estd Phys| Reads'

   name = 'DEFAULT'
   block_size  = (SELECT value FROM V$PARAMETER
                   WHERE name = 'db_block_size')
   advice_status = 'ON';

Here is the output from this script. Note 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.

As a general rule, all available memory on the host should be tuned, and Oracle should be given to db_cache_size up to a point of diminishing returns (see Figure 9-23). There is a point where the addition of buffer blocks will not significantly improve the buffer hit ratio, and these tools give the Oracle DBA the ability to find the optimal amount of buffers.

Figure 9-77: Determining the optimal db_cache_size

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 of the memory on a processor for the database management system. Therefore, a DBA should carefully review the amount of available memory and determine the optimal amount of buffer blocks.

Tip: Since you must preallocate the additional RAM data buffers for the db_cache_size to use 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.

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 32KB block is not significantly more expensive than an I/O for a 4KB 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.

When to Trigger a Dynamic Reconfiguration

When your scripts detect a condition where a RAM memory region is overstressed, you are faced with a choice about which region will shrink to provide the RAM for the overstressed area. Table 9-3 provides the threshold condition for triggering a dynamic memory change.

RAM Area

Overstressed Condition

Overallocated Condition

Shared pool

Library cache misses

No misses

Data buffer cache

Hit ratio < 90%

Hit ratio > 95%

PGA aggregate

High multi-pass executions

100% optimal executions

Table 9-5: Threshold Conditions for Dynamic RAM Reallocation

In practice, the choice of which area to reduce in size is a choice between the shared pool and the PGA aggregate memory (see Figure 9-24). This is because the shared pool is almost always a small region when compared to the regions for the data buffers and PGA session memory.

Now that we understand Oracle dynamic tuning, let’s conclude this chapter with a review of the major topics and points.

Get the Complete
Oracle Tuning Details 

The landmark book "Oracle Tuning: The Definitive Reference Second Edition" has been updated with over 1,150 pages of expert performance tuning tips. It's packed with scripts and tools to hypercharge Oracle 11g performance and you can buy it for 40% off directly from the publisher.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



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

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter