SGA details of Oracle 11g

Their has been a lot of talk regarding the SGA details of Oracle. And a lot people even get confused when talking of various processes in memory and buffer of Oracle. So here we will discuss some of the important process and buffer details of Oracle SGA (system Global Area).

A system global area (SGA) is a group of shared memory structures that contain data and control information for one Oracle database instance. If multiple users are concurrently connected to the same instance, then the data in the instance's SGA is shared among the users. Consequently, the SGA is sometimes called the shared global area.

An SGA and Oracle processes constitute an Oracle instance. Oracle automatically allocates memory for an SGA when you start an instance, and the operating system reclaims the memory when you shut down the instance. Each instance has its own SGA.

The SGA is read/write. All users connected to a multiple-process database instance can read information contained within the instance's SGA, and several processes write to the SGA during execution of Oracle.

The SGA contains the following data structures:

•             Database buffer cache

•             Redo log buffer

•             Shared pool

•             Java pool

•             Large pool (optional)

•             Streams pool

•             Data dictionary cache

•             Other miscellaneous information

Part of the SGA contains general information about the state of the database and the instance, which the background processes need to access; this is called the fixed SGA. No user data is stored here. The SGA also includes information communicated between processes, such as locking information.

If the system uses shared server architecture, then the request and response queues and some contents of the PGA are in the SGA.

Parameters used to control SGA Size

SGA_MAX_SIZE  - controls the total SGA Size (including real and virtual memory allocation) that can be allocated by OS to an Instance.

SGA_TARGET  -  The target maximum size of SGA. If SGA_TARGET is set to a value greater than SGA_MAX_SIZE at startup, then the latter is bumped up to accommodate SGA_TARGET.

SGA minimum size is the sum of below given parameters.

DB_CACHE_SIZE              The size of the cache of standard blocks.

LOG_BUFFER                    The number of bytes allocated for the redo log buffer.

SHARED_POOL_SIZE       The size in bytes of the area devoted to shared SQL and PL/SQL statements.

LARGE_POOL_SIZE          The size of the large pool; the default is 0.

JAVA_POOL_SIZE              The size of the Java pool.

Oracle Instance

http://docs.oracle.com/cd/E11882_01/server.112/e25789/img/cncpt283.gif

Database writer (DBWn)

The database writer writes modified blocks from the database buffer cache to the datafiles. Oracle Database allows a maximum of 20 database writer processes (DBW0-DBW9 and DBWa-DBWj). The DB_WRITER_PROCESSES initialization parameter specifies the number of DBWn processes. The database selects an appropriate default setting for this initialization parameter or adjusts a user-specified setting based on the number of CPUs and the number of processor groups.

Log writer (LGWR)

The log writer process writes redo log entries to disk. Redo log entries are generated in the redo log buffer of the system global area (SGA). LGWR writes the redo log entries sequentially into a redo log file. If the database has a multiplexed redo log, then LGWR writes the redo log entries to a group of redo log files.

Checkpoint (CKPT)

At specific times, all modified database buffers in the system global area are written to the datafiles by DBWn. This event is called a checkpoint. The checkpoint process is responsible for signalling DBWn at checkpoints and updating all the datafiles and control files of the database to indicate the most recent checkpoint.

System monitor (SMON)

The system monitor performs recovery when a failed instance starts up again. In an Oracle Real Application Clusters database, the SMON process of one instance can perform instance recovery for other instances that have failed. SMON also cleans up temporary segments that are no longer in use and recovers dead transactions skipped during system failure and instance recovery because of file-read or offline errors. These transactions are eventually recovered by SMON when the tablespace or file is brought back online.

Process monitor (PMON)

The process monitor performs process recovery when a user process fails. PMON is responsible for cleaning up the cache and freeing resources that the process was using. PMON also checks on the dispatcher processes (described later in this table) and server processes and restarts them if they have failed.

Archiver (ARCn)

One or more archiver processes copy the redo log files to archival storage when they are full or a log switch occurs. Archiver processes are the subject of Chapter 11, "Managing Archived Redo Logs".

Recoverer (RECO)

The recoverer process is used to resolve distributed transactions that are pending because of a network or system failure in a distributed database. At timed intervals, the local RECO attempts to connect to remote databases and automatically complete the commit or rollback of the local portion of any pending distributed transactions. For information about this process and how to start it, see Chapter 33, "Managing Distributed Transactions".

Dispatcher (Dnnn)

Dispatchers are optional background processes, present only when the shared server configuration is used. Shared server was discussed previously in "Configuring Oracle Database for Shared Server".

Global Cache Service (LMS)

In an Oracle Real Application Clusters environment, this process manages resources and provides inter-instance resource control.