System Global Area
Each Oracle instance uses a System Global Area or SGA — a shared-memory area — to store its data and control-information.
Each Oracle instance allocates itself an SGA when it starts and de-allocates it at shut-down time. The information in the SGA consists of the following elements, each of which has a fixed size, established at instance startup:
* The database buffer cache: this stores the most recently-used data blocks. These blocks can contain modified data not yet written to disk (sometimes known as "dirty blocks"), unmodified blocks, or blocks written to disk since modification (sometimes known as clean blocks). Because the buffer cache keeps blocks based on a most-recently-used algorithm, the most active buffers stay in memory to reduce I/O and to improve performance.
* The redo log buffer: this stores redo entries — a log of changes made to the database. The instance writes redo log buffers to the redo log as quickly and efficiently as possible. The redo log aids in instance recovery in the event of a system failure.
* The shared pool: this area of the SGA stores shared-memory structures such as shared SQL areas in the library cache and internal information in the data dictionary. An insufficient amount of memory allocated to the shared pool can cause performance degradation
Library cache
The library cache stores shared SQL, caching the parse tree and the execution plan for every unique SQL statement.
If multiple applications issue the same SQL statement, each application can access the shared SQL area. This reduces the amount of memory needed and reduces the processing-time used for parsing and execution planning.
Data dictionary cache
The data dictionary comprises a set of tables and views that map the structure of the database.
Oracle stores information here about the logical and physical structure of the database. The data dictionary contains information such as the following:
* User information, such as user privileges
* Integrity constraints defined for tables in the database
* Names and datatypes of all columns in database tables
* Information on space allocated and used for schema objects
The Oracle instance frequently accesses the data dictionary in order to parse SQL statements. The operation of Oracle depends on ready access to the data dictionary: performance bottlenecks in the data dictionary affect all Oracle users. Because of this, database administrators should make sure that the data dictionary cache has sufficient capacity to cache this data. Without enough memory for the data-dictionary cache, users see a severe performance degradation. Allocating sufficient memory to the shared pool where the data dictionary cache resides precludes these particular performance problems.
Program Global Area
The Program Global Area or PGA memory-area contains data and control-information for Oracle's server-processes.
The size and content of the PGA depends on the Oracle-server options installed. This area consists of the following components:
* stack-space: the memory that holds the session's variables, arrays, and so on.
* session-information: unless using the multithreaded server, the instance stores its session-information in the PGA. (In a multithreaded server, the session-information goes in the SGA.)
* private SQL-area: an area in the PGA which holds information such as bind-variables and runtime-buffers.
* sorting area: an area in the PGA which holds information on sorts, hash-joins, etc.