SGA Memory Structures

We are learning oracle SGA since last couple of posts, and now we are very much into it. Let's have discussion on memory structures of oracle SGA(System Global Area).

As we discussed that oracle SGA is consist of several memory structures and background processes. We discussed background processes in last post, let's now have look at memory structures of SGA.

SGA is consist of following memory structures:
1. Database buffer cache
2. Shared pool
3. Redo log buffer cache
4. Java pool
5. Large pool
6. Streams pool




1. Database Buffer Cache:

The database buffer cache is that region of SGA which holds the copy of data blocks read from datafiles. The buffer cache is shared among all connected user of database. When user try to retrieve data from database, server process read it first from datafiles and place a copy of recently used data blocks in database buffer cache. It is managed by least recently used (LRU) algorithm.Data updation is done in this area. There are three types of buffer in buffer cache:

A. Dirty buffer:- Dirty buffer are those which are changed or modified, and need to be written to the disk. This task is done by DBWR, and these dirty buffer are flushed out to datafiles.

B. Free buffers:- Free buffers are those which do not contain any data and ready to use. When server process fetch any data from datafiles, it si stored in free buffers.

C. Pinned buffers:- Pinned buffer are those which are currently being used or explicitly retained for future used.

2. Shared Pool:

The shared pool contains most recently used SQL and PL/SQL statements and data definition, data dictionary, character set information. It is further divided into:

A. Library cache: The library cache includes the shared SQL areas, private SQL areas,PL/SQL procedures and packages, and control structures such as locks and library cache handles. The shared SQL area contains recently executed SQL commands and their execution plans and parse tree where private SQL area contains values for bind variables and run time buffers.

PL/SQL program units are processed the same way as SQL statements by Oracle. When a PL/SQL program unit is executed, the code is moved to the shared PL/SQL area while the individual SQL commands within the program unit are moved to the shared SQL area. Again, the shared program units are maintained in memory with an LRU algorithm.

B. Data Dictionary Cache:

The data dictionary cache is collection of database tables and views containing metadata about database, its structure, its privileges and its users. Oracle access the data dictionary during the parsing of SQL statements. The data dictionary cache is also known as the row cache because it holds data as rows instead of buffers(which hold entire blocks of data).

3. Redo Log Buffer Cache:

The redo log buffer cache is circular buffer in SGA which holds information about changes made to database. It contains the redo entries which contains the information to redo the changes made to database in case of failure. Changes are made to the database through any of these commands:INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP.

4. Java Pool:

The java pool services parsing requirements for java commands. It is required if installing and using java.

5. Large Pool:

The Large pool is a voluntary area in the SGA that can be configured by the database administrator to provide large memory allocations for specific database operations such as an Oracle backup or restore. The large pool allows Oracle to request large memory allocations from a separate pool to prevent contention from other applications for the same memory. The large pool does not have an LRU list.

6. Streams Pool:

The streams pool is used when you are using oracle streams. It is used on capture activity. In prior releases of oracle it is assigned 10% from shared pool, which often faces overflow.

Other than this some memory structures are still there, they are as follows:-

1. Program Global Area:

This is another memory structure of Oracle.

(PGA) is the area in the memory that contains the data and process information for one process. This area is non-shared memory. The PGA size is fixed and is dependent on the operating system. The contents of the PGA vary depending on server configuration.

The memory allocated to hold variables, arrays, and other information that belongs to the session is called stake space.PGA is allocated when the process is completed. Unlike the SGA that is shared by several processes, the PGA provides sort space, session information, stack space, and cursor information for a single server process.

2.Sort Area Size:

The memory area that Oracle uses to sort data is known as the sort area, which uses memory from the PGA for a dedicated server connection. For multi threaded server configurations, the sort area is allocated from the SGA. Sort area size can grow depending on the need. The parameter SORT_AREA_RETAINED_SIZE determines the size to which the sort area is reduced after the sort operation. The memory released from the sort area is kept with the server process; it is not released to the operating system.