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.

Autonomous Transactions

Transactions are at the heart of any database management system. Autonomous transaction is a transaction that starts from a parent transaction but proceeds without any regard to the parent transaction. The typical application of autonomous transaction is of logging activity. If you want your logging activity to start on a certain event and then proceed without any regard to the activity for which the logging is taking place then the autonomous transactions are the natural solution. Oracle provides a very simple way to start an autonomous transaction. You can start an autonomous transaction in a PL/SQL block by including the "Pragma Autonomous Transaction" in the declarative section of the block. After the transaction has been started it will operate independently of the parent transaction. It does not rely on the success or failure of PL/SQL block from which it was started.

Talking about transactions; they operate without any user interaction. However Oracle allows you to have some control over their operation. The command "Set Transaction" is used to set five different arguments that will guide the transaction in its operation. The "Read only" argument makes the transaction to work only on a snapshot of data from the database. The "Read Write" argument will bring back the transaction to its default mode. The argument "Read Committed" specifies that if a user is updating a row of table and another user tries to do the same then the second user's transaction will wait for the first one's to complete. This is also the default behavior. The argument "Serializable" is almost the same as "Read Committed" except when set then the transaction of the second user will abort with an error. You can also name the rollback segment that the transaction should use while it proceeds with the argument "Rollback Segment". But remember before you do all this that the Command "Set Transaction" should be the first one in your transaction.

Data Dictionary

Data Dictionary is an important component of any database just like it is of oracle. Data Dictionary holds the meta data. By meta data we mean data about the data. Oracle maintains information about the data it stores in data dictionary. Data dictionary holds information like logical and physical structure of the database, object definitions, Integrity constraints, user privileges, and auditing.

When a database starts two scripts run. First is called sql.bsq and second is known as catalog.sql. When sql.bsq runs it creates the base tables. Base tables are the tables that contain the actual information. All the information stored in data dictionary is in the form of these base tables. When catalog.sql runs it creates views. These views are based on the base tables. Actually a user or even a DBA can’t see the base tables. Whatever information you need to retrieve from the data dictionary is available to you through these views and they are from one of the following categories.

With DBA Prefix: The views that have a prefix of DBA in their names are only visible to a DBA. You can see them when you are login as sysdba. They contain information about the entire database.

With User Prefix: The views with user as their prefix will show the user information about the objects which belong to him.

With All Prefix: The views with all prefix will show the user information about objects that belong to him and also on which he has been granted permission by some other user or DBA.

Let us clarify with an example. There are three views dba_tables, user_tables, and all_tables. The dba_tables view will show you the information about all the tables the database has. The user_tables view will show you the information about the tables which are your self created. The all_tables view will show you the information about the tables which are either your self created or on which you have permissions.

There is another category of data dictionary views Dynamic Performance Views. They contain all the dynamic information about the database which is running at that time. They all have v$ as the prefix with their names. And usually they are known as v$ views. These dynamic views take information from shared global area and from control files. Some examples of these are:

V$session: Will show you the information about current sessions.

V$instance: It will show you the information about current instance.

V$datafile: It will show you the information about currently used data files.

Modes Of Oracle Server

Oracle runs in two modes. One is Dedicated mode and the other is Shared mode. Before differentiating the two let us take a look at what happens when a user or client connects to Oracle Database.

When a user connects to Oracle using a client tool like SQL*PLUS two things happen: first user is connected to listener and second a user process is created for the user. The Listener connects the user to the Instance part of the Oracle Server. And as you know the Instance is mean to access the database. So Instance connects the user to the database. After the connection is established a Server process and Process Global Area (PGA) is created for the user. Now there is no need to be connected to the listener. All the requests of the user will now be carried out through the user process. The user process will put them in the PGA. The Server process will take the requests from PGA and will carry them out for the user. After the server process had got the data from the database which was requested by the user; it will put the data into the PGA. The user process will now take the data from the PGA and will hand it over to the user or client. Again remember that not even the server process is directly connected to the database. It is using the Instance (Background Processes + Shared Global Area) as a mean to access the database.

The default mode of Oracle server- is dedicated mode. In dedicated mode there is one user process, one server process and one PGA for each user. And the working described above takes place for each individual user. But when there are hundreds and even thousands of users connected to the database the dedicated mode is not appropriate. You must turn the server into shared mode. When Oracle is running in shared mode like in parallel server there is only one user process that will take requests from all the users connected to the database. There will be very few server processes (if not one than normally far less than the number of users) communicating with the database on behalf of the users. In shared mode there is only one PGA. The PGA here does not contain any requests or data; it consists of small UGAs (User Global Area) one for each user. The actual data and requests are kept inside individual UGAs.

Cursor Defined

When a SQL statement is issued against Oracle RDBMS then Oracle checks whether it is a valid SQL statement and also whether the user who has issued it has the appropriate permissions. If these two conditions are satisfied then a private SQL area is assigned to the statement. If this is the first time this statement is issued then it is parsed and its parsed copy is saved in the Shared SQL area for further use. Next time when this statement is issued then it is not parsed again but instead already parsed version residing in the Shared SQL area is used.

Both the Private and Shared SQL areas are part of Library Cache which in turn is part of Shared Pool. Private SQL area further consists of two more areas. One is called Persistent area which has static size and stores all the constants values used in the statement. The other is called runtime area and it is used to store all dynamic contents of the statement; for example Binding information. The cursor is nothing but a symbolic name or handle of the Private SQL area of a statement.

Oracle SQL Developer

More then a year ago Sir Fahd gave me an Oracle product as a New Year gift. The product was named as Oracle Raptor. The product was for developing SQL in an easy way then it is possible using SQL*PLUS. I did not use it for two reasons. First it is always hard for me to use something in place of SQL*PLUS and second was the presence of Oracle EM which can aid you to a great deal if you want your development to be fast. One more reason was the time it takes in starting.

The latest version of this product is released in this year’s February. I downloaded it just for fun but for my surprise it takes much less time to s art then its earlier version. Also it has some more and exciting new features. One of them of my great interest is its support for other databases then Oracle. For the time being it supports Microsoft SQL Server, Microsoft Access, MySQL and of course Oracle itself. In my office there is no Oracle type thing so I am trying to connect it to SQL Server. So far I am not been able to do it but still trying. Hoping some favor from it. All in all some appreciation has started to arise in my mind for it. Let us see to how far?