Donald K. Burleson Oracle Tips

Oracle 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 overhead .

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:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm