A High Level Comparison Between Oracle and SQL Server – Part II
Database Instance Architecture
When an Oracle instance is created, it is nothing but a collection of some structured areas in the server’s memory and some background processes spawned to interact with it. Oracle literature quite extensively describes these memory structures and processes.
The area occupied in the memory by a running Oracle instance is called the System Global Area (SGA). The size of the SGA can be controlled by modifying an Oracle initialisation parameter. Within the SGA, there are at least three distinct areas that are also created. These are:
Database Buffer Cache: This is where blocks of data are kept. Just like in SQL Server, users do not directly access data from the database files: when data is read, the relevant blocks are copied from the data files into this memory area. When data is changed, it is updated here in the memory. The changed contents of the DB buffer cache are written to the data files by a separate process.
Redo Log Buffer: This area in the SGA continuously records the changes made to the data blocks in the database buffer cache. Contents of the redo log buffer are written to the online redo logs.
Shared Pool: Within the SGA a chunk of memory is reserved for the Shared Pool. Shared pool is the equivalent of SQL Server’s procedure cache. It is primarily used for caching the most recently issued SQL statements against the database. The components that make up shared pool consist of:
Dictionary Cache: the dictionary cache stores information recently fetched from the data dictionary by Oracle.
Library Cache: This area contains the recently executed SQL and PL/SQL statements and their execution plans. The SQL area can be further divided into the shared and the private areas: shared SQL area keeps statements that can be reused for multiple users and the private area keeps the bind variables unique to each individual connection. PL/SQL is Oracle’s own proprietary language that is an enhancement over standard SQL. When PL/SQL program units like procedures and functions are executing, their code is copied to the shared PL/SQL area in the library cache. Apart from the codes and their execution plans, the library cache also contains locks, latches and character set information.
A number of optional areas can also exist in the SGA depending on what other components Oracle is running. The Java Pool is used when Oracle uses Java application code. Oracle’s built-in backup and recovery system RMAN (short for Recovery Manager) uses the Large Pool. When Oracle’s advanced queuing mechanism is used, the Streams Pool is used.
All these components in the memory are parts of the SGA and they interact with the database through a number of processes. We begin our tour of Oracle processes with an introduction of user and server processes.
When a user or an application connects to an Oracle database, a user process is started. In a two-tiered architecture, the user process resides in the client workstation; in a three-tiered application, the middle tier spawns the user process. Once the user process connects to an Oracle listener service running in the database server, the listener starts a server process for that user session. Just to make things clear, an Oracle listener is the network component of Oracle that handles oncoming database connections. Listener itself is a process and watches for connection requests from clients. If the listener is not running, the database cannot be connected to. Once the connection is made, the server process spawned by the listener handles the user requests against the database. In the most common scenario, each user’s connection spawns a separate, dedicated server process. Oracle can also be configured to run in a mode where a number of server processes are pre-created in a pool. User processes connected to the database instance are allocated server processes from this pool.
There can be a number of background processes running in an Oracle instance at any one time. Of these, there are five that are always executing.
The Database Writer process or DBWn is responsible for writing the modified data blocks from the database buffer cache into the data files. Oracle can run up to 20 instances of this process at any one time to improve performance. The n in DBWn represents an individual process: n can be anywhere between 0 to 9 and a to j.
While the dirty data blocks are written to the data files by one or more DBWn processes, the Log Writer (LGWR) process writes the contents of the redo log buffer into the online redo logs. LGWR executes far more frequently than the database writer process because of two reasons: the first is to ensure transaction information is hardened to disk as quickly as possible so that the database can be recovered later if an unexpected crash happens. The second reason is that redo information in the log buffer is not the actual changed data, but the change that happened to the data – and thus has a much smaller footprint in terms of size.
The Checkpoint process (CKPT) is responsible for periodically synchronising the contents of the SGA and the database. When a checkpoint runs, it invokes the DBWn process to write the dirty data blocks to the data files, flushes the contents of the redo log buffer to the online redo logs and then updates the headers of the data files and the database control file.
Sometimes, if a user session against the database crashes or terminate unexpectedly, the Process Monitor (PMON) process will take care of any loose ends remaining at the back-end side. This will include releasing any locks held by the terminated user session, reclaiming any resources allocated and ensuring the process ID is removed from the list of active processes. PMON is like an UNIX daemon: it wakes up periodically to see if any house-cleaning needs to be done. It can initiate other processes if necessary and other background processes can invoke it as well.
The System Monitor or SMON is invoked by Oracle automatically when an instance starts after a server crash. SMON performs crash recovery using the online redo log files.
One of the optional processes that may be running in an Oracle instance is the Archiver (ARCn) process. Once again the “n” represents an individual instance of the process. ARCn runs when the database is running in the archive log mode. The archiver backs up the contents of a full redo log into a disk file known as the archive log before Oracle overwrites it. That way, the contents of the online redo logs are not lost.
The following diagram shows a very simplified view of the Oracle instance architecture.
Finally, you want to see the components of the System Global Area by running the following query:
To see the total amount of memory allocated to the Oracle instance, run the following query:
To see what Oracle processes are running in the instance, you can run a query like the following:
Microsoft SQL Server also maintains internal memory structures and runs background processes when an instance is up and running. Unlike Oracle though, the inner workings and architecture is not widely published in SQL Server literature.