Critical memory components in the System Global Area (SGA)

System Global Area or SGA is the shared memory area that provides memory access to all Oracle server processes to manage the database access. SGA and the background server processes are effectively the Oracle Database Instance. ­­­The SGA memory structure holds several memory components like database buffer cache, shared pool, large pool, redo log buffer, Java pool, Streams pool etc. that are used by specific background processes to perform specific tasks. All these memory components of the SGA are allocated automatically during the instance startup and freed up at instance shutdown. The primary function of the SGA is to achieve faster access to data from the Random Access Memory (RAM) by avoiding expensive disk I/O as well as optimize data processing

Source: Oracle® Database Concepts 12c Release 1 (12.1)

Data Buffer Cache

Also known as Database Buffer Cache is the memory area where data blocks are read into from the data segments and temporarily caches the frequently accessed data blocks. Technically, every data access, except a few specific scenarios, from the database goes through this cache area. Oracle first searches the data in this buffer cache and if there’s a miss then it performs the expensive disk I/O.  Buffer manager uses complex cache retention algorithm (e.g. Least Recently Used or LRU) and probability theory (through the learning of data access pattern) to efficiently use this precious buffer cache memory. The primary function of the Database Buffer Cache is to optimize the physical disk I/O while read and update:

•         When user updates record, the database updates the data in the cache first and stores metadata about the changes in the redo log buffer. Then later on Database Writer (DBWR) process performs lazy writes in the background to maximize the database performance
•      When user read records, they’re accessed from the cache first and avoid expensive disk I/O. It keeps the frequently accessed blocks in the buffer and move the infrequently accessed blocks to the disk (using the LRU algorithm)

DBWR process is directly involved in reading and writing on the the Database Buffer Cache area and LGWR is indirectly associated with this area. PMON and SMON are also access the Database Buffer Cache when a process is failed or terminated abruptly and do cleanup and recovery. LGWR doesn’t access it directly but DBWR process’s write to disk may get impacted due to the LGWR process’s completion of write of the redo log buffer to the disk due to the mandate of the write-ahead protocol by the Oracle Database

 Redo Buffer Cache

The Redo Buffer Cache or Redo Log Buffer is a circular buffer in the SGA that stores redo entries, which is a data structure that contains the information necessary to reconstruct changes to the database. Server Processes write the redo entry to the Redo Log Buffer cache. The use of Redo buffer dramatically improves the performance of the database as the users don’t need to wait for the changes to be persisted to the disk (which are very expensive I/O) but red entries written to the redo log buffer cache by database processes are considered as the changes are done. To avoid data loss due to the database instance crash or malfunction, a background process (LGWR) sequentially writes the records from the Redo Log Buffer to the online redo log group on disk. This is very fast process as it goes sequentially from the redo log to online redo log. When recovery of data is necessary by PMON and SMON processes, the Redo Log Buffer Cache is read and reconstructs the lost record

The Shared Pool

The Shared Pool is a container memory area of various subcomponents like Library Cache, Data Dictionary Cache, Server Result Cache, Reserved Pool etc. It caches various types of program data like, parsed SQL, PL/SQL code, System Parameters, and data dictionary information. Almost every operation that occurs in the database does access data from the shared pool

Source: Oracle® Database Concepts 12c Release 1 (12.1)

The Data Dictionary Cache

Data Dictionary Cache, also known as Row Cache, is a collection of database tables and views containing information about the database, its structure, and its users. This information is used by all server processes to perform their specific function, for example – to parse a SQL statement and create the execution plan the data dictionary is being used. As this cache is used very often by the server processes, holding the data dictionary information in the SGA memory area improves the performance of Oracle Database

The Library Cache

Library Cache is a SGA memory component that falls under the Shared Pool and used to hold the executable SQL and PL/SQL code and the control structures such as locks and library cache handles. The Library cache has a variation depending on the architecture. If it’s in dedicated mode, it only contains Shared SQL area but in shared server architecture mode, it has a Private SQL area. Shared SQL area holds the first occurrence of a SQL statement and the Private SQL area holds for each of the sessions. Private SQL area can be associated to the same Shared SQL area.

The storage of executable SQL and PL/SQL statements helps to tremendously improve performance of the database processing. Every time there is a request from user or an application to execute a statement, it first looks up into this cache area and if it’s syntactically and semantically same, it just reuses the same parsed statement. But if the server process doesn’t find the exact same, it parses the statement and subsequently loads that into the Library cache for future use.

Like the Database Buffer cache, this cache also uses the LRU algorithm to optimize the use of the memory location and to increase the probability of Library cache hit. Apart from the LRU algorithm, it also recycles any statement if the underlying objects used by the statement are changed, for example: if the database gathers statistics for a table, table cluster, or index, and change in the database object through DDL statements as well as change in the global database name.

All Server processes use the Library cache to improve the efficiency of SQL and PL/SQL statements execution