Oracle Database Instance Architecture

 • An Oracle database server consists of at least one database instance

and a database. - A database is a set of files, located on disk, that store data. - An instance is a set of memory structures and processes that manage the

database files.

• An instance consists of a shared memory area, called the system

global area (SGA), and a set of background processes.

• For each user connection to the instance, there is a client process.

Each client process is associated with its own server process. The

server process has its own private session memory, known as the

program global area (PGA).

System Global Area (SGA) Components

• Shared Pool - Library Cache: stores executable SQL and PL/SQL code. - Data Dictionary Cache: holds info about accessed database objects

- Server Result Cache: holds result sets (not data blocks). It contains the

SQL query result cache and PL/SQL function result cache.

• Database buffer cache: the memory area that stores copies of data

blocks read from data files to optimize physical I/O.

• Large Pool: (optional) large memory allocation for buffers for RMAN,

buffers for deferred inserts, message buffers used parallel executions,

and others.

• Redo Log buffer: used to save redo entries

Buffer (Data Blocks) States

• A buffer can be in any of the following mutually exclusive states: - Unused: the buffer not been used or accessed

- Clean: this buffer was used earlier and now contains a read-consistent

version of a block as of a point in time. - Dirty: the buffer contain modified data that has not yet been written to disk