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
📌 Key concept: Oracle Instance = Memory Structures + Background Processes

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:

SQL
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:

  1. Memory structures
  2. Background processes
        ORACLE INSTANCE
              |
       +------+------+
       |             |
     Memory       Processes
   Structures     Background
       |             |
       v             v
      SGA       PMON / SMON /
                DBWn / LGWR /
                CKPT / ARCn

The 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:

  1. Shared Pool
  2. Database Buffer Cache
  3. Redo Log Buffer
  4. Large Pool
  5. Java Pool
  6. 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:

  1. Library Cache
  2. Data Dictionary Cache
  3. Result Cache
  4. 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
Fetching
Syntax 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. Fetch

The 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:

SQL
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:

SQL
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:

SQL
SELECT /*+ RESULT_CACHE */
       *
FROM employees;
📌 Key point: Result Cache stores query results, not database data blocks.

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:

⚠️ Error: ORA-04031: unable to allocate bytes of shared memory

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 Files

The 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 Info

18. PGA Memory Management

In modern Oracle databases, automatic PGA memory management is commonly configured using:

SHOW PARAMETER pga_aggregate_target;

For example:

SQL
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:

SQL
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 Tablespace

Disk-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:

ProcessFull FormMain Responsibility
PMONProcess MonitorMonitors and cleans up failed user/server processes
SMONSystem MonitorPerforms instance recovery and system-level cleanup
DBWnDatabase WriterWrites modified database blocks to datafiles
LGWRLog WriterWrites redo from redo log buffer to online redo logs
CKPTCheckpointCoordinates checkpoint-related operations
ARCnArchiverArchives 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
    Datafiles
📌 Important point: DBWn does not write every changed block immediately after a transaction changes it. Oracle uses various conditions and internal mechanisms to determine when blocks need to be written.
25. 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 Log

LGWR 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 Logs

Archived 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
           Files

30. 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
SQL
SELECT sid, serial#, username, server
FROM v$session;
Check Library Cache
SQL
DESC V$LIBRARYCACHE;
SELECT namespace
FROM V$LIBRARYCACHE;
Check Data Dictionary Cache
DESC V$ROWCACHE;
Check SQL information
SQL
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?
SGAPGA
Shared memoryPrivate process memory
Shared among Oracle processesAssociated with individual server processes
Contains buffer cache, shared pool, redo buffer, etc.Contains work areas and process/session-related information
Part of the instanceAllocated 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 Logs

For 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.