|
Memory is a critical system resource which
has a significant impact on the overall performance of the
Oracle database. Database administrators, therefore, closely
monitor system memory utilization to ensure its most optimal
use. Continuing its quest to make the management of Oracle
databases simple, Oracle9i
includes a number of features which automate memory management,
provide optimal out-of-box performance and allow dynamic reconfiguration.
Oracle9i's
enhanced memory management enables faster query execution,
ensures the most efficient utilization of available memory,
and facilitates better integration with operating system resource
managers.
Dynamic
System Global Area (SGA)
Oracle9i
makes it simple to add to and remove memory from an instance.
Database administrators can change the size of the buffer
cache and the shared pool without having to restart the instance.
By allowing such changes to be made online, the dynamic SGA
feature helps administrators increase database availability.
It also makes it possible to dynamically change the memory
available to an Oracle instance resulting either from changes
in system hardware or changes to OS resource manager allocations.
The SGA configuration of a running instance
can be altered by changing the values of the parameters governing
sizes of the buffer cache (DB_CACHE_SIZE) and the shared pool
(SHARED_POOL_SIZE). Both these parameters have been made dynamic
in Oracle9i and their values can be modified using the ALTER
SYSTEM command.
Buffer
Cache Advisory
Oracle9i
also includes an advisory to help DBAs size the buffer cache
optimally. This advisory relies on an internal simulation
based on the current workload to predict the cache "miss"
rates for various sizes of the buffer cache raging from 10%
to 200% of the current cache size. These predictions are published
through a new view V$DB_CACHE_ADVICE. This view can be used
to determine if the current size of the buffer cache should
be increased or decreased to ensure optimal performance for
the present workload. By providing a deterministic way to
size the buffer cache, Oracle9i
takes the guesswork out of database memory configuration thereby
eliminating wastage caused by memory over allocation.
The buffer cache advisory is turned off by
default since there is a minor performance overhead associated
with the data collection and cache simulation. It can be turned
on by setting the parameter DB_CACHE_ADVICE to ON. Changing
the value of this parameter to READY stops further data collection
but preserves the advisory results in the V$DB_CACHE_ADVICE
view. The contents of this view are cleared when the advisory
is turned off by setting the DB_CACHE_ADVICE parameter OFF.
.
Oracle9i Buffer Cache Advisory
takes the guess work out of database memory configuration
Automatic
SQL Execution Memory Management
The performance of complex long running queries,
typical in a DSS environment, depends to a large extent on
the memory available in the Program Global Area (PGA). In
Oracle8i
and previous releases of the database, administrators sized
the PGA by carefully adjusting a number of initialization
parameters. Oracle9i
completely automates the management of PGA memory. Administrators
merely need to specify the maximum amount of PGA memory available
to an instance using a newly introduced parameter PGA_AGGREGATE_TARGET.
The database server automatically distributes this memory
among various active queries in an intelligent manner so as
to ensure maximum performance benefits and the most efficient
utilization of memory. Furthermore, Oracle9i
can adapt itself to changing workload thus utilizing resources
efficiently regardless of the load on the system. The amount
of the PGA memory available to an instance can be dynamically
changed by altering the value of the PGA_AGGREGATE_TARGET
parameter making it possible to add to and remove PGA memory
from an active instance online. In the automatic mode, administrators
no longer have to manually tune the sizes of the individual
work areas using parameters such as SORT_AREA_SIZE, HASH_AREA_SIZE,
BITMAP_MERGE_AREA_SIZE and CREATE_BITMAP_AREA_SIZE.
The automatic SQL execution memory management
feature is enabled by setting the parameter WORK_AREA_SIZE
to AUTO. For backward compatibility reasons, Oracle9i
continues to support the manual PGA management mode. The manual
mode can be activated by either setting the WORK_AREA_SIZE
parameter to MANUAL or not specifying a value for PGA_AGGREGATE_TARGET.
This feature will help DBAs reduce the time
required to tune the memory usage for their DSS applications.
The saving gained from the improved use of memory should translate
to better throughput where large number of users are on the
system, as well as improved response time for queries.
More
Info
Oracle9i
Database Daily Features
|
Bookmark
