Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

Memory Management In Oracle9i

Oracle9i introduces a number of new features aimed to make memory management simpler and more flexible.

Dynamic SGA Parameters

In Oracle8i, making changes to the SGA memory structures involved a restart of the server. This made the process of modification and testing of new SGA configurations almost impossible on production systems. Oracle9i has made more instance parameters dynamic, including DB_CACHE_SIZE and SHARED_POOL_SIZE.

ALTER SYSTEM SET DB_CACHE_SIZE = 10000000;
ALTER SYSTEM SET SHARED_POOL_SIZE = 10000000;

Automatic SQL Execution Memory Management

Prior to Oracle9i optimization of the PGA memory structures could be very time consuming depending on the type of operations the system was performing. Oracle9i allows the DBA to leave configuration of the PGA up to Oracle by setting two initialization parameters.

WORKAREA_SIZE_POLICY = AUTO
PGA_AGGREGATE_TARGET = 100000K

The WORKAREA_SIZE_POLICY parameter tell the server that it should take over PGA memory management. The PGA_AGGREGATE_TARGET parameter specifies the total amount of memory the server can allocate to the PGA. Oracle quote the following equations as a base for calculating the value of this parameter.

PGA_AGGREGATE_TARGET = (TOTAL_MEM * 80%) * 20% for an OLTP system
PGA_AGGREGATE_TARGET = (TOTAL_MEM * 80%) * 50% for a DSS system

Where TOTAL_MEMORY is the total available memory for the system. If multiple applications/instances are running on the machine the values should be adjusted accordingly.

For backwards compatibility Oracle9i allows manual configuration of the PGA using.

WORKAREA_SIZE_POLICY = MANUAL
SORT_AREA_SIZE = ???
HASH_AREA_SIZE = ???
BITMAP_MERGE_AREA_SIZE = ???
CREATE_BITMAP_AREA_SIZE = ???

Buffer Cache Advisory

Oracle9i includes a buffer cache advisory to aid configuration of the buffer cache. 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 ranging from 10% to 200% of the current cache size. The advisor can be started and stopped using the following.

ALTER SYSTEM SET DB_CACHE_ADVICE = <ON/OFF/READY>

By default the advisor is set to off as data collection and cache simulation cause a slight performance overhead. The results of switching the parameter are as follows.

The results of the advisor can be viewed by querying the V$DB_CACHE_ADVICE view.

Hope this helps. Regards Tim...

Back to the Top.