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 









Automatic Shared Memory Management

Oracle RAC Cluster Tips by Burleson Consulting

This is an excerpt from the bestselling book Oracle Grid & Real Application Clusters.  To get immediate access to the code depot of working RAC scripts, buy it directly from the publisher and save more than 30%.

Oracle 10g, with its emphasis on self-management and self-tuning, introduces many features that operate automatically, as well as in an advisory role. Since the values for the SGA components are fixed at instance start time, they must be used as they are during the instance runtime, with some exceptions.

Often it happens that a certain component?s memory pool is never used and it is not available for another component, which is in need of extra memory. Under-sizing can lead to poor performance and out-of memory errors (ORA-4031), while over-sizing can waste memory.

With the Database 10g, the Automatic Shared Memory Management feature can be employed. This feature enables the Oracle database to automatically determine the size of each of these memory components within the limits of the total SGA size. This solves the allocation issues that are normally faced in a manual method.

This feature enables the specification of a total memory amount to be used for all SGA components. The Oracle Database periodically redistributes memory between the components according to workload requirements.

Use of the sga_target initialization parameter configures automatic Shared Memory Management. If a non-zero value is specified for sga_target, the following four memory pools are automatically sized by Oracle.

* Database Buffer cache (Only the Default pool)

* Shared Pool

* Large Pool

* Java Pool

If sga_target is set to the default value of 0, Automatic Shared Memory Management is disabled. When sga_target is not set, or equal to the default value of 0, auto-tuned SGA parameters behave as in previous releases of the Oracle database. In Oracle 10g, the initialization parameters such as db_cache_size, shared_pool_size, large_pool_size, and java_pool_size are referred to as auto-tuned SGA parameters.

Figure 4.4 shows an example of auto tuned SGA components.

Figure 4.4:  SGA Memory Buffers managed dynamically

Configuration of the following buffers still remains manual and they are now referred to as manually-sized components:

* Log Buffer

* Other Buffer Caches (KEEP/RECYCLE, other block sizes)

* Streams Pool (Newly introduced in Oracle Database 10g)

* Fixed SGA and other internal allocations

The user specifies manual SGA parameters, and parameter sizes precisely control the size of their corresponding components. When sga_target is set, the total size of manual SGA parameters are subtracted from the sga_target value and the balance is given to the auto-tuned SGA components.

sga_target is also a dynamic parameter and can be changed through Enterprise Manager or with the ALTER SYSTEM command. However, the sga_target can be increased only up to the value of sga_max_size.

The question may arise, how is auto management possible? A new background process named Memory Manager (MMAN) manages the automatic shared memory. MMAN serves as the SGA Memory Broker and coordinates the sizing of the memory components.

Important: statistics_level must be set to TYPICAL (default) or ALL to use Automatic Shared Memory Management.

Program Global Area (PGA)

A program global area (PGA) is a memory region that stores the data and control information for the server processes. Each Server process has a non-shared memory region created by Oracle when a server process is started. Access to the PGA is exclusive to that server process and it is read and written only by Oracle code. Broadly speaking, PGA contains a private SQL area and a Session memory area.

A private SQL area contains data such as bind information and runtime memory structures. Each session that issues an SQL statement has a private SQL area.

Note that the Location of a private SQL area depends on the type of connection established for a session. If a session is connected through a dedicated server, private SQL areas are located in the server process? PGA. However, if a session is connected through a shared server, part of the private SQL area is kept in the SGA.

Session memory is the memory allocated to hold a session?s variables (logon information) and other information related to the session. For a shared server, the session memory is shared and not private.

With the initialization parameter pga_aggregate_target, sizing of work areas for all dedicated sessions is made automatic and all *_area_size parameters are ignored for these sessions. At any given time, the total amount of PGA memory available to active work areas on the instance is automatically derived from the pga_aggregate_target parameter.


This is an excerpt from the bestselling book Oracle Grid & Real Application Clusters, Rampant TechPress, by Mike Ault and Madhu Tumma.

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


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 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational