René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

SGA (System Global Area)

The SGA is a chunk of memory that is allocated by an Oracle Instance (during the nomount stage) and is shared among Oracle processes, hence the name. It contains all sorts of information about the instance and the database that is needed to operate.

Components of the SGA

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

Fixed portion

The size of the fixed portion is constant for a release and a plattform of Oracle, that is, it cannot be changed through any means such as altering the initialization parameters

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)
    Provides working space for rman (although rman will also work without large pool).
  • 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.

    The shared pool can further be subdivied into:

    • Control structures
    • Character sets
    • Dictionary cache
      The dictionary cache stores parts fo the data dictionary because Oracle has to query the data dictionary very often as it is fundamental to the functioning of Oracle.
    • Library cache
      The library cache is further divided into
      • Shared SQL Area,
      • PL/SQL Procedures and
      • Control Structures (Latches and Locks).
    The size of the Shared Pool is essentially governed by the initialization parameter shared_pool_size (although shared_pool_size is usually smaller than the size of the shared pool, see here) and db_block_buffers (which plays a role for this size because the database buffer cache must be administered.)

    v$db_object_cache displays objects (=tables, indexes, clusters, synonym definitions, PL/SQL procedures/packages and triggers) that are cached in the library cache.

  • java pool
The size for the variable portion is roughly equal to the result of the following statement:
select
  sum(bytes) 
from 
  v$sgastat
where 
  pool in ('shared pool', 'java pool', 'large pool');

Redo log buffer

Redo Buffers is roughly equal to the parameter log_buffer

See redo log buffer

Database buffer cache

It's size is equal to db_block_size * db_block_buffers.
(Note: db_block_buffers is deprecated as of 9i, so if the init parameter db_cache_size) is set, the buffer cache's size will be set according to this value.

UGA

If the instance is running in MTS mode, there'se also a UGA: user global area

Showing information about the SGA

You can use v$sga to show the amount of these sizes or alternatively use SHOW SGA in sql*plus.

Parameters affecting the size of SGA

Limiting the size of the SGA for a user

The amount of SGA that a user can use can be limitted through profiles. Use the private_sga option in the create profile statement.

Dynamic SGA

Dynamic SGA allows to change the size of the buffer cache, the large pool, the shared pool and the process private memory on the fly, that is without shutting down the instance.

Granule size

The granule size of the components can be found out with v$sga_dynamic_components

Peeking into SGA

The x$ tables are an sql interface to the SGA and allow to peek into the SGA.

Notably, x$ksmmem seems to allow to address every byte in the SGA.

See also PGA.

Shared server vs dedicated server

The request and response queues and other parts that are found in the pga as dedicated server are found in the sga when the server is a shared server.

Protecting data structures

As the SGA is, after all, a shared area, and more than one process can perform read and write operations on the SGA, Oracle has to make sure that those operations don't get in their ways. This is ensured with locks and latches.

Locking SGA into memory

On platform that support it, the init parameter lock_sga can be set to true, which will lock the entire SGA into physical memory.

Thanks

Thanks to Rajasekhar who found a broken link on this page.