ORACLE DATABASE ARCHITECTURE — COMPLETE GUIDE FOR BEGINNERS
Introduction
Understanding Oracle Database Architecture is one of the most important fundamentals for an Oracle DBA. It helps us understand how Oracle manages memory, processes, SQL execution, data blocks, transactions, and database files.
In this article, we will understand the Oracle architecture from the basic level, including:
- Client Process
- Server Process
- Oracle Instance
- SGA
- PGA
- UGA
- Shared Pool
- Database Buffer Cache
- Redo Log Buffer
- Large Pool
- Java Pool
- Streams Pool
- Background Processes
- SQL Parsing and Execution
1. Oracle Database Architecture — High-Level View
Oracle Database can be broadly understood as two major parts:
CLIENT / USER
|
|
Client Process
|
v
Server Process
|
+-----------+-----------+
| |
v v
SGA PGA
| |
+-----------+-----------+
|
Oracle Instance
|
+-------------+-------------+
| |
Background Processes Memory Structures
| |
v v
Database SGA / PGA
|
v
Datafiles / Controlfiles /
Online Redo Log Files
The database consists of physical files, while the instance is the set of memory structures and processes that manage those files.
2. Client Process
A client process is the process running on the client side that sends SQL statements or requests to the Oracle database and receives the results.
User | v SQL Developer / SQL*Plus / Application | v Client Process | v Oracle Database
Examples of client applications include:
- SQL*Plus
- Oracle SQL Developer
- Application servers
- Custom applications
The client process does not directly perform the database work. It communicates with the Oracle server through a connection.
3. Server Process
A server process runs on the database server and performs work on behalf of the client process.
The server process can:
- Receive SQL statements
- Parse SQL
- Check privileges
- Execute SQL
- Read data blocks
- Perform sorting and other operations
- Return results to the client
There are two major types of server processes.
1. Dedicated Server Process
In a dedicated server architecture, one client connection is associated with one dedicated server process.
Client 1 ----> Dedicated Server Process 1 Client 2 ----> Dedicated Server Process 2 Client 3 ----> Dedicated Server Process 3
This is commonly used for many Oracle database environments.
2. Shared Server Process
In a shared server architecture, multiple client sessions can share a smaller number of server processes.
Client 1 ----\ Client 2 -----\ Client 3 ------> Dispatcher --> Shared Server Processes Client 4 -----/
This can be useful when there are a large number of connections and the sessions do not continuously require a dedicated server process.
Checking the Server Process Type
The following query can be used to check the server type for sessions:
SELECT sid, serial#, username, server FROM v$session;
The SERVER column can show values such as:
DEDICATED SHARED
4. Oracle Instance
An Oracle instance is a combination of:
- Memory structures
- Background processes
ORACLE INSTANCE
|
+------+------+
| |
Memory Processes
Structures Background
| |
v v
SGA PMON / SMON /
DBWn / LGWR /
CKPT / ARCnThe instance is generally considered a temporary structure because it exists while the Oracle instance is running.
When the database instance is started, Oracle allocates memory and starts the required background processes. When the instance is shut down, those instance-level memory structures are released.
5. SGA — System Global Area
SGA stands for System Global Area.
The SGA is a shared memory area used by Oracle Database for storing information required by multiple sessions. It contains several important memory structures.
Major SGA components include:
- Shared Pool
- Database Buffer Cache
- Redo Log Buffer
- Large Pool
- Java Pool
- Streams Pool
SGA
|
+-----------+-----------+
| | |
Shared Pool Buffer Cache Redo Buffer
|
+----+----+
| | |
Library Data Result
Cache Dictionary Cache
Cache
+----------------+
| Large Pool |
| Java Pool |
| Streams Pool |
+----------------+You can check SGA-related parameters using:
SHOW PARAMETER SGA;
You can also check the database name using:
SHOW PARAMETER DB_NAME;
6. Shared Pool
The Shared Pool is one of the most important components of the SGA.
It stores information that can be shared among sessions, such as:
- Parsed SQL
- PL/SQL code
- Execution plans
- Data dictionary information
- Result cache information
The Shared Pool contains important components such as:
- Library Cache
- Data Dictionary Cache
- Result Cache
- Reserved Pool
7. Library Cache
The Library Cache stores SQL and PL/SQL-related information.
When a SQL statement is submitted, Oracle performs several steps before executing it. The basic flow is:
SQL Statement
|
v
Syntax Check
|
v
Semantic Check
|
v
Parsing
|
v
Optimization
|
v
Execution
|
v
FetchingSyntax Checking
Oracle checks whether the SQL statement follows the correct SQL syntax. Example:
SELEC * FROM employees;
The statement contains a syntax error.
Semantic Checking
Oracle checks whether the statement is meaningful and whether the user has the required privileges. For example:
SELECT * FROM hr.employees;
Oracle needs to verify that the object exists and that the user has the required privileges.
Parsing
Parsing can involve:
- Hard Parse
- Soft Parse
Hard Parse — A hard parse occurs when Oracle needs to generate a new execution plan and perform the required parsing work. Hard parsing is relatively expensive compared with a soft parse.
Soft Parse — If Oracle can reuse an existing suitable parsed representation and execution information, it can perform a soft parse.
8. SQL Execution Flow
A simplified Oracle SQL processing flow is:
1. Syntax Check
|
2. Semantic Check
|
3. Parse
| |
Hard Soft
Parse Parse
|
4. Optimization
|
5. Execution
|
6. FetchThe optimizer determines an appropriate execution plan based on available information such as statistics, indexes, predicates, joins, and other factors.
You can inspect SQL information using:
SELECT sql_text,
plan_hash_value,
disk_reads,
parse_calls,
loads,
executions
FROM v$sql
WHERE sql_text LIKE 'select * from scott.dept%';The V$SQL view is useful for examining SQL statements currently represented in the shared SQL area.
9. Data Dictionary Cache
The Data Dictionary Cache, also called the Row Cache, stores information about database objects and database metadata.
It can contain information related to:
- Users
- Privileges
- Tables
- Datafiles
- Segments
- Database objects
- Other database metadata
This information allows Oracle to efficiently access metadata required during SQL processing.
You can examine the data dictionary cache using:
DESC V$ROWCACHE; SELECT * FROM V$ROWCACHE;
10. Result Cache
The Result Cache can store the result of eligible queries so that Oracle can potentially reuse the result instead of performing the same expensive operation again.
For example, a query involving:
- Joins
- Aggregations
- Expensive calculations
may benefit when the result can be reused. A SQL statement can provide a result-cache hint:
SELECT /*+ RESULT_CACHE */
*
FROM employees;The availability and behavior of result caching depend on Oracle configuration and whether the query is eligible for caching.
11. Shared Pool Reserved Area
Oracle has a reserved area associated with the Shared Pool that can help satisfy certain larger memory allocation requests. The parameter associated with this area is:
SHOW PARAMETER shared_pool_reserved_size;
This mechanism can help reduce failures caused by fragmentation and certain large allocation requests in the shared pool. An important error associated with shared pool memory pressure is:
However, increasing the reserved pool alone is not a universal solution for ORA-04031. The underlying memory pressure and allocation problem should also be investigated.
12. Database Buffer Cache
The Database Buffer Cache is an important part of the SGA. It stores copies of data blocks read from datafiles.
Datafile | | Read v Database Buffer Cache | v SQL Processing
When Oracle needs a data block, it first checks whether the required block is already available in the buffer cache. The buffer cache is also a major area where database blocks are modified before being written to the datafiles.
Buffer States
Some commonly discussed buffer states include:
- Free — A buffer that can be used for another block.
- Dirty — A buffer containing a modified block that has not yet been written to the corresponding datafile.
- Pinned — A buffer that is currently being accessed or otherwise cannot be immediately replaced.
Oracle manages buffer usage using sophisticated algorithms, including LRU-related mechanisms.
13. Redo Log Buffer
The Redo Log Buffer is an SGA memory structure. When database changes occur, Oracle generates redo information describing those changes. For example:
INSERT UPDATE DELETE
and many other database operations generate redo. The simplified flow is:
SQL Operation
|
v
Redo Information
|
v
Redo Log Buffer
|
v
LGWR
|
v
Online Redo Log FilesThe LGWR (Log Writer) background process writes redo information from the redo log buffer to the online redo log files. Redo is critical for database recovery.
14. Large Pool
The Large Pool is an optional SGA component designed for certain larger memory allocations that Oracle should not take from the shared pool.
It can be used for areas such as:
- Shared server-related memory
- RMAN backup and recovery operations
- Parallel execution-related memory in appropriate configurations
The Large Pool helps separate these allocations from normal shared pool allocations. Parameter:
SHOW PARAMETER large_pool_size;
15. Java Pool
The Java Pool is used for Java-related operations inside the Oracle database, including memory required by the Oracle JVM when Java functionality is installed and used. It is primarily relevant to environments that use database Java features. Parameter:
SHOW PARAMETER java_pool_size;
16. Streams Pool
The Streams Pool is an SGA memory area associated with Oracle Streams functionality. It can provide memory for processes and operations related to Oracle Streams.
The Streams feature is legacy technology in modern Oracle environments, but understanding the Streams Pool is useful when working with older Oracle architectures and systems.
17. PGA — Program Global Area
PGA stands for Program Global Area.
The PGA is a private memory area associated with an Oracle server process. Unlike the SGA, which is shared among Oracle processes, PGA memory is not shared between server processes in the same way.
The PGA can contain information required by the server process for operations such as:
- Sorting
- Hash operations
- Session-related information
- Cursor state
- Other work areas
Server Process
|
v
PGA
+--------+--------+
| | |
Sort Hash Session/
Area Area Process Info18. PGA Memory Management
In modern Oracle databases, automatic PGA memory management is commonly configured using:
SHOW PARAMETER pga_aggregate_target;
For example:
ALTER SYSTEM SET pga_aggregate_target=2G SCOPE=BOTH;
The actual configuration should be based on the database workload and available system memory. Oracle can automatically manage work areas based on the configured PGA memory management settings.
19. Sorting in PGA
When Oracle performs a sort operation, it tries to perform the operation in memory. For example:
SELECT * FROM employees ORDER BY salary;
If sufficient PGA work area memory is available, the sort can be performed in memory. This is commonly called an in-memory sort. If the required memory is not sufficient, Oracle can use temporary space in the TEMP tablespace.
Sort Operation
|
+------+------+
| |
Enough PGA Not enough PGA
| |
v v
Memory Sort TEMP TablespaceDisk-based or temporary-space-intensive operations can be significantly more expensive than operations completed entirely in memory.
20. UGA — User Global Area
UGA stands for User Global Area.
The UGA contains session-related information for a user session. Its location can depend on the server architecture:
- In a dedicated server environment, UGA is generally located in the PGA.
- In a shared server environment, session state that belongs in UGA is stored in the SGA, typically in the Large Pool when configured appropriately.
This distinction is important when explaining Oracle memory architecture.
21. Oracle Background Processes
Oracle uses background processes to perform many important database operations. Some major background processes are:
| Process | Full Form | Main Responsibility |
|---|---|---|
| PMON | Process Monitor | Monitors and cleans up failed user/server processes |
| SMON | System Monitor | Performs instance recovery and system-level cleanup |
| DBWn | Database Writer | Writes modified database blocks to datafiles |
| LGWR | Log Writer | Writes redo from redo log buffer to online redo logs |
| CKPT | Checkpoint | Coordinates checkpoint-related operations |
| ARCn | Archiver | Archives filled online redo log groups in ARCHIVELOG mode |
22. PMON — Process Monitor
PMON stands for Process Monitor. PMON performs cleanup and recovery-related tasks when a user or server process fails. Examples include:
- Cleaning up failed process resources
- Releasing certain resources held by failed processes
- Performing related process-monitoring tasks
23. SMON — System Monitor
SMON stands for System Monitor. One of its major responsibilities is instance recovery when required. For example, if an instance crashes unexpectedly, SMON participates in recovering the database when the instance is restarted. SMON also performs other system-level cleanup and maintenance activities.
24. DBWn — Database Writer
DBWn stands for Database Writer. DBWn writes modified database blocks from the database buffer cache to the datafiles.
Database Buffer Cache
|
| Dirty Blocks
v
DBWn
|
v
Datafiles25. LGWR — Log Writer
LGWR stands for Log Writer. LGWR writes redo information from the redo log buffer to the online redo log files.
Transaction
|
v
Redo Log Buffer
|
v
LGWR
|
v
Online Redo LogLGWR plays a critical role in transaction durability.
26. CKPT — Checkpoint Process
CKPT stands for Checkpoint Process. A checkpoint helps coordinate the writing of modified database blocks and updates checkpoint-related information in the database. Checkpoints are important for:
- Database consistency
- Recovery
- Reducing the amount of work required during instance recovery
27. ARCn — Archiver Process
ARCn stands for Archiver. When the database is operating in ARCHIVELOG mode, ARCn processes archive filled online redo logs to archive destinations.
Online Redo Logs
|
v
ARCn
|
v
Archived Redo LogsArchived redo logs are extremely important for:
- Point-in-Time Recovery
- Media Recovery
- RMAN backup and recovery
- Data Guard
28. Complete Oracle Architecture Flow
Now let's combine the major concepts.
USER
|
v
CLIENT PROCESS
|
v
SERVER PROCESS
/ \
/ \
PGA SGA
|
+-----------------------+----------------------+
| | | |
v v v v
Shared Pool Buffer Cache Redo Log Buffer Other Pools
|
+----+-----+
| |
v v
Library Data Dictionary
Cache Cache
|
v
SQL Parsing / Optimization
|
v
Execution
|
v
Database Buffer Cache
|
+-------------------+
| |
v v
DBWn LGWR
| |
v v
Datafiles Online Redo Logs
|
v
ARCn
|
v
Archive Logs
29. Oracle Instance vs Oracle Database
This is a very common interview question.
Oracle Instance
An Oracle instance consists of:
Memory Structures + Background Processes
Main components:
- SGA
- PGA associated with server processes
- PMON
- SMON
- DBWn
- LGWR
- CKPT
- ARCn
- Other Oracle processes
Oracle Database
The database consists of physical files such as:
- Datafiles
- Control files
- Online redo log files
Simplified view:
Oracle Instance
|
| manages
v
Oracle Database
|
+-----+------+------+
| | |
Datafiles Control Redo Logs
Files30. Important DBA Commands
Check SGA parameters
SHOW PARAMETER SGA;
Check PGA parameters
SHOW PARAMETER PGA;
Check database name
SHOW PARAMETER DB_NAME;
Check server process type
SELECT sid, serial#, username, server FROM v$session;
Check Library Cache
DESC V$LIBRARYCACHE; SELECT namespace FROM V$LIBRARYCACHE;
Check Data Dictionary Cache
DESC V$ROWCACHE;
Check SQL information
SELECT sql_text,
plan_hash_value,
disk_reads,
parse_calls,
loads,
executions
FROM v$sql
WHERE sql_text LIKE 'select * from scott.dept%';31. Important Interview Points
- Q1. What is an Oracle Instance?
An Oracle instance is a combination of memory structures and background processes used to manage an Oracle database. - Q2. What is SGA?
SGA is a shared memory area containing components such as the Shared Pool, Database Buffer Cache, and Redo Log Buffer. - Q3. What is PGA?
PGA is private memory associated with an Oracle server process and is used for SQL execution work areas and process/session-related information. - Q6. What is a Dedicated Server?
A dedicated server process is assigned to a client connection and performs database work for that connection. - Q7. What is a Shared Server?
Shared server allows multiple client sessions to share a pool of server processes through dispatchers. - Q8. What is the purpose of the Database Buffer Cache?
It stores copies of database blocks read from datafiles and provides the working area for modifying those blocks. - Q9. What is the purpose of the Redo Log Buffer?
It temporarily stores redo entries generated by database changes before LGWR writes them to online redo log files.
Q4. What is the difference between SGA and PGA?
| SGA | PGA |
|---|---|
| Shared memory | Private process memory |
| Shared among Oracle processes | Associated with individual server processes |
| Contains buffer cache, shared pool, redo buffer, etc. | Contains work areas and process/session-related information |
| Part of the instance | Allocated to server/background processes as required |
Q5. What is the difference between DBWn and LGWR?
DBWn writes modified database blocks to datafiles.
LGWR writes redo information to online redo log files.
Conclusion
Oracle Database Architecture explains how a client request travels through the Oracle environment and how Oracle uses memory and processes to execute that request.
The most important concepts to remember are:
Client Process
↓
Server Process
↓
PGA + SGA
↓
SQL Parsing
↓
Execution
↓
Database Buffer Cache
↓
DBWn → Datafiles
Redo Generation
↓
Redo Log Buffer
↓
LGWR
↓
Online Redo Logs
↓
ARCn
↓
Archive LogsFor an Oracle DBA, a strong understanding of Instance, SGA, PGA, Server Processes, Shared Pool, Buffer Cache, Redo Log Buffer, and Background Processes provides the foundation for learning advanced topics such as RMAN, RAC, Data Guard, Performance Tuning, ASM, and Troubleshooting.