Oracle Dynamic RAM and UNIX

Prior to Oracle9i, the Oracle DBA could only control UNIX memory for Oracle at database start time.  Oracle provided several init.ora 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 re-starting 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 upon the existing demands on the Oracle database. 

Oracle has also introduced a new RAM memory management technique whereby the DBA can pre-allocate 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% of the RAM memory for UNIX overhead (Figure 7-1).

Figure 1: Pre-allocation of SGA and PGA memory in Oracle

Prior to Oracle9i, it was not uncommon for the Oracle DBA to have several copies of their init.ora parameter file, and then “bounce” the database daily to re-configure 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 (Figure 7-2).

Figure 2: 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 a Oracle data warehouse.  OLTP system 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.

Table 1: RAM memory changes depending on type of application

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

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.

Oracle and UNIX Granules

Starting with Oracle9i, Oracle expanded 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 init.ora parameter, then Oracle is free to allocate more granules until the SGA size reaches the sga_max_size limit.

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 (Figure 7-3).

Figure 3: Dynamic memory allocation

NOTE: Early versions of Oracle 9i did not allow dynamic modification of the large_pool_size parameter and the sga_max_size parameter.

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

This query gives us 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 output below is an example of a change which has been rejected by Oracle because there is insufficient UNIX memory to expand the selected pool

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

Now that we see how we 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.