Oracle Database Processes Considerations

SMON, the System Monitor
SMON initially has the task of mounting and opening a database. SMON mounts a database by locating and validating the database controlfile. It then opens a database by locating and validating all the datafiles and online log files. Once the database is opened and in use, SMON is responsible for various housekeeping tasks, such as collating free space in datafiles.

PMON, the Process Monitor

PMON monitors all the server processes and detects any problems with the sessions. If a session has terminated abnormally, PMON will destroy the server process, return its PGA memory to the operating system’s free memory pool, and roll back any incomplete transaction that may have been in progress.

DBWn, the Database Writer

How many database writers do you need? The default number may well be correct. Adding more may help performance, but usually you should look at tuning memory first. As a rule, before you optimize disk I/O, ask why there is any need for disk I/O.

What will cause DBWR to write? No free buffers, too many dirty buffers, a three-second timeout, or a checkpoint.

What does DBWn do when a transaction is committed? It does absolutely nothing.



LGWR, the Log Writer
LGWR writes the contents of the log buffer to the online log files on disk. A write of the log buffer to the online redo log files is often referred to as flushing the log buffer.

Circumstances that will cause LGWR to flush the log buffer: if a session issues a COMMIT; if the log buffer is one-third full; if DBWn is about to write dirty buffers.

It is in fact possible to prevent the LGWR write-on-commit. If this is done, sessions will not have to wait for LGWR when they commit: they issue the command and then carry on working. This will improve performance but can also mean that work can be lost.

CKPT, the Checkpoint Process

When do full checkpoints occur? Only on request, or as part of an orderly database shutdown.

The current checkpoint position, also known as the RBA (the redo byte address), is the point in the redo stream at which recovery must begin in the event of an instance crash. CKPT continually updates the controlfile with the current checkpoint position.

The faster the incremental checkpoint advances, the quicker recovery will be after a failure. But performance will deteriorate due to the extra disk I/O, as DBWn has write out dirty buffers more quickly.

MMON, the Manageability Monitor

By default, MMON gathers a snapshot and launches the ADDM every hour.


MMNL, the Manageability Monitor Light
MMNL is a process that assists the MMON.


MMAN, the Memory Manager
MMAN is a process that was introduced with database release 10g. It enables the automatic management of memory allocations.



ARCn, the Archiver

LGWR writes the online log files; ARCn reads them. In normal running, no other processes touch them at all.


The progress of the ARCn processes and the state of the destination(s) to which they are writing must be monitored. If archiving fails, the database will eventually hang. This monitoring can be done through the alert system.



RECO, the Recoverer Process
A distributed transaction is a transaction that involves updates to two or more databases.

RECO, the Recoverer Process

Distributed transactions require a two-phase commit. A two-phase commit prepares each database by instructing their LGWRs to flush the log buffer to disk (the first phase), and once this is confirmed, the transaction is flagged as committed everywhere (the second phase). If anything goes wrong anywhere between the two phases, RECO takes action to cancel the commit and roll back the work in all databases.



SQL> select program from v$session order by program;

PROGRAM
------------------------------------------------
oracle@solaris10 (ARC0)
oracle@solaris10 (ARC1)
oracle@solaris10 (ARC2)
oracle@solaris10 (ARC3)
oracle@solaris10 (ASMB)
oracle@solaris10 (CJQ0)
oracle@solaris10 (CKPT)
oracle@solaris10 (DBRM)
oracle@solaris10 (DBW0)
oracle@solaris10 (DIA0)
oracle@solaris10 (DIAG)

PROGRAM
------------------------------------------------
oracle@solaris10 (GEN0)
oracle@solaris10 (LGWR)
oracle@solaris10 (MARK)
oracle@solaris10 (MMAN)
oracle@solaris10 (MMNL)
oracle@solaris10 (MMON)
oracle@solaris10 (O000)
oracle@solaris10 (PMON)
oracle@solaris10 (PSP0)
oracle@solaris10 (Q000)
oracle@solaris10 (Q001)

PROGRAM
------------------------------------------------
oracle@solaris10 (QMNC)
oracle@solaris10 (RBAL)
oracle@solaris10 (RECO)
oracle@solaris10 (SMCO)
oracle@solaris10 (SMON)
oracle@solaris10 (VKTM)
oracle@solaris10 (W000)
sqlplus@solaris10 (TNS V1-V3)

30 rows selected.

SQL> select program from v$process order by program;

PROGRAM
------------------------------------------------
PSEUDO
oracle@solaris10 (ARC0)
oracle@solaris10 (ARC1)
oracle@solaris10 (ARC2)
oracle@solaris10 (ARC3)
oracle@solaris10 (ASMB)
oracle@solaris10 (CJQ0)
oracle@solaris10 (CKPT)
oracle@solaris10 (D000)
oracle@solaris10 (DBRM)
oracle@solaris10 (DBW0)

PROGRAM
------------------------------------------------
oracle@solaris10 (DIA0)
oracle@solaris10 (DIAG)
oracle@solaris10 (GEN0)
oracle@solaris10 (LGWR)
oracle@solaris10 (MARK)
oracle@solaris10 (MMAN)
oracle@solaris10 (MMNL)
oracle@solaris10 (MMON)
oracle@solaris10 (O000)
oracle@solaris10 (PMON)
oracle@solaris10 (PSP0)

PROGRAM
------------------------------------------------
oracle@solaris10 (Q000)
oracle@solaris10 (Q001)
oracle@solaris10 (QMNC)
oracle@solaris10 (RBAL)
oracle@solaris10 (RECO)
oracle@solaris10 (S000)
oracle@solaris10 (SMCO)
oracle@solaris10 (SMON)
oracle@solaris10 (TNS V1-V3)
oracle@solaris10 (VKTM)
oracle@solaris10 (W000)

33 rows selected.

SQL>