What is SGA

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.

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

* Fixed Portion
* Variable Portion
* Shared pool
* java pool

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 which provides working space for rman (although rman will also work without large pool).
* Shared pool used for objects that are shared among all users.

The shared pool can further be subdivied into:
o Control structures
o Character sets
o 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.
o 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. )

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

Database buffer cache
It's size is equal to db_block_size * db_block_buffers.

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.
If the instance is running in MTS mode, there'se also a UGA: user global area

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

The following initialization parameters affect the size of the SGA:

* db_block_buffers,
* db_block_size,
* db_cache_size,
* db_keep_cache_ size,
* db_recycle_cache_ size,
* java_pool_size.
* large_pool_size,
* log_buffer,
* shared_pool_ size,
* streams_pool_ size,

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 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.

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.

No comments: