Oracle Tips by Burleson

Oracle SGA

SGA is an abbreviation for Shared Global Area. As the term global implies, this area is accessible to all Oracle processes and users. Each instance will have its own SGA. Oracle processes and users must share large amounts of data. If all of the processes had to get the data from the disk, the I/O load would soon render totally unacceptable response times. To prevent this, Oracle uses global memory areas, that is, CPU memory. This memory is dedicated to use for Oracle alone. In Oracle8i, the SGA contains data buffer areas, redo log buffers, and the shared pool (context areas). Each area is important to the database’s overall performance. An additional area, the LARGE POOL, is also configured. Under Oracle8i, another area has been added, the Java shared pool area. Oracle9i has the capability to divide the database buffer regions into multiple varying block sized areas (2K, 4K, 8K, 16K and 32K block sizes are supported).

The shared pool context areas and database buffers provide immediate access to data that has been preread from either the data dictionary tables or the data tables. The Oracle kernel process uses an LRU (least recently used) algorithm to write data back to the disks. Data is never altered on the disks directly; it is altered in memory first. In Oracle8 the ability to have areas where LRU aging was turned off, know as the KEEP area and where LRU aging was accelerated, known as the RECYCLE area were added.

The redo buffers contain row change information, transaction commit history, and checkpoint history. This data is written into the redo logs and eventually to the archive logs. A commit will force a disk write, as will the filling of a redo log buffer or the reaching of a predefined checkpoint.

For Oracle7 the queue and request areas store data that is being transferred between processes such as servers and other Oracle7 processes. The shared SQL area stores all SQL statements in a parsed form. When a user or process issues an SQL (Structured Query Language) command, the shared SQL area is checked to see if the command already exists in parsed form; if it does, this shared version is used. If the multithreaded server option is utilized, some of the user Process Global Area (PGA; described in the next section) is also placed in the shared pool. Under Oracle8, the LARGE POOL area is an optional extension to the SGA. If configured via its initialization parameters, the LARGE POOL takes over the session-level memory needs for MTS (multithreaded server) or XA sessions. The LARGE POOL is also used for I/O slaves and during Oracle backup and restore operations. Under Oracle8i, parallel execution queues are also allocated from the LARGE POOL area. If you will be using the parallel query option, pay particular attention to the section in Chapter 12, Tuning Oracle Applications, on tuning the shared, large, and Java pools for Oracle8i. In Oracle9i, the database buffer areas are allowed to have multiple blocksizes; this promises greater tuning flexibility for mixed-mode databases. .

Expert Remote DBA BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call BC Remote DBA today.