Oracle Architecture

Oracle Server:

As you can see on top of the hierarchy is Oracle Server. But before going in the details let us start with a very important question. It will seem to you very simple but even many DBAs have no clear answer to this. The question is: what is Oracle. I mean we all know that Oracle is the name of a corporation but what else the word Oracle refers to. Is it the name of a database or it is the name of developer tool or something else? The word Oracle does not refer to a database nor to the developer tool it is something else. And that something else is DBMS. Oracle is the name of the DBMS that controls the Oracle database. And remember that Oracle is not a relational DBMS: it is an Object Relational DataBase Management System (O-RDBMS).

As I told you that Oracle is the name of a DBMS. It is its short term (Oracle). Its full name is Oracle Server. So Oracle Architecture means the Architecture of Oracle Server. Oracle Server has two parts. One is Physical and the second is Logical. Physical Part is the part that resides on the Hard Disc; and Logical Part resides in RAM.

Database (Physical Part):

The physical part of Oracle is database. Database is an informational unit. The two fundamental purposes of database are data storage and retrieval. So database stores the data in such an organized way that it can be efficiently and effectively retrieved. Database resides in the hard disc in the form of different kind of files. There are generally three kinds of files.

Data Files: Those files that contain the actual data. It is particularly user's data. There are different kinds of structures in which data is stored. The most fundamental of which is block.

Redo Log Files: These files contain the log information of the database. They keep track of which data item is stored, retrieved, or modified. They must be kept secure. Oracle does the data recovery through these files. In the case of a system failure if these files are lost than Oracle will not be able to recover the database.

Control Files: Oracle also maintains the information about the database for itself. Oracle stores that information in the control files. They are normally known as the heart of the database. These files keep the information e.g. where the data files and redo log files are actually stored. The exact path of where they are located etc.

Instance (Logical Part):

In very simple words Instance is known as the mean to access the database. Every time you want to get access to the database you must connect through an Instance. Every database has its own Instance which lets its users to communicate with it.

An Oracle Instance consists of two things:

1. Background Processes
   
2. Memory Structures
   

Background Processes: Oracle uses a number of different background processes to perform different kind of activities. The mandatory background processes of Oracle are:

1. Database Writer (DBWR): Suppose you are working on a database. You need to change any part of the table. When you do this you also need to make the change in the actual file for consistency. The process that allows you to write in the database or to change contents of the database is DBWR. Thus it is the only process that can write in database directly.
   
2. Log Writer (LGWR): Its functionality is almost the same of DBWR; the only difference is that it writes or updates the redo log files instead of data files. The updating of redo log files is very important because the whole recovery procedure in case of system failure is dependent on these files.
   
3. Check Point (CKPT): Although the DBWR is the only process that has direct access to the database; but in actual the DBWR is an idle process. It is the CKPT that invokes the DBWR to perform the write operation on the database after a specific time interval known as Check Point.
   
4. System Monitor (SMON): System Monitor as specified by its name it keeps track of the entire system. In case of a system failure it is this process that performs the recovery procedure. It also does the defragmentation of the disc.
   
5. Process Monitor (PMON): In case of abort termination of database the Process Monitor checks which process was using the database. It also performs the resource cleanup; release of resources from the process.
   

Memory Structures: When an Oracle instance is started a set of memory structures are allocated to it. These memory structures are collectively called System Global Area or SGA for short. Every Oracle Instance has its own SGA. The most commonly used memory structures are:

1. Database Buffer Cache: When you retrieve information from a database it is first stored in the database buffer cache. It contains the most recently used data for easy reference if that data is needed again soon. This is a great performance improvement because in the case of a cache hit a lot of time is saved which would have been wasted in retrieving data from disc.

The term buffer is used to represent a minimum storage unit. Normally the size of the buffer is equal to the size of data block on the hard disc. Further more almost all buffer caches mentioned here work on least recently used algorithm. Part of the data that is least used will be written back to disc.

2. Redo Log Buffer Cache: It is said to be the most important component of Oracle Instance. It stores the most current log information of the database. Just like data is modified and stored from database buffer cache; log information is written back from this cache. In case of a system failure data is recovered from this log information. And if you had not written back the log information to disc than data lose through a system crash will be permanent.

3. Shared Pool: Shared Pool stores two important kind of information and therefore has two parts:

1. Data Dictionary Cache: - Data dictionary cache stores the metadata of the database in which you are working. It stores information like the database name, instance name etc.
   
2. Library Cache: - It is used to store information about commands and queries applied by the user. It has two further parts:
   
   • SQL Cache: - As suggested by its name it stores information about most recently used SQL commands.
     
   • PL/SQL Cache: - It stores information about most recently used blocks of code written in PL/SQL.
     

Shared Pool has a great role in the performance of the database. Suppose you have given a select statement to fetch records from a table. What happens here is the Oracle calculates a hash value of every SQL command and stores it in the SQL cache. Next time the command is checked; if its hash value is present in the SQL cache than it will not go under semantics and syntactical analysis again. This saves a lot of CPU time of processing the same command again and again.

4. Large Pool: It is not mandatory part. Rather it is an optional part of Oracle Instance. It is mainly used when the Oracle is running in shared mode. And shared pool is not enough to hold every thing.

5. Java Pool: As suggested by its name it is only used when you are using java with oracle. Thus it is also an optional part.