Oracle9i Dynamic RAM and UNIX Administration

Oracle9i 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 Oracle9i 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. 

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

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 data and DSS mode at night, the DBA can run a script to steal RAM from the SGA and re-allocate this RAM memory to the PGA region:

alter system set sga_memory_max = 3g;
alter system set pga_aggregate_target = 3g;

Starting in Oracle9i, 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.

Oracle9i and UNIX granules

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

In Oracle9i, 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: At this time, Oracle9i does not support 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 Oracle9i 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, Oracle9i provides alter system commands to allow the DBA to change the configuration of the Oracle9i 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 Oracle9i 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 se 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 Oracle9i manages dynamic RAM memory in a UNIX environment.