|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:
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
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
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.
Showing information about the SGA
Parameters 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 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 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 to Rajasekhar who found a broken link on this page.