Oracle Architecture Basics
Posted by decipherinfosys on February 9, 2009
In response to one of our previous post, a reader asked us to explain the basic Oracle architecture. There are several books devoted to Oracle concepts and the online manuals are also a very good source to get your concepts correct. The best book that we will recommend are the ones written by the Oracle guru Tom Kyte on Database Architecture. Infact, you should get all the books written by him and try to read them through and through.
In this post, we will try to cover the very basic architecture points. The two big concepts to understand first are Instance and Database. A database is a set of files (data files, redo log files, control files and so on). An instance is a set of processes (SMON, PMON, MMON, DBWR, etc) and a shared memory segment (SGA). A database may be mounted and opened by many instances (RAC installation) concurrently (this is to do with the shared everything architecture of Oracle RAC). An instance may mount and open any database — however it may only open a single database at any time. So, in ERD terms: Database:Instance :: 1:N (a one to many relationship) in Oracle.
So, an Instance is just a collection of:
a) Processes such as SMON (System Monitor), PMON (Process Monitor), DBWn (DB Writer), LGWR (Log Writer) etc., and
b) A couple of memory areas like SGA (System Global Area), PGA (Program Global Area).
SGA comprises of the buffer cache, shared pool, the log buffer, the large pool, the Java Pool and the Oracle Streams Pool (10gR2 onwards). The buffer cache is essentially used to store the blocks of the table(s) that are retrieved by the engine from the database files. So, essentially when a call is made by an application, the data is read from the disk (assuming it is not already in the memory) and is placed in the buffer cache. And then from the buffer cache, it is displayed through the application to the end user. If another user session then requests the same data, the data is rendered from the buffer cache rather than going to the disk again. Now, when a session then writes (updates, deletes) the data – both the pre and the post changes of the data are written to the log buffer. When the session commits, at that time the contents of the log buffer are written to the disk into the online redo log files. These contents of the log buffer are written to the disk by the log writer (LGWR) and it can happen depending upon a couple of events – a checkpoint, a commit by the session, log buffer being 1/3rd full or 1MB full or a time lapse of 3 seconds between the previous flush of the log buffer to the redo files. The changed blocks in the buffer cache are written to the database files via the DBWR (Database Writer). There can be more than 1 database writer process. This event of flushing the data from the cache to the files is called the checkpoint.
Now, the other memory area that we had mentioned above was the Shared Pool. This has the library cache as well as the row cache. Row cache holds the information on the data dictionary objects where as the library cache holds SQL queries as well as the parsed object definitions.
So, by now we have briefly talked about the memory areas. The other big concept is about processes – we have the Oracle Background processes, the user processes and their counterpart – the server processes. We had briefly discussed 2 Oracle background processes above – DBWn and LGWR. Besides these 2 background processes, the other background processes like PMON monitors all the processes and for failed processes, it does the roll back of the transactions, the SMON performs process cleanups, MMON takes snapshots of the database health and stores that information in the AWR (Automatic Workload Repository), the ARCn or the archiver process writes the online redo log files to the archived redo log files (only if you have configured the database in an archivelog mode). There are other background processes as well like the Job Queue processes, Checkpoint process (CKPT), etc. that you can read more on from the link below.
Now, when a connection is made by the application, a user process is created to run the user’s application. A connection is just a pathway between the user process and an Oracle database instance and is done via the available interprocess communication mechanisms (same machine for the user process and the Oracle database instance) or network software (separate machines for the user process and the Oracle database instance). A session is a specific connection of an user to the instance through a user process. Oracle database creates a server process to handle the requests from the user process. For a given connection, you can have 0, 1 or more sessions. It is very simple to see that in action as well – connect through SQL *PLUS and do: “Set autotrace on statistics”. Doing that, you will see that you have a connection, 1 single process and 2 sessions since autotrace will use another session so that it can query the stats of your current session without having any effect on the statistics for that session. So, a Connection to Session to Process is typically 1 to 1 however, as mentioned above in with the SQL *PLUS example, it can very well be different as well.
A process is used by that session from the connection to execute the statements and this process can be a dedicated one (i.e. dedicated to a connection/session) or a Shared one (i.e. when using a shared server, the session can grab a process from a pool of processes and execute the statement and return it back to the pool when done). Discussing dedicated server vs shared server is a pretty big topic. Look it up in the URL given below.
This is a very high level overview of the structure. We would recommend that you read Tom’s books or the concepts guide in the Oracle reference: