Using the Dynamic SGA Features of Oracle9i

by Donald K. Burleson

While the Oracle9i database introduced a huge amount of significant internal enhancements, one of the most exciting for the Oracle DBA is the ability to dynamically reset all of Oracle's SGA control parameters. Unlike Oracle8i, in which the initialization parameters were kept inside a flat file to be read at database startup time, Oracle9i opens up a whole new world of possibilities because all of the Oracle parameters can be reset dynamically using ALTER DATABASE and ALTER SYSTEM commands.

Prior to Oracle9i, the Oracle administrator would have to shut down reconfigure the INIT.ORA parameters and restarted database instance whenever significant processing patterns changed within the Oracle database. This type of reconfiguration is commonly done in Oracle databases that operated in OLTP mode during the online day, and then switched to a data warehouse mode for evening processing.

This requirement to stop and restart the Oracle database to change the parameters is a significant enhancement to Oracle9i, and one that makes continuous availability an easy goal.

This ability to dynamically grow and shrink different areas within the Oracle SGA offers some exciting new possibilities for the Oracle database administrator. Database activity within each region of the SGA can be monitored individually, and resources can be allocated and deleted according the usage patterns within the Oracle database.

Let's begin by taking a look at the differences between the Oracle9i database and the Oracle8i database. One of the most significant enhancements to Oracle9i is the elimination of the need to have a separate PGA area for all dedicated connections to the Oracle database. As we may remember from Oracle8i, dedicated Oracle connections were required to allocate a separate area within the RAM memory called a Program Global Area, or PGA. This PGA region contains the SORT_AREA_SIZE and additional RAM control structures that were used to maintain the state for the connected task. In Oracle 9i, the PGA region has been replaced by a new RAM region inside the Oracle SGA as specified by the PGA_AGGREGATE_TARGET parameter (refer to figure 1).

Figure 1: RAM allocation differences between Oracle8i and Oracle9i

Because all RAM memory usage is now handled completely within the Oracle SGA, the Oracle database administrator can fully allocate the Oracle server's RAM memory, up to 80 percent of the total RAM on the Oracle server. Oracle recommends that 20 percent of the RAM memory on a database server be reserved for operating system tasks.

As users connect to the Oracle9i database, RAM for their sort work areas are allocated within the PGA_AGGREGATE_TARGET region of Oracle9i. This allows Oracle9i perform far faster than Oracle8i because the memory is only allocated in used for the duration of the session upon which is immediately freed up to become available for use by other connected Oracle tasks.

Dynamically Changing the SGA Regions

Given that the Oracle administrator now has the ability to grow and shrink all of the areas of the SGA, it might be beneficial to take a quick look at what these SGA areas look like and describe how the Oracle DBA can monitor their usage to most effectively reallocate the RAM memory for the Oracle database. The areas of the SGA are broken into the following categories.

Data buffers -- Oracle9i has up to seven separate and distinct data buffers to hold incoming data blocks from the disks. These include the traditional KEEP pool, the RECYCLE pool, the DEFAULT pool, as well as separate data buffer pools for each blocksize supported by the Oracle database (2K, 4K, 8K, 16K, and 32K) (refer to figure 2).

Figure 2: The separate Oracle9i data buffers

We can monitor the data buffer hit ratios for each one of the seven data buffer areas, and if the buffer hit ratio remains consistently about 90 percent, we can steal memory pages from these RAM data buffers and reallocate RAM into other areas of the Oracle instance that require additional memory.

When a Data Buffer Hit Ratio (DBHR) falls, we can de-allocate RAM from one data buffer and reallocate it to another data buffer (refer to figure 3).

Figure 3: Re-allocating RAM between Oracle9i data buffers

Shared pool -- The Oracle 9i shared pool serves a very important function for the parsing and execution of Oracle SQL statements. High library cache misses often indicate a shortage of RAM within the library cache, and the Oracle 9i database administrator can issue ALTER SYSTEM commands to add additional memory to the shared pool when the shared pool has become overly stressed by parsing and executing SQL statements.

PGA area - The RAM allocated to the PGA_AGGREGATE_TARGET is used by Oracle connections to maintain connection-specific information (e.g., cursor states) and to sort Oracle SQL result sets.

Log buffer -- Evidence of a high amount of activity within the Oracle redo log buffers will also be found in frequent log switches in a high number of redo logs based request. The Oracle administrator can monitor the activity within the redo log area, and dynamically add memory to the law buffer's parameter whenever it appears that the Oracle databases in need of additional RAM memory to service the law buffer areas.

Now let's take a closer look at how these RAM memory areas interact with each other.

Changing PGA RAM Allocation

We may want to dynamically change the PGA_AGGREGATE_TARGET parameter when any one of the following conditions are true:

Let's take a look at how a simple script can be used to identify when the shared pool requires additional RAM.

Measuring Library Cache Misses

set lines 80;
set pages 999;

column mydate heading 'Yr. Mo Dy Hr.' format a16
column c1 heading "execs" format 9,999,999
column c2 heading "Cache Misses|While Executing" format 9,999,999
column c3 heading "Library Cache|Miss Ratio" format 999.99999
break on mydate skip 2;

to_char(snap_time,'yyyy-mm-dd HH24') mydate,
sum(new.pins-old.pins) c1,
sum(new.reloads-old.reloads) c2,
sum(new.reloads-old.reloads)/sum(new.pins-old.pins) library_cache_miss_ratio
from stats$librarycache old,
stats$librarycache new,
stats$snapshot sn
where new.snap_id = sn.snap_id
and old.snap_id = new.snap_id-1
and old.namespace = new.namespace
group by to_char(snap_time,'yyyy-mm-dd HH24');
                             Cache Misses
Yr. Mo Dy Hr. execs While Executing LIBRARY_CACHE_MISS_RATIO
---------------- ---------- --------------- ------------------------
2001-12-11 10 10,338 3 .00029
2001-12-12 10 182,477 134 .00073
2001-12-14 10 190,707 202 .00106
2001-12-16 10 2,803 11 .00392

In the above example, we see a clear RAM shortage in the shared pool between 9:00 AM to 10:00 AM each day. In this case we could dynamically re-configure the shared_pool parameter with additional RAM memory from the db_cache_size during this period.

Summary of SGA Thresholds

As we see in table 1, there are several salient thresholds that we can use to monitor the usage of RAM within the SGA. Scripts can be written to poll these values and intelligence can be built into the scripts top reconfigure the SGA when processing demands change.

RAM Area

Too-small Condition

Too-Large Condition

Shared pool

Library cache misses

No misses

Data buffer cache

Hit ratio < 90%

Hit ratio > 95%

PGA aggregate

high multi-pass executions

100% optimal executions

Table 1: Indicators of exceptional conditions within the SGA

Putting It All Together

In a UNIX environment it is very easy to schedule a task to change the RAM memory configuration when the processing needs change. For example, many Oracle database operate in OLTP mode during normal work hours, while at night the database services memory-intensive batch reports.

As we have noted, an OLTP database should have a large value for DB_CACHE_SIZE while memory-intensive batch tasks require additional RAM in the PGA_AGGREGATE_TARGET.

The UNIX scripts below can be used to re-configure the SGA between OLTP and DSS without stopping the instance. In this example, we assume that we have an isolated Oracle server with eight gigabytes of RAM. We also assume that we reserve 20 percent of RAM for UNIX overhead, leaving a total of six gigabytes for Oracle and Oracle connections. These scripts are for HP/UX or Solaris, and accept the $ORACLE_SID as an argument.

The DSS_CONFIG.KSH script will be run at 6:00 p.m. each evening to re-configure Oracle for the memory-intensive batch tasks that run each night.


# First, we must set the environment . . . .
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
#ORACLE_HOME=`cat /var/opt/oracle/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
export PATH

$ORACLE_HOME/bin/sqlplus -s /nologin<<!
connect system/manager as sysdba;
ALTER SYSTEM set db_cache_size=1500m;
ALTER SYSTEM set shared_pool_size=500m;
ALTER SYSTEM set pga_aggregate_target=400m;

Now that we see a generic way to change the Oracle configuration, it should be clear that we can easily develop a mechanism to constantly monitor the processing demands on Oracle and issue the ALTER SYSTEM commands according to existing database demands.