SGA

Larger Pic Link

clip_image002[5]

A system global area (SGA) is a group of shared memory structures that contain data and control information for one Oracle database instance. SGA information is shared among multiple users of oracle database instance, its sometimes called shared global area. Oracle automatically allocates memory to SGA when an instance starts up, and the operating system reclaims the memory when the instance shuts down.

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

Dynamic SGA:

Sizing the SGA:

The size of the SGA is determined by several initialization parameters. The parameters that most affect SGA size are:

DB_CACHE_SIZE: The size of the cache of standard blocks. The default is 48 MB on UNIX and 52 MB on NT

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. The default is 16 MB. If 64 bit, then the default size is 64 MB

LARGE_POOL_SIZE: The size of the Large Pool. The default is zero (Unless the init.ora parameter PARALLEL_AUTOMATIC_TUNING is set to TRUE, then the default is automatically calculated.)

JAVA_POOL_SIZE: The size of the Java Pool. The default is 24 MB

Therefore, the SGAs size can not exceed SGA_MAX_SIZE – DB_CACHE_SIZE – LOG_BUFFER – SHARED_POOL_SIZE – LARGE_POOL_SIZE – JAVA_POOL_SIZE.

Components of the SGA

The SGA consists of the following four (five if MTS) parts:

Fixed SGA

The fixed SGA is a component of the SGA that varies in size from platform to platform and release to release. It is compiled into the database. The fixed SGA contains a set of variables that point to the other components of the SGA and variables that contain the values of various parameters. The size of the fixed SGA is constant and cannot be changed through any means and it is generally very small. Think of this area as a bootstrap section of the SGA, something Oracle uses internally to find the other bits and pieces of the SGA.

Variable portion

The variable portion is called variable because its size (measured in bytes) can be changed.The variable portion consists of:

  • large pool (optional)
    It provides large memory allocation for backup and restoration operations, session memory for shard servers and Oracle XA interface, I/O server processes(DBWO) or parallel query buffers.
  • Java pool The location where session-specified Java code and application variables reside during program execution. 
  • Shared pool The shared pool is used for objects that are shared among all users. For example: table definitions, PL/SQL definitions, cursors and so on.

Shared Pool

Shared Pool

The shared pool portion of the SGA contains the Library Cache, the dictionary cache, buffers for parallel execution messages, and control structures.

Library Cache

The library cache includes the shared SQL areas, private SQL areas (in the case of a shared server configuration), PL/SQL procedures and packages, and control structures such as locks and library cache handles.

Dictionary Cache

The data dictionary is a collection of database tables and views containing reference information about the database, its structures, and its users. Oracle accesses the data dictionary frequently during SQL statement parsing. This access is essential to the continuing operation of Oracle.

The data dictionary is accessed so often by Oracle that two special locations in memory are designated to hold dictionary data. One area is called the data dictionary cache, also known as the row cache because it holds data as rows instead of buffers (which hold entire blocks of data). The other area in memory to hold dictionary data is the library cache. All Oracle user processes share these two caches for access to data dictionary information.See dictionary cache efficiency for monitoring dictionary cache.

If the data dictionary cache is too small, requests for information from the data dictionary will cause extra I/O to occur; these I/O-bound data dictionary requests are called recursive calls and should be avoided by sizing the data dictionary cache correctly.

 

Streams Pool

The streams pool holds data and control structures to support the Oracle Streams
feature of Oracle Enterprise Edition. Oracle Streams manages the sharing of data and events in a distributed environment. If the initialization parameter STREAMS_POOL_SIZE is uninitialized or set to zero, the memory used for Streams operations is allocated from the shared pool and may use up to 10 percent of the shared pool.

The Oracle Streams pool stores buffered queue messages in memory, and it provides memory for capture processes and apply processes.

Shared Sql Area

A shared SQL area contains the parse tree and execution plan for a given SQL statement. Oracle saves memory by using one shared SQL area for SQL statements run multiple times, which often happens when many users run the same application.

Shared PL/SQL Area

PL/SQL program units(procedures,functions,packages,anonymous blocks,triggers) are processed much the same way as individual SQL statements. Oracle allocates a shared area to hold the parsed, compiled form of a program unit. Oracle allocates private area to hold values specific to the session that runs the program unit including local, global and package variables and buffers for executing SQL.

A figure of between 90 – 95 % should be maintained; if the rate starts to drop, SHARED_POOL_SIZE should be increased.

 

SGA Parameters and Memory Allocation:

The SGA comprises a number of memory components, which are pools of memory used to satisfy a particular class of memory allocation requests.All SGA components allocate and deallocate space in units of granules. Oracle Database tracks SGA memory use in internal numbers of granules for each SGA component. 

SGA_MAX_SIZE:

Memory allocated to SGA components can expand up to the maximum determined by SGA_MAX_SIZE initialization parameter.

Automatic Shared Memory Management:

With ASMM SGA_TARGET initialization parameter can be set and Oracle will automatically distribute this memory along various components of SGA to ensure most effective utilization of memory.

Manually Managed SGA Components

There are a few SGA components whose sizes are not automatically adjusted. The administrator needs to specify the sizes of these components explicitly, if needed by the application. Such components are:

  • Keep/Recycle buffer caches (controlled by DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE)
  • Additional buffer caches for non-standard block sizes (controlled by DB_nK_CACHE_SIZE, n = {2, 4, 8, 16, 32})

 

Redo Log Buffer

The redo log buffer is a circular buffer in the SGA that holds information about changes made to the database. This information is stored in redo entries. Redo entries contain the information necessary to reconstruct, or redo, changes made to the database by INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP operations. Redo entries are used for database recovery, if necessary.

Redo entries are copied by Oracle database processes from the user’s memory space to the redo log buffer in the SGA. The redo entries take up continuous, sequential space in the buffer. The background process LGWR writes the redo log buffer to the active redo log file (or group of files) on disk.

Database Buffer Cache

The database buffer cache is the portion of the SGA that holds copies of data blocks read from datafiles. All user processes concurrently connected to the instance share access to the database buffer cache.

Organization of the Database Buffer Cache

The buffers in the cache are organized in two lists: the write list and the least recently used (LRU) list. The write list holds dirty buffers, which contain data that has been modified but has not yet been written to disk. The LRU list holds free buffers, pinned buffers, and dirty buffers that have not yet been moved to the write list. Free buffers do not contain any useful data and are available for use. Pinned buffers are currently being accessed.

Size of the Database Buffer Cache

Oracle supports multiple block sizes in a database. The standard block size is used for the SYSTEM tablespace. You specify the standard block size by setting the initialization parameter DB_BLOCK_SIZE. Legitimate values are from 2K to 32K.

Optionally, you can also set the size for two additional buffer pools, KEEP and RECYCLE, by setting DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE. These three parameters are independent of one another.

See Also: "Multiple Buffer Pools" for more information about KEEP and RECYCLE buffer pools

Block Size Specific Caches

The sizes and numbers of non-standard block size buffers are specified by the following parameters:

DB_2K_CACHE_SIZE

DB_4K_CACHE_SIZE

DB_8K_CACHE_SIZE

DB_16K_CACHE_SIZE

DB_32K_CACHE_SIZE

Multiple Buffer Pools

You can configure the database buffer cache with separate buffer pools that either keep data in the buffer cache or make the buffers available for new data immediately after using the data blocks. Particular schema objects (tables, clusters, indexes, and partitions) can then be assigned to the appropriate buffer pool to control the way their data blocks age out of the cache.

  • The KEEP buffer pool(keep cache) retains the schema object’s data blocks in memory.
  • The RECYCLE buffer pool(recycle cache) eliminates data blocks from memory as soon as they are no longer needed.
  • The DEFAULT buffer pool(default cache) contains data blocks from schema objects that are not assigned to any buffer pool, as well as schema objects that are explicitly assigned to the DEFAULT pool.

The initialization parameters that configure the KEEP and RECYCLE buffer pools are DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: