Oracle Database Architecture (Part 02)
Oracle Instance
consists of Oracle’s main memory structure, called the system global area (SGA, also known as shared global area) and several Oracle background processes.
When the user accesses the data in the database,It is the SGA with which the server process communicates.
- The SGA is a shared memory area.
- All the users of the database share the information maintained in this area.
- Oracle allocates memory for the SGA when the instance is started
- De-allocates it when the instance is shut down.
The SGA consists :
A- Three mandatory components :
- Shared pool : Caches the most recently used SQL statements that have been issued by database users
- Database buffer cache : Caches the data that has been most recently accessed by database users
- Redo log buffer : Stores transaction information for recovery purposes
B- Four optional components :
- Java pool :Caches the most recently used Java objects and application code when Oracle’s JVM option is used.
- Large pool : Caches data for large operations such as Recovery Manager (RMAN) backup and restore activities and Shared Server components.
- Streams pool : Caches the data associated with queued message requests when Oracle’s Advanced Queuing option is used.
- Result cache : Stores results of SQL queries and PL/SQL functions for better performance .
Oracle Database can manage the components of SGA and PGA automatically using the Automatic Memory Management (AMM) .
Memory in the SGA is allocated in units of contiguous memory called granules. The size of a granule of depends on the parameter MEMORY_MAX_TARGET.
On the PGA can be configured to manage automatically by setting the database parameter
PGA_AGGREGATE_TARGET
Database Buffer Cache
The database buffer cache is the area in SGA that caches the database data, holding blocks from the data files that have been accessed recently. The database buffer cache is shared among all the users connected to the database .
There are three types of buffers:
- Dirty buffers : are the buffer blocks that need to be written to the data files.
- Free buffers : do not contain any data or are free to be overwritten.
- Pinned buffers : are the buffers that are currently being accessed or explicitly retained for future use.
The background process DBWn writes the database blocks from the database buffer cache to the data files.
Oracle Database provides three buffer caches to better manage buffer cache :
- The DEFAULT cache is the default "required" : By default all the data read from the disk is written to the DEFAULT pool.
- The KEEP cache "optionally" : If you want certain data not to be aged from memory, you can configure the KEEP pool and use the ALTER TABLE statement to specify which tables should use the KEEP pool, follow the LRU algorithm .data kept in memory all time .
- The RECYCLE cache "optionally" : if you do not want to age out good data from the default cache for temporary data, you may specify such tables to have the RECYCLE pool instead of the default.
To specifies the size of the database buffer cache DEFAULT pool : The DB_CACHE_SIZE parameter. To configure the KEEP and RECYCLE pools, use the DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE parameters.
Oracle use flash storage for additional buffer cache.by provide Database Smart Flash Cache :
allows the database buffer cache to be expanded beyond the SGA in main memory to a second level cache on flash memory.
When the block expires from the SGA buffer cache, it is evicted to the database flash cache until required again. Flash cache is configured using two database parameters.
DB_FLASH_CACHE_FILE identifies the flash device, and DB_FLASH_CACHE_SIZE specifies the size of flash cache.
Redo Log Buffer :
The redo log buffer is a circular buffer in the SGA that holds information about the changes made to the database data. The changes are known as redo entries or change vectors and are used to redo the changes in case of a failure. DML and DDL statements are used to make changes to the database data.
The parameter LOG_BUFFER determines the size of the redo log buffer cache.
The background process LGWR writes the redo log information to the online redo log files.
Shared Pool :
The shared pool portion of the SGA holds information such as SQL, PL/SQL procedures and packages, the data dictionary, locks, character-set information, security attributes, and so on.
The shared pool consists :
- Library cache :
- Shared SQL areas :used for maintaining recently executed SQL statements and their execution plans and the parse tree.
- Private SQL areas :contains values for the bind variables (persistent area) and runtime buffers (runtime area).
- control structures such as locks and library cache handles :store control information
- Data dictionary cache : holds the most recently used database dictionary information.also known as the row cache because it holds data as rows instead of buffers (which hold entire blocks of data)
- Server result cache :
- SQL query result cache : stores the results of queries , If an application runs the same SELECT statement repeatedly and if the results are cached, then the database can return them immediately.
- PL/SQL function result cache :hold the SQL and PL/SQL function results
- Reserved pool is an area in the shared pool used to allocate large chunks of memory.
PL/SQL programs :it processes SQL statements when a PL/SQL program unit is executed, the code is moved to the shared PL/SQL area,and the individual SQL commands within the program unit are moved to the shared SQL area. again, the shared program units are maintained in memory with an LRU algorithm.
The size is determined by the SHARED_POOL_RESERVED_SIZE initialization parameter.
The parameter SHARED_POOL_SIZE determines the size of the shared pool.
Large Pool :
The large pool is an optional area in the SGA that the DBA can configure to provide large memory allocations for specific database operations such as an RMAN backup or restore.
The large pool allows Oracle to request large memory allocations from a separate pool to prevent contention from other applications for the same memory.
The large pool does not have an LRU list; Oracle Database 12c does not attempt to age objects out of the large pool. The parameter LARGE_POOL_SIZE determines the size of the large pool.
Java Pool:
can configure to provide memory for Java operations, just as the shared pool is provided for processing SQL and PL/SQL statements. The parameter JAVA_POL_SIZE Odetermines the size of the Java pool.
Streams Pool :
Exclusively used by Oracle streams. The STREAMS_POOL_SIZE parameter determines the size of the streams pool.
Practical :
How to see components and sizes of SGA ?
you can use V$SGA and V$SGAINFO,
sql >select * from v$sga;
sql >show sga
sql >SELECT * FROM v$sgainfo; --For more details