Oracle Architecture (Basic):
Below is the typical architecture of Oracle 11g. When you fire a SQL from any of the client tools like (SQL Developer/Toad/ or even SQLPLUS) below processes gets triggered at the back end.
Background Processes: It start with the database instance and perform maintenance tasks such as performing instance recovery, cleaning up processes, and writing redo buffers to disk, and so on.
Server Processes: perform work based on a client request.
Credits: docs.oracle.com
Terminologies & Functionality:
1. SGA
System global area. A group of shared memory structures that contain data and control information for one Oracle database instance.
2. PGA
Program global area. A memory buffer that contains data and control information for a server process.
3. Shared pool
Portion of the SGA that contains shared memory constructs such as shared SQL areas.
4. Large pool
Optional area in the SGA that provides large memory allocations for backup and restore operations, I/O server processes, and session memory for the shared server and Oracle XA.
5. Database buffer cache
The portion of the system global area (SGA) that holds copies of data blocks. All client processes concurrently connected to the instance share access to the buffer cache.
6. Redo log buffer
Memory structure in the SGA that stores redo entries—a log of changes made to the database. The database writes the redo entries stored in the redo log buffers to an online redo log file, which is used if instance recovery is necessary.
7. Online redo log
The set of two or more online redo log files that record all changes made to Oracle Database data files and control file. When a change is made to the database, Oracle Database generates a redo record in the redo buffer. log writer (LGWR) writes the contents of the redo buffer to the online redo log.
Redo Log has set of files that protect altered database data in memory that has not been written to the data files. The redo log can consist of two parts: the online redo log and the archived redo log.
8. Shared SQL area:
An area in the shared pool that contains the parse tree and execution plan for a SQL statement. Only one shared SQL area exists for a unique statement.
9. Back Ground Processes:
A process that consolidates functions that would otherwise be handled by multiple Oracle programs running for each client process. The background processes asynchronously perform I/O and monitor other Oracle processes.
1. Mandatory Background Processes
These processes run by default in a database instance started with a minimally configured initialization parameter file
i. Process Monitor Process (PMON) :
PMON is responsible for cleaning up the database buffer cache and freeing resources that the client process was using. PMON also registers information about the instance and dispatcher processes with the Oracle Net listener
ii. System Monitor Process (SMON): is in charge of a variety of system-level cleanup duties like Performing instance recovery, Recovering terminated transactions, cleaning up unused temporary segments, Coalescing contiguous free extents.
iii. Database Writer Process (DBWn): It writes the contents of database buffers to data files. Typically database buffer is the memory area that stores copies of data blocks read from data files.
iv. Log Writer Process (LGWR) :
The log writer process (LGWR) manages the redo log buffer. LGWR writes one contiguous portion of the buffer to the online redo log.
v. Checkpoint Process (CKPT) :
The checkpoint process (CKPT) updates the control file and data file headers with checkpoint information and signals DBWn to write blocks to disk
vi. Manageability Monitor Processes (MMON and MMNL):
The manageability monitor process (MMON) performs many tasks related to the Automatic Workload Repository (AWR). For example, MMON writes when a metric violates its threshold value, taking snapshots, and capturing statistics value for recently modified SQL objects.
The manageability monitor lite process (MMNL) writes statistics from the Active Session History (ASH) buffer in the SGA to disk. MMNL writes to disk when the ASH buffer is full.
vii. Recoverer Process (RECO)
In a distributed database, the Recoverer process (RECO) automatically resolves failures in distributed transactions. The RECO process of a node automatically connects to other databases involved in an in-doubt distributed transaction.
2. Optional Background Processes:
i. Archiver Processes (ARCn):
The Archiver processes (ARCn) copy online redo log files to offline storage after a redo log switch occurs. These processes can also collect transaction redo data and transmit it to standby database destinations. ARCn processes exist only when the database is in ARCHIVELOG mode and automatic archiving is enabled.
ii. Job Queue Processes (CJQ0 and Jnnn):
Oracle Database uses job queue processes to run user jobs, often in batch mode.
iii. Flashback Data Archiver Process (FBDA):
It archives historical rows of tracked tables into Flashback Data Archives. When a transaction containing DML on a tracked table commits, this process stores the pre-image of the rows into the Flashback Data Archive.
iv. Space Management Coordinator Process (SMCO) :
The SMCO process coordinates the execution of various space management related tasks, such as proactive space allocation and space reclamation. SMCO dynamically spawns slave processes (Wnnn) to implement the task.
3. Slave Processes
Slave processes are background processes that perform work on behalf of other processes.
i. I/O Slave Processes:
I/O slave processes (Innn) simulate asynchronous I/O for systems and devices that do not support it. In asynchronous I/O, there is no timing requirement for transmission, enabling other processes to start before the transmission has finished.
ii. Parallel Query Slaves:
In parallel execution or parallel processing, multiple processes work together simultaneously to run a single SQL statement. By dividing the work among multiple processes, Oracle Database can run the statement more quickly. For example, four processes handle four different quarters in a year instead of one process handling all four quarters by itself.