Oracle Process Architecture

PMON

- Cleans up cancelled processes - monitors other background processes - registers instance with SQL*Net Listener

Server processes can perform one or more of these

1- Parse and run SQL statements issued through the application. 2- Read necessary data blocks from datafiles on disk into the database buffers of the SGA, if the blocks are not already present in the SGA. 3- Return results in such a way that the application can process the information.

server and background processes

2 types of processes that run the Oracle database server code

Application or Oracle tool

A database user runs a database ________________, which issues SQL statements to an Oracle database

dedicated server process; shared server process

A server process can be either what type of process

Archiver Processes

ARCn

application or oracle tool; oracle database server code

All connected Oracle users must run two modules of code to access an Oracle database instance. These code modules are run by processes

30

An Oracle instance can have up to _____ ARCn processes (ARC0 to ARC9).

diminishes

As buffers are dirtied by user processes, the number of free buffer __________

Checkpoint Process

CKPT

checkpoint abbreviation

CKPT

Database Writer Process

DBWn

Oracle instance

Each process in an _________ performs a specific job

Oracle database server code

Each user has some______________ executing on his or her behalf, which interprets and processes the application's SQL statements

Manageability Monitor

Examples of? -Issuing alerts whenever a given metrics violates its threshold value. -Taking snapshots by spawning additional process (slaves). -Capturing statistics value for SQL objects which have been recently modified.

Every three seconds

How often does the LGWR write the redo log buffer to a redo log file on disk?

select count(*) from V$PROCESS;

How to check the currently active processes?

show parameter processes;

How to see the current number of processes which is set in the database?

recovers them when the tablespace or file is brought back online

If any terminated transactions were skipped during instance recovery because of file-read or offline errors, SMON _________. SMON checks regularly to see whether it is needed.

not able to find free buffers

If the number of free buffers drops too low, user processes that must read blocks from disk into the cache are ______________

Log Writer Process

LGWR

Memory Manager

MMAN

Manageability Monitor

MMON

background processes

Oracle uses _____________ to maximize performance and accommodate many users. An Oracle instance can have many of these; not all are always present.

CKPT background process

Oracle uses _________________ to automatically update each of the control files as needed, keeping the contents of all copies of the control synchronized

Process Monitor Process

PMON

Recovery if a user process fails; Cleaning up the database buffer cache; Registering the instance information with the listener

PMON is responsible for:

Queue Monitor Processes

QMNn

Queue Monitor Processes

QMPn

System Monitor Process

SMON

Increase the number of processes

Solution of error: ORA-00020: maximum number of processes (600) exceeded.

the current number of ARCn processes is insufficient to handle the workload

The LGWR process starts a new ARCn process whenever _____________

LGWR starts a new ARCn process

The alert log keeps a record of when ____________.

user processes; oracle processes

The processes in an Oracle system can be categorized into two major groups

Processes

This parameter specifies the maximum number of operating system user processes that can simultaneously connect to Oracle

buffer

When a _____ in the database buffer cache is modified, it is marked dirty

user process

When a user runs an application or an Oracle tool, Oracle creates a ______________ to run the user's application.

10

You can configure up to _____ queue monitor processes.

Server processes

_________ handle the requests of user processes connected to the instance

Connection; session

__________ and __________ are closely related to user process but are very different in meaning.

LOG_ARCHIVE_MAX_PROCESSES

_____________ parameter specifies the maximum number of ARCn processes that can be created.

User processes

_____________ run the application or Oracle tool code

Oracle processes

______________ run the Oracle database server code. They include server processes and background processes

cold buffer

a buffer that has not been recently used according to the Least Recently Used (LRU) algorithm

queue monitor process

an optional background process for Oracle Streams Advanced Queuing, which monitors the message queues. These processes are different from other Oracle background processes, if that process fails does not cause the instance failure.

LGWR

background process writes to the current redo log group whenever one of the following database events occurs: - Every three seconds. - A user commits a transaction. - The Redo Log Buffer is one-third full. - Before the DBWn process whenever a database checkpoint occurs.

alter system set processes=700 scope=spfile;

command to Increase the number of processes from 600 to 700

connection

communication pathway between a user process and an Oracle instance

ARCn

copies redo log files to a designated storage device after a log switch has occurred

CKPT process

does not write blocks to disk; DBWn always performs that work

checkpoint information

information that includes the checkpoint position, System Change Number (SCN), and location in online redo log to begin recovery

DB_WRITER_PROCESSES

initialization parameter that specifies the number of DBWn processes

background processes

manage memory structures: -Perform I/O to write data to a file on a disk. -Perform general maintenance tasks.

DBWn

manages the buffer cache so that user processes can always find free buffers

MMAN

manages the dynamic resizing of SGA memory areas as the workload increases or decreases

36

maximum number of DBWn processes

process

mechanism in an operating system that can run a series of steps

process

normally has its own private memory area in which it runs

LGWR

normally writes fast enough to ensure that space is always available in the buffer for new entries.

database buffer cache

part of the Oracle SGA that provides the following tasks: -Storage for data blocks that have been retrieved from data files. -Provides optimization boost for DML operations (UPDATES). -Data managed via the LRU algorithm.

SMON

performs recovery, if necessary, at instance startup

Manageability Monitor (MMON)

performs various manageability-related background tasks

Server processes

process created on behalf of each user's application

shared server process

process that can service multiple user processes

dedicated server process

process that services only one user process

ARCn

processes are present only when the database is in ARCHIVELOG mode, and automatic archiving is enabled

Redo log files

record all changes made to the database

SMON

responsible for cleaning up temporary segments that are no longer in use and for coalescing contiguous free extents within dictionary managed tablespaces

Log Writer Process (LGWR)

responsible for redo log buffer management

checkpoint process

responsible for updating file headers in the database datafiles and control files

session

specific connection of a user to an Oracle instance through a user process

server and background processes

two types of processes that run the Oracle database server code

CKPT process

updates the control file and data file headers with checkpoint information and signals DBW to write blocks to disk

MMAN

used for internal database tasks

V$BGPROCESS

view contains information about the background processes

LGWR

writes a commit record, when a user process commits a transaction

LGWR

writes all redo entries that have been copied into the buffer since the last time it wrote The redo log buffer is a circular buffer. When LGWR writes redo entries from the redo log buffer to a redo log file, server processes can then copy new entries over the entries in the redo log buffer that have been written to disk.

DBWn process

writes cold, dirty buffers to disk so that user processes are able to find clean buffers that can be used to read new blocks into the cache, so this improves the performance

LGWR

writes one contiguous portion of the buffer to disk.

database writer process (DBWn)

writes the contents of buffers to datafiles

LGWR

writes the redo log buffer to a redo log file on disk

checkpoint

writing by the DBW (database writer) process of all modified buffers in the SGA buffer cache to the database data files