Oracle Processes


Oracle creates processes on the server to manage database connected user processes. A dedicated server process services a single user process and a shared server process processes multiple user processes, ie. the MTS or multi-threaded server. The multi-threaded server effectively eliminates the requirement for each connection requiring a decicated server process. MTS is appropriate for sharing of database resources between users in an OLTP environment such as the internet. Some processes are required and other process are optional. Note that dedicated (non-MTS) database connections are required for batch jobs, Enterprise Manager, database startup and shutdown, media recovery and use of the recovery manager.

Server Monitor (SMON)

The server monitor process monitors the Oracle instance. The SMON process recovers temporary segment space when no longer required and colesces contiguous areas of free space in database tablespaces when the default storage parameter PCTINCREASE is not set to zero. The SMON process recovers dead transactions skipped during instance recovery because of file-read or offline errors. Transactions are eventually recovered by SMON when the tablespace or file is brought back online. If SMON dies the instance will crash requiring instance recovery on restart. The SMON process executes peridiocally and performs it's tasks as needed. An example of the temporary segment space recovery of the SMON process is as follows. The Oracle Server creates temporary segments when sorting cannot be done in memory. When the temporary segment is created in a PERMANENT tabblespace the SMON process reclaims the space after the statement is completed.

Process Monitor (PMON)

The process monitor cleans up failed transactions. The cleanup process is as follows.

When an instance crashes and is restarted PMON will roll back any uncommitted transactions using rollback segment information. The PMON process executes peridiocally and performs is taks as needed.

Database Writer (DBWR / DBWn)

The DBWR (database writer) manages the contents of the database blocck buffer cache and the dictionary cache. The DBWR generally writes only when more data is required to be read into the SGA since there are not enough free database buffers. Only least recently used data blocks are written to datafiles first. The primary purpose of the DBWR is the management of the database buffer cache such that the server processes can always find free buffers.

The DBWR is the database dirty write buffer. This process writes modified (dirty) blocks from the database buffer cache to disk files. The DBWR writes changed blocks from the database buffer cache to the disk. A rollback will clear the rollback segments. A commit will cause the DBWR to write changed blocks from the database buffer cache to disk.

The DBWR process is controlled by the following parameters.

In Oracle8 multiple DBWR processes can be executed numbered DBW0 to DBW9. The parameter DB_WRITER_PROCESSES controls the number of database block writer processes. Multiple DBWR processes can be executed at once when executing parallel queries. Multiple DBWR processes can help to minimize contention within the DBWR with large queries spanning large tables. The number of DBWR I/O slaves running at once is controlled by the DBWR_IO_SLAVES parameter.

Log Writer (LGWR / LGWn)

LGWR is the log writer. This process writes out the redo log buffer to the redo logs on a commit statement. The LGWR process also allows for the recovery of committed data in the database buffer cache at the point of an instance failure. Log files can be mirrored and thus the LGWR will writes to all copies. Note that the log files contain sequentially, all actions on the database. Thus the redo logs can be used for database reconstrcution from a specific point in time. Performance of the LGWR can be improved by using multiple LGWR I/O slaves, set using the LGWR_IO_SLAVES parameter, thus improving the performance of writing to the redo log files by executing writes to different log files in parallel.

Checkpoint Process (CKPT)

At specific points in time all modified database buffers in the SGA are written to datafiles by the DBWR. This event is called a checkpoint. If the CKPT process (optional in Oracle 7.3 and mandatory in Oracle 8) is present then the checkpoint responsibilities of the LGWR process are assumed by the CKPT process. The checkpoint process is controlled by the CHECKPOINT_PROCESS parameter. In Oracle7 the checkpoint process stopped data being written from the redo log buffer to the redo logs. Oracle8 requires the CKPT process as mandatory to relieve the LGWR process of managing CKPT responsibilities when CKPT was switched off in Oracle7.

Archive Writer (ARCH)

This is the archiver process. The archiver process copies filled redo log files to the archive log destination. If the archive log destination is filled then the archive log process will hang silently. Problems will be reported to the alert log. The parameters below affect the archive process.

Heavy transaction periods can lead to heavy I/O contention due to filling of the redo logs too fast and thus frequent copying of the redo logs to the archive location. Note that this can be partially resolved by increasing the size of the redo log files and thus leaving a larger period of time between archiving. Multiple ARCH I/O slaves can be created ti improve performance. Use the ARCH_IO_SLAVES parameter to set the number of archiver ARCH processes.

Distributed Database Recovery (RECO)

The recover process recovers failed distributed transactions and is only present if the distributed database option is installed in the database. The RECO process is started if the DISTRIBUTED_TRANSACTIONS initialisation parameter is set to a value greater than zero.

Snapshot Processes (SNPn)

This is the snapshot process. The SNPnn process checks and executes jobs for the job queue.

Lock Processes (LCKn)

Multiple LCKn processes are named LCK0 through to LCK9. LCKn processes are use for interinstance locking when using the Oracle parallel server option.

Dispatcher Processes (Dnnn)

Dispatcher processes are used for the multi-threaded server (MTS). In an MTS environment client user processes are connected to the database via a dispathcer process. The dispatcher will support multiple concurrent client connections. The dispatcher process manages a section of memory shared by all client connections called virutal circuits as a way of handling database access. Note that Net8 in Oracle8 (not SQL*Net) is required to support connections to the database utilising MTS. The dispatcher process (Dnnnn) distributes attachments to the Snnnn (server) process. The Dnnnn and Snnnn processes are only present in an MTS-enabled instance. In general consider about 1 dispatcher process for each 1000 concurrent user connections.

Dispatcher processes can be configured to listen on different IP Addresses as shown below. The first example causes two dispatchers to listen on a single IP Address and the second example sets three dispatchers to listen on separate ports on the same IP Address.

MTS_DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)\(HOST=100.20.1.10))(DISPATCHERS=2)"
MTS_DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)\(HOST=100.20.1.10))(PORT=1001))"
MTS_DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)\(HOST=100.20.1.10))(PORT=1002))"
MTS_DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)\(HOST=100.20.1.10))(PORT=1002))"

Server Processes (Snnn)

Server processes are created to manage connections to the database where a dedicated server is required.

Queue Manager (QMNn)

The queue manager process is used with Oracle advanced queuing. The AQ_TM_PROCESSES parameter will enable the queue manager process. Set this parameter to 0 (off) or 1 (on). This process monitors the advanced queuing message queues.

Pnnn Processes

These are the parallel query slave process and are started and stopped by the parallel query server. A single parallel query can result in many Pnnn processes dependant on table size and the number of processors and disks. Loss of a Pnnn process may result in a failed query but should otherwise not affect instance operations. Oracle creates a series of query processes for execution of parallel queries. Database access volume is changed automatically by changes in the number of query server processes in the pool. Processes are terminated when the idel time is exceeded.

Managing Calling of External Processes

Oracle allows for the calling of functions from shared library functions stored in DLLs written for instance in C. External procedures require changes to the listener.ora and the listener.ora files. Oracle requires a separate listener process in order to handle external procedures. External implies external to the logical Oracle database. External procedure executables should be placed into the Oracle home bin directory, commonly the <drive>\Oracle\Ora81\bin directory.

Managing Sessions

Sessions are terminated using the ALTER SYSTEM KILL SESSION command. Sessions are identified by a SID number and a serial number, ie. ALTER SYSTEM KILL SESSION '2,12';. Note that an active session is killed after network I/O and transaction rollback has been completed. Inactive sessions are terminated immediately.

Multi-Threaded Server Configuration

The Oracle MTS environment is intended to allow for the sharing of database connections by multiple users and is intended for database access from the internet. MTS allows the sharing of a few server processes by many user processes. Note that this connection pooling will only function with Net8. In general MTS uses one or more dispatcher and shared server processes. When a user makes a call that user's dispatcher process places that request into the request queue. The next available server process fetches the request from the request queue. When complete the response is placed back into the calling dispatcher process's response queue.