Memory Structures

Oracle's memory structure consists of two memory areas known as the System Global Area (SGA) and the Program Global Area (PGA) (see Figure 1.3).

Figure 1.3. Memory structures and processes of Oracle.

System Global Area

The SGA is the shared memory region that contains both data and control information for one Oracle instance. If there is more than one user connect to the instance, the SGA and the data allocation in the SGA are shared among all connected users. For this reason the SGA is often referred to as the Shared Global Area. Oracle allocates the entire SGA when an instance starts and deallocates the memory (freeing it for the operating system to reclaim) only when the instance shuts down. Each instance has its own SGA.

The SGA has read/write permissions for every user connected to the instance. Users are not the only ones able to read from and write to the SGA, however. Oracle itself writes certain things to the SGA outside the user query results. You, as the DBA, can pin stored procedures in the SGA. You can also pin small lookup tables in the SGA so that they don't get aged out and so that their access is speeded up. The SGA is allocated at instance startup and remains allocated until the instance is shut down; it is a fundamental component of every Oracle instance.

A granule, a contiguous unit of virtual memory allocation, is how the memory allocation in the SGA is tracked. If the SGA_MAX_SIZE parameter of the database is set to under 128MB, a granule is 4MB; otherwise, it is 16MB. It is on the granule boundary that the components that make up the SGA are allowed to grow and shrink to.

The SGA contains the database buffer cache, the redo log buffer, and the shared pool. The following sections explain what each portion's function is.

Database Buffer Cache

The database's buffer cache is that portion of the SGA that holds the most recent copies of data blocks that have been read from datafiles. The set of database buffers in an instance is the database's buffer cache. The buffer cache contains modified as well as unmodified data blocks. All user processes concurrently connected to the instance share access to the database buffer cache.

The most recently used data is kept in this memory area. Fewer reads from and writes to disk are necessary, and this can contribute significantly to performance improvements. The most recently used data is often the most frequently used data. Because it is accessed frequently, it is usually among the most recently accessed.

Redo Log Buffer

The redo log buffer is a circular buffer in the SGA whose job it is to hold information, stored in redo entries, about changes made to the database. Redo entries contain all the information necessary to reconstruct (or redo) all the changes made to the database by any INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP operation that has occurred in the database. Redo entries are used for database recovery, if necessary, and are copied by an Oracle server process from the memory space to the redo log buffer in the SGA. These entries take up continuous sequential space in the circular buffer and are written out to the redo log files on disk by Log Writer process (LGWR) before they are overwritten by another set of entries.

Shared Pool

The shared pool portion of the SGA contains two major areas and is sized using the parameter SHARED_POOL_SIZE. The two areas are as follows:

• The Library Cache Includes the shared SQL areas, private SQL areas, PL/SQL procedures and packages, and control structures

• The Dictionary Cache SGA memory area containing information on various data dictionary objects that contain data about tables, indexes, users, and other database objects

Oracle represents each SQL statement it runs with a shared SQL area and a private SQL area and recognizes when two different users are executing the same SQL statement. When this happens, Oracle reuses the shared SQL area for those users. However, each user must have a separate copy of the statement's private SQL area.

In the following section, we will look more closely at the shared SQL areas.