Oracle database Architecture - Part 1

Oracle database is widely used RDBMS for nearly 2 decades and has become most successful database. In this article, we will see how the oracle database architecture is designed and how db works both at memory and OS level.

Oracle database server is categorized into 3 parts.

1. Oracle memory structures

2. Oracle background structures and

3. Oracle disk utilization structures.

Oracle memory structures consist of two main components.

i.                     System Global Area (SGA)

ii.                   Program Global Area (PGA)

SGA – It is a RAM of the database and this is the first and most important component.  Whenever DBA speaks about memory of the database, it means that it is SGA.

SGA stores different components of memory usage that are designed to fetch the data quickly for users and to maximize the number of concurrent users to access the oracle instance.

SGA consists of 3 mandatory parts.

a.  Buffer cache

b.  Redo log buffer

c. Shared pool

Buffer cache consists of buffers which are in the size of database blocks. These are designed to store data blocks recently used by the SQL statements issued by the user. If the recently used  blocks are used in the buffer cache, the performance will improvise on subsequent fetching of the data using select queries.

Redo log buffer allows the user processes to write their redo log entries to the memory area in order to increase the speed on tracking the database changes.  It is important to remember that every user processes that makes a change to database must write an entry to the redo log in order to allow the database to recover the changes. If the database set up to archive redo logs, the database changes are kept  in order to rebuild the database objects in the event of disk failure.  The main purpose of having the redo information in memory is that it avoids the need for the  user process to spend extra time to write directly on the physical files (redo log files on the disk). By doing so, oracle database avoids contention on the disk usage which would in-turn would slow down the database.

Shared pool is a another most important mandatory component of Oracle memory. It consists of

1.       Library cache

2.       Data Dictionary cache (also known as “row” cache)

Shared SQL library cache is designed to store the parse information of SQL queries executed against the database. Parse information includes the set of database operations that SQL execution performs to obtain the data (i.e., Parse phase,execute phase and fetch phase). This information is a shared resource in the library cache. When another user session/process executes for same query again, oracle will first check whether the query is existing in the shared pool (library cache) and if it is available, then the parse information in the shared pool will be used. But, the data returned to the user will not be shared in the shared pool, because sharing data between applications represent a integrity/security issue.

Data dictionary cache or “row” cache as referred is other mandatory component of shared pool. This is used to store data from data dictionary in order to improve response time on the data dictionary views.  Since all user processes and oracle database internal processes use data dictionary, the database benefits a lot in performance when data dictionary objects are cached in memory.

We will see more about shared pool architecture and its working in a different post.