Chapter 4 – Managing Oracle Database Processes

–          Oracle database creates server processes to handle the requests of user processes connected to an instance.

–          Your database is always enabled to allow dedicated server processes, but you must specifically configure and enable shared server by setting one or more initialization parameters

–          Shared server can be configured for session multiplexing, which combines multiple sessions for transmission over a single network connection in order to conserve the operating system’s resources.

–          DBW – The database writer writes modified blocks from the database buffer cache to the datafiles. Oracle database allows a maximum of 20 database writer processes. The DB_WRITER_PROCESSES initialization parameter specifies the number of DBW processes.

–          LGWR – the log writer process writes redo log entries to disk. Redo log entries are generated in the redo log buffer of the SGA. LGWR writes the redo log entries sequentially into a redo log file. If the database has a multiplexed redo log, then LGWR writes the redo log entries to a group of redo log files.

–          CKPT – At specific times, all modified database buffers in the system global area are written to the datafiles by DBW. This event is called a checkpoint. The checkpoint process is responsible for signaling DBW at checkpoints and updating all the datafiles and control files of the database to indicate the most recent checkpoint.

–          SMON – the system monitor performs recovery when a failed instance starts up again. SMON also cleans up temporary segments that are no longer in use and recovers dead transactions skipped during system failure and instance recovery because of file-read or offline erros.

–          PMON – the process monitor performs process recovery when a user process fails. PMON is responsible for cleaning up the cache and freeing resources that the process was using.

–          RECO – the recoverer process is used to resolve distributed transactions that are pending because of a network or system failure in a distributed database.

The following views help you to monitor the Oracle instance

V$PROCESS – Contains information about the currently active processes

V$LOCKED_OBJECT – Lists all locks acquired by every transaction on the system

V$SESSION – lists session information for each current session

V$SESS_IO – contains I/O statistics for each user session

V$SESSION_WAIT – lists the resources or events for which active sessions are waiting

V$SYSSTAT – Contains session statistics

V$SQLAREA – Contains statistics about shared SQL area and contains one row for each SQL string