René Nyffenegger's collection of things on the web | |
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
|
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:
The size for the variable portion is roughly equal to the result of the following statement:
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 SGAParameters affecting the size of SGA
The following initialization parameters affect the size of the 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.
|