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.
Oracle 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 Oracle DBA must fully allocate all of the RAM memory
on the UNIX Oracle server, reserving 20% of the RAM memory for UNIX
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 .
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.
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.
If you like Oracle
tuning, you might enjoy my latest book “Oracle Tuning: The DefinitiveReference” by Rampant TechPress. (I don’t think
it is right to charge a fortune for books!) and you can buy it right now
at this link: