Oracle Database Architecture and Memory Management

An Oracle Instance consists of memory structures (SGA and PGA) and background processes. The System Global Area (SGA) is a shared memory region containing data and control information for one Oracle instance. Background processes are processes that handle various tasks like database writing, log writing, and process monitoring. Physical files and the database itself are storage components, not instance components.

SGA_TARGET is the initialization parameter that defines the size of the System Global Area (SGA) in Oracle 10g and later versions. This parameter allows for automatic SGA management where Oracle dynamically tunes memory components within the SGA. SGA_SIZE is not a valid parameter, and SGA_TARGET_SIZE or SGA_INIT_SIZE are not standard Oracle parameters.

PGA (Program Global Area) is a memory region that contains data and control information for a server process. It is allocated when a server process is created and is exclusive to that process. The PGA is non-shared memory, unlike the SGA which is shared across all processes. Each user session gets its own PGA allocation.

A database session must be established before you can access any data in Oracle. The session authenticates the user and allocates the necessary server process and PGA memory. Without establishing a connection and session, there is no context through which SQL statements can be executed and data can be retrieved.

A cache hit occurs when requested data is found in the database buffer cache, eliminating the need to read from disk. This is much faster than a cache miss, which requires reading from data files on disk. The buffer cache is part of the SGA and stores frequently accessed data blocks to improve performance.

The database buffer cache internally manages three types of buffers: free buffers (available for use), dirty buffers (modified but not yet written to disk), and pinned buffers (currently being read or modified). Access buffers is not a standard buffer cache type. The buffer cache uses these states to manage data blocks efficiently and maintain data consistency.

PGA memory is configured using the PGA_AGGREGATE_TARGET parameter, which specifies the total memory for all server processes combined. You cannot specify memory per individual server process. Oracle dynamically allocates PGA memory from this aggregate target to individual processes as needed, optimizing overall memory usage.

The redo log buffer is a volatile memory area in the SGA that holds redo entries temporarily before they are written to disk. On instance failure, the contents of the redo log buffer are lost. Recovery uses the online redo log FILES (on disk) and archived redo logs, not the buffer contents. The buffer is just a staging area, not a recovery source.

LGWR (Log Writer) is the Oracle background process responsible for writing redo entries from the redo log buffer in the SGA to the online redo log files on disk. DBWR writes data blocks, CKPT is the checkpoint process, and ARCH archives redo logs. LGWR is the only process that writes redo log buffer to redo log files.

The Shared Pool in the SGA contains the Library Cache (which stores parsed SQL and PL/SQL) and the Data Dictionary Cache (which caches data dictionary information). The Data Buffer Cache is a separate SGA component for caching data blocks. SQL buffer cache is not a standard Oracle term - the Library Cache handles SQL caching.

When data is found in the buffer cache, transaction throughput is FASTER because Oracle avoids expensive disk I/O operations. The buffer cache is designed specifically to improve performance by keeping frequently accessed data blocks in memory. Accessing memory is orders of magnitude faster than reading from disk.

The Large Pool is an optional area of the SGA used for RMAN backups, Recovery Manager operations, and shared server connections. The Shared Pool, Redo Log Buffer, and (in most configurations) Java Pool are mandatory components required for basic database operation. Large Pool can be configured but is not required.

SMON (System Monitor) is the Oracle background process responsible for instance recovery. It performs recovery at startup by rolling forward changes from redo logs and rolling back uncommitted transactions. PMON monitors and recovers user processes, while IMON and INSTMON are not standard Oracle processes.

Cursor bind information and private runtime values are stored in the Private SQL Area within the PGA (Process Global Area). Each user session has its own Private SQL Area containing bind variable values, execution state, and other session-specific SQL information. The Cursor and SQL area terminology is less precise.

DBWR (Database Writer) is the Oracle background process responsible for writing modified data blocks from the database buffer cache to the data files. LGWR writes redo logs, PMON manages processes, and CKPT updates headers.

When a SQL query is parsed, Oracle first checks the Data Dictionary Cache to verify that referenced tables, columns, and other objects exist and the user has proper privileges. The Library Cache stores parsed SQL, but object definitions come from the Data Dictionary Cache. The buffer cache stores data blocks, not metadata.