The Best Oracle
Resource on the Web
Using the Dynamic SGA Features of Oracle
by Donald K. Burleson
Oracle 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, Oracle opens up a whole new world of possibilities because all
of the Oracle parameters can be reset dynamically using alter database and alter
Oracle, 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
to stop and restart the Oracle database to change the parameters is a
significant enhancement to Oracle, 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
Let's begin by
taking a look at the differences between the Oracle database and the Oracle8i
database. One of the most significant enhancements to Oracle 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 , 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 Oracle
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 Oracle database, RAM for their sort work areas are allocated within the
PGA_AGGREGATE_TARGET region of Oracle. This allows Oracle 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.
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.
buffers -- Oracle 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 Oracle 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 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 Oracle data buffers
pool -- The Oracle 9 9 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 9 9
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.
- 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.
-- 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.
We may want to
dynamically change the PGA_AGGREGATE_TARGET parameter when any one of the
following conditions are true:
- Whenever the
value of the V$SYSSTAT statistic "estimated PGA memory for one-pass" exceeds
PGA_AGGREGATE_TARGET, then we want to increase the value for
- Whenever the
value of the V$SYSSTAT statistic "workarea executions - multipass" is greater
than one percent, the database may benefit from additional RAM.
- You may
over-allocate PGA memory, and you may consider reducing the value of
PGA_AGGREGATE_TARGET whenever the value of the V$SYSSTAT row "workarea
executions - optimal" consistently measures 100 percent.
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,
new.snap_id = sn.snap_id
old.snap_id = new.snap_id-1
old.namespace = new.namespace
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.
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.
||Hit ratio <
||Hit ratio >
Table 1: Indicators of exceptional conditions within the SGA
Viewing Load on SGA Memory Areas
introduced several new internal views and new columns in existing views to aid
in viewing the internal allocation of RAM memory in Oracle. The following new
v$ views can be used to monitor RAM memory usage of Oracle connections.
- Three new columns are added in Oracle for monitoring PGA memory usage.
The new columns are called pga_used_mem, pga_alloc_mem and pga_max_mem.
- There are many new statistics rows, including work area statistics for
optimal, one-pass and multi-pass.
- This new view shows internals of PGA memory usage for all background
processes and dedicated connections.
- This exciting new view contains execution plan information for all currently
executing SQL. This is a tremendous tolls for the performance tuning
processional who must locate sub-optimal SQL statements.
- This new view provides detailed cumulative statistics on RAM usage for
V$WORKAREA_ACTIVE - This new view show internal RAM memory usage
information for all currently executing SQL statements.
The goal is to
use these V$ views to monitor the RAM usage within the SGA and issue alter
system commands to re-allocate RAM memory based upon the processing requirements
of the Oracle instance. Let's take a closer look at these new Oracle features
and scripts that allow us to see detailed RAM memory usage.
While we do not
have enough room in this article to explore all of the techniques, let's take a
look at a simple example of using the V$SYSSTAT view to determine when to
reconfigure the PGA_AGGREGATE_TARGET parameter.
query gives the total number and the percentage of times work areas were
executed in these three modes since the database instance was started.
decode(total, 0, 0, round(cnt*100/total)) percentage
(sum(value) over ()) total
name like 'workarea exec%'
The output of
this query might look like the following:
PROFILE CNT PERCENTAGE
----------------------------------- ---------- ----------
workarea executions - optimal 5395 95
workarea executions - onepass 284 5
workarea executions - multipass 0 0
This output of
this query is used to tell the DBA when to dynamically adjust
PGA_AGGREGATE_TARGET. In general, the value of PGA_AGGREGATE_TARGET should be
increased when multi-pass executions are greater than zero, and reduced whenever
the optimal executions is 100 percent.
We can also use
the V$PGASTAT view to determine RAM usage for our Oracle instance. The V$PGASTAT
view provides instance level summary statistics on the PGA usage and the
automatic memory manager. The following script provides excellent overall usage
statistics for all Oracle connections.
Here is a simple
script to detect PGA RAM usage within Oracle.
column name format a30
column value format 999,999,999
The output of
this query might look like the following:
aggregate PGA auto target 736,052,224
global memory bound 21,200
total expected memory 141,144
total PGA inuse 22,234,736
total PGA allocated 55,327,872
maximum PGA allocated 23,970,624
total PGA used for auto workareas 262,144
maximum PGA used for auto workareas 7,333,032
total PGA used for manual workareas 0
maximum PGA used for manual workareas 0
estimated PGA memory for optimal 141,395
maximum PGA memory for optimal 500,123,520
estimated PGA memory for one-pass 534,144
maximum PGA memory for one-pass 52,123,520
In the above
display from v$pgastat we see the following statistics.
PGA auto target - This column gives the total amount of available memory
for Oracle connections. As we have already noted, this value is derived from
the value on the INIT.ORA parameter PGA_AGGREGATE_TARGET.
memory bound - This statistic measures the max size of a work area, and
Oracle recommends that whenever this statistics drops below one megabyte, you
should increase the value of the PGA_AGGREGATE_TARGET parameter.
- Total PGA
allocated - This statistic display the high-water mark of all PGA memory
usage on the database. You should see this value approach the value of
PGA_AGGREGATE_TARGET as usage increases.
- Total PGA
used for auto workareas - This statistic monitors RAM consumption or all
connections that are running in automatic memory mode. Remember, not all
internal processes may use the automatic memory feature. For example, Java and
PL/SQL will allocate RAM memory, and this will not be counted in this
statistic. Hence, we can subtract value to the total PGA allocated to see the
amount of memory used by connections and the RAM memory consumed by Java and
PGA memory for optimal/one-pass - This statistic estimates how much memory
is required to execute all task connections RAM demands in optimal mode.
Remember, when Oracle experienced a memory shortage, it invoked the
multi-pass operation. This statistics is critical for monitoring RAM
consumption in Oracle, and most Oracle DBA's will increase
PGA_AGGREGATE_TARGET to this value.
Now that we
understand the concept, let's take a look at possible methods for automating the
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.
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':'`
$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
management within Oracle still largely a manual process, many Oracle
administrators use writing tools that constantly monitor the RAM memory usage
within the Oracle SGA, and automatically de-allocate and reallocate RAM memory
based on the existing usage within the Oracle instance. This allows the Oracle
administrator complete flexibility in reconfiguring their system demands on the