Queue Monitor Process (QMNn)

The QMON processes are optional background processes used by Oracle Streams Advanced Queueing (AQ), Streams and a variety of other Database products which monitor and maintain all the system and user-owned AQ persistent and buffered objects. These optional processes, like the job_queue processes, do not cause the instance to fail on process failure. They provide the mechanism for message expiration, retry, and delay, maintaining queue statistics, removing processed messages from the queue table and maintaining the dequeue IOT. They also handle...

Read More

What Is Archive Process (ARCn)

The process of turning online redo log files into archived redo log files is done by archiving process (ACRn). This process is only work if the database is running in ArchiveLog mode and automatic archiving is enabled. An archived redo log file is a copy of one of the identical filled members of an online redo log group. It includes the redo entries present in the identical member  if you are multiplexing your online redo log, and if Group 1 contains member files redo_log_01_01.log and redo_log_01_02.log,...

Read More

Recoverer Process (RECO)

The Distributed Transaction Recovery Process finds pending distributed transactions and resolves them. Pending distributed transactions are two-phase commit transactions involving multiple databases.The RECO process manager two-phase commits to track and resolve in-doubt transactions. The database that the  transaction started is normally the coordinator. It will send request to other  databases involved in two-phase commit if they are ready to commit. The RECO process of a node automatically connects to other databases involved...

Read More

Process Monitor (PMON)

Process Monitor process recovers failed process resources. PMON monitors and restarts any failed dispatcher or server processes. In RAC, PMON’s role as service registration agent is particularly important. PMON periodically checks the status of dispatcher and server processes, and restarts any that have stopped running. PMON also registers information about the instance and dispatcher processes with the network listener. Like SMON, PMON checks regularly to see whether it is needed and can...

Read More

System Monitor Process (SMON)

The system monitor process (SMON) performs recovery, if necessary, at instance startup. If the Oracle instance fails, any information in the SGA that has not been written to disk is lost. For example, the failure of the operating system causes an instance failure. After the loss of the instance, the background process SMON automatically performs instance recovery when the database is reopened. Instance recovery when the database is reopened. Instance recovery consists of the following...

Read More

What is Checkpoint (CKPT)

The checkpoint process is responsible for updating file headers in the database datafiles. A checkpoint occurs when the Oracle backgroundprocess DBWn writes all the modified database buffers in the SGA  including both committed and uncommitted data to the data files. Checkpoints are implemented for the following reasons: Checkpoints ensure that data blocks in memory that change frequently are written to data files regularly. Because of the least recently used algorithm of DBWn, a data block that changes frequently might never qualify as the...

Read More

Log Writer Process (LGWR)

Oracle database keeps record of changes made to data. Every time user performs a DML, DDL or DCL operation, its redo entries are also created. These redo entries contain commands to rebuild or redo the changes. These entries are stored in Redo Log buffer. Log writer process (LGWR) writes these redo entries to redo log files. Redo log buffer works in circular fashion. It means that it overwrites old entries. Before overwriting LGWR writes  old entries in to redo log files. Log writer process (LGWR) writes redo entries after certain amount...

Read More

Database Writer Process (DBWn)

The server process records changes to rollback and data blocks in the buffer cache. Database Writer (DBWn) writes the dirty buffers from the database buffer cache to the data files. Although one database writer process is enough for most systems, you can configure additional processes through  from DBW1 to DBW9 and from DBWa to DBWj to improve write performance if your system modifies data heavily. When a buffer in the database buffer cache is changed, it is marked as a dirty buffer. A dirty buffer is a buffer that has not been recently used...

Read More

Background Processes

An Oracle instance is the combination of the background processes and memory structures. The relationship between the physical and memory structures is maintained and enforced by Oracle’s background processes.Background processes perform functions on behalf of the invoking process. They consolidate functions that would otherwise be handled by multiple Oracle programs running for each user. The background processes perform input/output (I/O) and monitor other Oracle processes to provide increased...

Read More

Server Process

A server process is a program that directly interacts with the Oracle server. Once a user has established a connection, a server process is started to handle the user processes requests. A server process can be either a dedicated server process or a shared server process. In a dedicated server environment, the server process handles the request of a single user process. Once a user process disconnects, the server process is terminated. In ashared server environment, the server process handles the request of several user processes. The server...

Read More

User Process

User Processes When a user runs an Oracle tool Oracle creates a user process to run the user's application. A database user who needs to request information from the database must first make a connection with the Oracle server. The connection is requested using a database interface tool, such as SQL*Plus, and beginning the user process. The user process does not interact directly with the Oracle server. Rather it generates calls through the user program interface (UPI), which creates a session and starts a server process Connections and Sessions The...

Read More

Introduction to processes

All connected Oracle users must execute two modules of code to access an Oracle database instance:Application or Oracle tool  Oracle server code  These code modules are executed by processes. A process is a "thread of control" or a mechanism in an operating system that can execute a series of steps. (Some operating systems use the terms job or task. A process normally has its own private memory area in which it runs.The process structure varies for different Oracle configurations, depending on the operating system and the choice of...

Read More

Stream Pool

This is a new area in Oracle Database 10g that is used to provide buffer areas for the streams components of Oracle.  To configure the Streams pool explicitly, specify the size of the pool in bytes using the streams_pool_size initialization parameter. If the size of the Streams pool is greater than zero, then any SGA memory used by Streams is allocated from the Streams pool. If the size of the Streams Pool is zero or not specified, then the memory used by Streams is allocated from the shared pool and may use up to 10% of the shared po...

Read More

Large Pool & Java Pool

Oracle Large Pool is an optional memory component of the oracle database SGA. This area is used for providing large memory allocations in many situations that arise during the operations of an oracle database instance.Session memory for the a shared server and the Oracle XA Interface when distributed transactions are involved I/O Server Processes Parallel Query Buffers Oracle Backup and Restore Operations using RMAN  Large Pool plays an important role in Oracle Database Tuning since the allocation...

Read More

Data Dictionary Cache

An important part of an Oracle database is its data dictionary, which is a read-only set of tables that provides administrative metadata about the database. A data dictionary contains information such as the following:The definitions of every schema object in the database, including default values for columns and    integrity constraint information.  The amount of space allocated for and currently used by the schema objects The names of Oracle Database users, privileges and roles granted to users, and auditing information related...

Read More

Library Cache

The library cache stores information about the most recently used SQL and PL/SQL statements. Library cache is very important part of Oracle Sharedpool. Shared Pool controls execution of SQL statements. Shared pool is divided into Data dictionary Cache and Library Cache. In Dedicated server configuration Private SQL area is created in PGA of server process. Shared SQL areas are accessible to all users, so the library cache is contained in the shared pool with in the SGA. Shared SQL Area Shared SQL Area contains parse tree and execution plan...

Read More

Shared Pool

The shared pool is used to store the most recently executed SQL statements and the most recently used data definitions. The shared pool caches various types of program data. The shared pool stores parsing, interpreting, and executing all of the SQL statements and data dictionary information. The shared pool includes the following components:The library cache The dictionary cache Reserved Pool The total size of the shared pool is determined by the initialization parameter SHARED_POOL_SIZE....

Read More

Redo Log Buffer

Oracle database keeps record of changes made to data. Every time user performs a DML, DDL or DCL operation, its redo entries are also created. Redo entries contain the information necessary to reconstruct, or redo, changes made to the database by INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP operations. Redo entries are used for database recovery, if necessary. Oracle Database processes copy redo entries from the user memory space to the redo log buffer in the SGA. The redo entries take up continuous, sequential space in the buffer. The background...

Read More

Buffer Pools

A buffer pool is a collection of buffers. The database buffer cache is divided into one or more buffer pools. You can manually configure separate buffer pools that either keep data in the buffer cache or make the buffers available for new data immediately after using the data blocks. You can then assign specific schema objects to the appropriate buffer pool to control how blocks age out of the cache. The possible buffer pools are as follows: Default pool This pool is the location where blocks are...

Read More

Database Buffer Cache

Database Buffer cache is one of the most important components of System Global Area (SGA). Database Buffer Cache is the place where data blocks are copied from datafiles to perform SQL operations. Buffer Cache is shared memory structure and it is concurrently accessed by all server processes. The buffers in the cache are organized in two lists: the write list and the least recently used (LRU) list.The write list holds dirty buffers, which contain data that has been modified but has not yet been written to disk. The LRU list holds pinned buffers,clean,...

Read More