Shared Global Area - SGA
SGA is a dynamic pool which consists of various memory components. It contains control and data information for oracle database instance. SGA is shared by server process as well as background processes. SGA memory allocation and deallocation is done in terms of granules(contiguous memory) except for redolog buffer. Components of SGA are as follows
- Database Buffer Cache
- Redo Log Buffer
- Shared Pool
- Large Pool
- Java Pool
- Streams Pool
- Fixed SGA
Database Buffer cache
Buffer cache is the memory component that stores copies of data blocks that are read from data files. A buffer is a memory that temporarily caches a current or recently used data blocks. This component takes care of the following
- Optimize the physical I/O
- Keep freequently used blocks in buffer cache
- Write least frequently used blocks to disk
Buffers in the buffer cache has different states
- Unused :- The buffer is available for use
- Clean :- Buffers that were previously used but now it has read-consistant block
- Dirty : These are modified buffers that are not yet written to the disk
Allocation and de-allocation of buffers work on LRU(Least recently used) basis. Frequently used buffers are called hot buffer and cold buffer are those that are not used recently. Lets say when a query executes if server process can find the required data in buffer cache its called cache hit. If the data is not available in buffer cache its cache miss. Physical read happens when data block is copied from datafile to memory
Buffer cache has different pools
- Default cache :- defaults location for caching the blocks
- Keep cache :- frequently used blocks as usually kept here, to avoid aging out of blocks from cache
- Recycle cache :- Infrequently used blocks are kept here, or to phase it out of cache from consuming memory
- Cache to keep non standard blocks (2k, 4k, 16k blocks) :- This pool keeps blocks that are not standard block size
Redo log buffer
Redo log buffer is a circular buffer having changed vectors to the database. These changes are made through DML or DDL operations. It is the smallest memory component in SGA. Log writer process writes changed vectors from redolog buffer to online redolog files
Shared Pool
The shared pool caches various control information. It stores info such as parsed SQL, PL/SQL code, system parameters, and data dictionary information. Shared pool has different components
- Library Cache
- Data Dictionary Cache
- Server Result Cache
- Reserved Pool
Library Cache
Library cache stores executable SQL and PL/SQL code, along with control structures such as locks and library cache handles.
Data Dictionary Cache
Data dictionary cache hold metadata of tables and reference information about the database
Result Cache
Server result cache contains the SQL query result cache and PL/SQL function result cache. In simple words its the result of processed SQL statement
Reserved Pool
Reserved pool is a shared pool component that Oracle Database can use to allocate large contiguous chunks of memory. This pool can be used to Allocate memory from the shared pool which is performed in chunks. It will allow large object(over 5 KB) to be loaded into the cache without needing a single contiguous area
Large Pool
Large pool is an optional memory area intended for memory allocations that are larger than is appropriate for the shared pool
Java Pool
Java Pool is used along with JVM component
Streams Pool
Streams Pool is used if Oracle streams is configured or used. This pool is exclusively for the use of Oracle streams
Fixed SGA
The fixed SGA is used for internal housekeeping tasks like keeping information about locks etc
