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




Oracle Dynamic RAM and  UNIX

Oracle Tips by Burleson

Prior to Oracle, the Oracle DBA could only control UNIX memory for Oracle at database start time. Oracle provided several Oracle parameters to determine the RAM size of the SGA, and once the database was started, the SGA size and configuration could not be modified.

The movement of Oracle toward a 24x7 database has created the need for the Oracle DBA to adjust the size of the UNIX memory regions without stopping and restarting the database. More important, the dynamic SGA features of Oracle allow the DBA to monitor RAM memory usage within the SGA and adjust the SGA memory regions based on the existing demands on the Oracle database.

Oracle has also introduced a new RAM memory management technique whereby the DBA can preallocate all PGA memory and allow Oracle to distribute the RAM memory to connections according to the sorting demands of the connections. This is a radical departure from traditional Oracle databases, and it has made the sort_area_size and other PGA parameters obsolete. Also, it is no longer necessary to issue alter session commands to change the sort_area_size for connections that require a large sort area.

Rather than allocate just the SGA, the Oracle DBA must fully allocate all of the RAM memory on the UNIX Oracle server, reserving 20 percent of the RAM memory for UNIX overhead (see Figure 9-18).

Figure 9-72: Preallocation of SGA and PGA memory in Oracle

Prior to Oracle, it was not uncommon for the Oracle DBA to have several copies of their Oracle parameter file, and then “bounce” the database daily to reconfigure the SGA for different processing modes. For example, the allocations of an SGA for online transaction processing (OLTP) is quite different than the processing mode for an Oracle data warehouse (see Figure 9-19).

Figure 9-73: Comparison of RAM for DSS and OLTP applications

Oracle recommends a different RAM memory configuration for OLTP databases and decision support applications (DSS) such as an Oracle data warehouse (Table 1). OLTP systems should allocate the majority of total UNIX RAM to the SGA, while data warehouse and DSS applications that are RAM memory intensive should allocate the majority of RAM for PGA connections.

Processing Mode



UNIX RAM Overhead





Data warehouse and DSS




Table 9-4: RAM Memory Changes Depending on Type of Application

Of course, if a single database operates in a dual modality, the Oracle DBA can run a script to dynamically change this memory allocation. For example, if a database runs in OLTP mode during the day and DSS mode at night, the DBA can run a script to steal RAM from the SGA and reallocate this RAM memory to the PGA region:

alter system set pga_aggregate_target = 3g;

Starting in Oracle, Oracle has provided the ability to grow or shrink the following components of the SGA RAM memory:

  • Data buffer size alter system set db_cache_size=300m;

  • Shared pool size alter system set shared_pool_size=200m;

  • Total PGA RAM memory size alter system set pga_aggregate_target=2000m;

In UNIX, Oracle achieves the dynamic memory allocation by modifying the physical address space inside the UNIX memory region. This is done in UNIX by issuing malloc() and free() commands.

The new dynamic SGA features also allow the Oracle SGA to start small and grow on an as-needed basis. A new parameter called sga_max_size has been created to facilitate this process.

On many platforms the sga_max_size is totally allocated on startup with the excess going into the miscellaneous section of the shared pool.  In order to reallocate this memory, it must be deallocated from the shared pool and then reallocated.

Oracle and UNIX Granules

Starting with Oracle, Oracle expands the term granule to include a reserved region of RAM memory for SGA growth. A memory granule should not be confused with Oracle parallel query block range partition granules. Block range partition granules are used by OPQ to determine the block ranges for parallel query slaves, while a RAM memory granule is a unit of contiguous virtual memory allocation. If the current amount of SGA memory is less than the value of the sga_max_size Oracle parameter, Oracle is free to allocate more granules until the SGA size reaches the sga_max_size limit.

In Oracle, the DBA “reserves” granules for use by the dynamic SGA feature. When the DBA issues an alter system command to increase the size of a RAM memory region of the SGA, Oracle passes the command to a background process that allocates the RAM memory from the reserved space, adding the memory to the desired SGA component (see Figure 9-20).

Figure 9-74: Dynamic memory allocation

Note: At this time, Oracle does not support dynamic modification of the large_pool_size parameter or the sga_max_size parameter.

The v$process view can be used to display the existing values for the new Oracle parameters:

column name format 999,999,999,999

   name in

    NAME                  VALUE
    --------------------  -------------
    shared_pool_size         40,362,826
    sga_max_size          5,392,635,193
    large_pool_size           1,048,576
    db_cache_size             4,194,304
    pga_aggregate_target  2,403,628,363

This query gives you the current values of the dynamic memory parameters and allows the DBA to compare existing demands with current instance settings.

Changing Dynamic SGA and PGA Components

As we have noted, Oracle provides alter system commands to allow the DBA to change the configuration of the Oracle RAM memory. Before we look at using these features for automatic tuning, let’s briefly review the main parameters and see how they operate. The following output is an example of a change that has been rejected by Oracle because there is insufficient UNIX memory to expand the selected pool:

SQL> alter system set shared_pool_size=64m;
alter system set shared_pool_size=64m
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool

In the case of the preceding example, the error was generated because the command would have exceeded the value of the sga_max_size parameter. In the example that follows, you expand the shared pool to 300 megabytes, and we can immediately confirm the change with the show parameters command:

SQL> connect system/manager as sysdba;
SQL> alter system set shared_pool_size=300m;

System altered.

SQL> show parameter shared_pool

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
shared_pool_reserved_size            big integer 1258291
shared_pool_size                     big integer 33554432

Now that you see how you can alter the individual SGA components, let’s move on and take a look at how Oracle manages dynamic RAM memory in a UNIX environment.

This is an excerpt from "Oracle High Performance tuning with STATSPACK" by Oracle Press.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

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


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