Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Dynamic Adjustment of the Oracle SGA Pools

Oracle Tips by Burleson Consulting
December 16,  2008


The ability to dynamically reconfigure Oracle was the most exciting new feature of the new version's release, propelling Oracle into the world of true 24/7 systems for shops that need constant availability. But there's another important benefit to dynamic parameters. Because all 250 Oracle parameters can now be changed in real time, Oracle DBAs can reconfigure the database at any time, and many savvy Oracle administrators are adjusting their databases to match current processing load.

Prior to Oracle9i, Oracle administrators could use real-time performance monitors and detect performance problems. However, Oracle8i DBAs were sometimes powerless to make a change unless they were allowed to stop and restart the database. In Oracle9i, administrators have more options. When a DBA detects a performance problem, he or she can immediately correct the glitch using Oracle9i alter system commands. For example, if the library cache/hit ratio falls too low, a DBA can dynamically add RAM memory pages to the shared pool to correct the problem, all without affecting the database's availability.

As you can imagine, dynamic parameters can solve a host of performance problems. Let's take a close look at how you can use Oracle9i dynamic parameters to resolve performance issues.

The foundation for a self-tuning database

Because a change to Oracle parameters can affect all aspects of an Oracle instance, Oracle has built the foundation of a self-tuning database architecture. For example, if you're using the Oracle STATSPACK utility to track Oracle statistics, you can develop predictive models based on your historical data and predict when your database will experience a shortage of a specific Oracle resource. Your STATSPACK reports may indicate that your buffer cache/hit ratio falls below acceptable levels every Wednesday afternoon between 1 P.M. and 3 P.M., as illustrated in Figure A.

Figure A
A predictive model for proactive dynamic Oracle tuning

Using the Oracle9i dynamic System Global Area (SGA) feature, you can dynamically "steal" RAM pages from another RAM memory region of the Oracle instance (referred to as the SGA) and dynamically reallocate the RAM page frames to the data buffer cache every Wednesday during the crunch period. Ultimately, the Oracle9i dynamic SGA features will be incorporated with real-time performance monitors that suggest a dynamic correction for a performance problem. For example, software could be written that detects impending changes in data access patterns and then dynamically reconfigure the Oracle database to accommodate those changes.

Dynamic bimodal databases

Almost all Oracle databases have a distinct processing signature, which is modeled by hour of the day or day of the week; these processing signatures exist for disk I/O, CPU consumption, data buffer behavior, and shared pool activity. Essentially, these signatures can be combined to define specific "modes" of processing that are clearly identifiable and attributable to specific application requirements.

For example, a system could be operating in online transaction processing (OLTP) mode during the day and then switch to data-warehouse and decision-support modes each evening. If an Oracle DBA can detect when the system's processing characteristics change from OLTP to DSS, he or she can dynamically submit batch jobs that issue the alter system commands to reconfigure the SGA and background processes.

Future Oracle releases will most likely incorporate artificial intelligence to create a true self-tuning database. However, don't underestimate the challenge and complexity of creating a self-tuning database engine. Oracle has 250 parameters that interact with each other in a factorial fashion; there are literally many billions of unique processing scenarios. Any effort to develop a program to dynamically monitor and reconfigure Oracle will be a formidable software engineering challenge.

What can I do today?

Without getting into the minutiae of Oracle tuning parameters, you can monitor and change several critical metrics whenever they fall below predefined thresholds. The following recommendations require the STATSPACK utility to identify the values. The book Oracle9i High-Performance Tuning with STATSPACK, by Oracle Press, has great prewritten scripts to detect exceptional Oracle conditions.

Let's examine the major thresholds that can be used to trigger signal dynamic reconfiguration:

Hot file reads
You can use STATSPACK to identify files whose reads are greater than 25 percent of total database reads. Internally, the code compares the individual I/O for a data file from the stats$filestatxs table with the overall I/O for the period in the stats$sysstat table. When you find a hot file, you can locate the relevant table or index inside the file and dynamically place the table of index in the KEEP pool with this syntax:
Alter table customer storage (buffer_pool keep);

Hot file writes
A STATSPACK script alerts you to files whose write I/Os are greater than (25 percent or 50 percent or 75 percent) of total writes. This information can help you locate files that are consuming more than a normal portion of I/O writes. You may want to place these files in the KEEP pool or stripe them across multiple disks.

Data buffer hit ratio

2007 Update:  Since the days of Oracle7 when Oracle Corporation recommended keeping the buffer cache hit ratio above a fixed threshold, research has show some important facts. 

Please read: Is the Oracle buffer hit ratio a useless metric for monitoring and tuning?

You can use a STATSPACK script to alert you when the data buffer hit ratio falls below the preset threshold. This alert is useful for identifying times when decision-support type queries are being run, since a lot of large-table/full-table scans and random data access may make the data buffer hit ratio drop.

This script can also reports on all three data buffers, including the KEEP and RECYCLE pools. It can also be customized to report on individual pools, because 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. See here for scripts to automate the KEEP pool size to insure a 100% cache.  The syntax is:
-- steal RAM from the shared_pool;
Alter system set shared_pool_size = xxxx;
Alter system set db_cache_size = xxxx; - new, higher value


Here is the corresponding alert log message:
Wed Jul 31 16:30:47 2002
ALTER SYSTEM SET db_cache_size='2048000' SCOPE=BOTH;
Wed Jul 31 16:31:21 2002
CKPT: Begin resize of buffer pool 7 (DEFAULT for block size 16384)
CKPT: Current size = 16 MB, Target size = 8 MB
CKPT: Resize completed for buffer pool DEFAULT for blocksize 16384

Disk sorts

If the number of disk sorts is greater than 1,000 an hour, you may want to increase the value of the sort_area_size parameter or tune SQL to perform index scans instead of a disk sort. This report is very useful for monitoring the amount of activity against the TEMP tablespace, and it also helps ensure that sort_area_size is set to an optimal level. As a general rule, increasing sort_area_size will reduce the number of disk sorts, but huge sorts will always need to be performed on disk in the TEMP tablespace. The syntax for the command is:
Alter system set sort_area_size = 10m;

Buffer busy wait alert

Whenever you see buffer busy waits, a data block is in the data buffer but is unavailable. This type of contention is usually for a segment header block of a high-level index node block. Adding freelists for the object often corrects such buffer busy wait conditions. The syntax is:
Alter table customer storage ( freelists 10 );

Library cache misses alert

This alert interrogates the stats$sysstat table to look for excessive library cache miss ratios. When the library cache/miss ratio is greater than .02, you may want to increase shared_pool_size. The syntax is:
Alter system set shared_pool_size = 100m;

Database writer contention alert

This alert looks at Oracle for values in summed dirty queue length, write requests, and DBWR checkpoints. When the write request length is greater than 3 or your DBWR checkpoint waits, you need to look at tuning the database writer processes by changing the number of factotum (slave) processes. The syntax is:
Alter system set dbwr_io_slaves = 10;

Data dictionary miss ratio

This alert looks at the Oracle data dictionary to compute data dictionary gets, data dictionary cache misses, and the data dictionary hit ratio, and then alerts the DBA to times when requests for data dictionary metadata are high. This problem can sometimes be relieved by increasing the shared_pool_size init.ora parameter, using the following syntax.
Alter system set shared_pool_size = 100m;

Also see Scheduling Oracle reconfiguration tips


As Oracle evolves, there will be many attempts to exploit this powerful new dynamic reconfiguration feature, with the ultimate goal of creating a self-tuning database engine. However, because Oracle is very flexible, it's also one of the most complex databases ever created. Eventually, artificial intelligence will be developed to automatically tune Oracle, but today, the Oracle professional must develop a strategy for detecting and correcting real-time performance problems.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.