- USER PROCESSES
- ORACLE NET SERVICES
- LISTENER.ORA
- TNSNAMES.ORA
- SQLNET.ORA
a) LIBRARY CACHE
b) SHARED POOL
c) DICTIONARY CACHE
d) SHARED SQL AREA
e) ENQUEUES
f) LATCHES
g) RESULT CACHE
h) OTHER
i) FIXED AREA
DATABASE BUFFER CACHE
- db_recycle_cache_size
- db_keep_cache_size
LARGE POOL
JAVA POOL
a) SORT EXTENT POOL
b) STREAMS POOL
c) FLASHBACK MEMORY
ORACLE BACKGROUND PROCESSES
ARCH(Archiver)
CJQ(Job Queue Process)
CKPT(Checkpoint)
DBWR(Database Writer or Dirty Buffer Writer)
LGWR(Log Writer)
LMON(Lock Monitor)
MMON(Memory Monitor)
MMAN(Memory Manager)
PMON(Process Monitor)
SMON(System Monitor)
DMON(Dataguard Monitor)
SNP(Snapshot Process)
MRP(Managed recovery process)
RFS(Remote File Server process)
QMN(Queue Monitor Process)
DIAG(Diagnosability Daemon)
LCKx(Global Cache Service Processes)
LMON(Global Enqueue Service Monitor)
LMDx(Global Enqueue Service Daemon )
LMSx(Global Cache Service Processes)
USER PROCESSES
When a user runs an application program (such as a Pro*C program) or an Oracle tool (such as
Oracle Enterprise Manager or SQL*Plus), Oracle Database creates a user process to run the user's
application. Could be client connection or a DB HTTP server request.
ORACLE NET SERVICES
Supports network communication between a client application and a remote or local database
running on a variety of operating systems.
Oracle Net Services allows the database servers and the client applications (or servers acting as
clients) that access it to run on separate machines, and provides a means for moving data between
the nodes on a network.
Oracle Net Services is also used for Inter Process Communication if clients and servers are running
on the same machine.
LISTENER.ORA
is a SQL*Net configuration file used to configure Oracle Database Listeners (required to accept
remote connection requests).
This file normally resides in the ORACLE HOME\NETWORK\ADMIN directory or in the directory
define by the $TNS_ADMIN variable.
TNSNAMES.ORA
is a SQL*Net configuration file that defines databases addresses for establishing connections to
them. This file normally resides in the ORACLE HOME\NETWORK\ADMIN directory.
SQLNET.ORA
is a text file that provides SQL*Net with basic configuration details like tracing options, default
domain, encryption, etc. This file can be found in the ORACLE HOME\NETWORK\ADMIN directory.
SYSTEM GLOBAL AREA - System Global Area (SGA) is a group of shared memory areas that are dedicated to an Oracle “instance”. It consists of
- Shared Pool
- Database Buffer Cache
- Redo Log buffer
- Large Pool
- Java Pool
SHARED POOL
is a RAM area within the RAM heap that is created at startup time. This is
Since it is not possible to dedicate separate regions of memory for the shared pool components, the
shared pool is usually the second-largest SGA memory area (depending on the size of the
db_cache_size parameter).
All of the sub-areas in shared pool are controlled by the single shared_pool_size parameter.
The shared pool is like a buffer for SQL statements. Oracle's parsing algorithm ensures that identical
SQL statements do not have to be parsed each time they're executed. The shared pool is used to
store SQL statements.
The shared pool contains RAM memory regions that serve the following purposes:
LIBRARY CACHE
Responsible for collecting, parsing, interpreting, and executing all of the SQL statements that go
against the Oracle database. Contains the current SQL execution plan information. It also holds
stored procedures and trigger code.
SHARED SQL AREA -
The shared SQL area stores each SQL statement executed in the database. This area allows SQL
execution plans to be reused by many users.
DICTIONARY CACHE
The dictionary cache stores environmental information, which includes referential integrity, table
definitions, indexing information, and other metadata stored within Oracle's internal tables.
RESULT CACHE
is an area in the shared pool and contains the end results of a query execution.
LATCHES
Background process needs one of the data structure to satisfy its purpose, it acquires a latch while it
manipulates or looks for a shared resource.
Latches are simple types of a lock that can be very quickly acquired and freed, low-level serialization
mechanisms to protect shared data structures in the system global area (SGA). For example, latches
protect the list of users currently accessing the database and protect the data structures describing
the blocks in the buffer cache.
A server or background process acquires a latch for a very short time while manipulating or looking
at one of these structures. The implementation of latches is operating system dependent,
particularly in regard to whether and how long a process will wait for a latch.
Advantages of Latches
- It can be very quickly acquired and freed.
- There is a cleanup procedure that will be called if process dies while holding a latch.
- Synchronization of levels of latching - Process acquires a latch at a certain level and it cannot acquire another latch subsequently that is equal to or less than that level.
- Prevents more than one process from executing the same piece of code at a given time.
- Latches have an associated level that is used to prevent deadlocks.
- Low-level of serialization (one at a time)
- The implementation of latches is operating system dependent, particularly in regard to
- A server or background process acquires a latch for a very short time while manipulating or
process may die. There is no ordered queue of waiters like in enqueues. Latch waiters may
either use timers to wakeup and retry or spin (only in multiprocessors) . Since all waiters are
concurrently retrying (depending on the scheduler), anyone might get the latch and
conceivably the first one to try might be the last one to get.
ENQUEUES
Enqueues are another type of locking mechanism used in Oracle. Any object (Not only data
structures) which can be concurrently(non serially) used, can be protected with enqueues.
A good example is of locks on tables. We allow varying levels of sharing on tables e.g. two processes
can lock a table in share mode(Read) or in share update mode(Read and Write) etc.
Enqueue is obtained using an OS specific(not dependent) locking mechanism. An enqueue allows the
user to store a value in the lock, i.e the mode in which we are requesting it.
The OS lock manager keeps track of the resources locked. If a process cannot be granted the lock
because it is incompatible with the mode requested and the lock is requested with wait, the OS puts
the requesting process on a wait queue which is serviced in FIFO.
Advantages
- Allows several concurrent processes to have varying degree of known resources.
- Enqueue is obtained using an OS specific(not dependent) locking mechanism. If a process
- There is ordered queue of waiters.
Latches - Low level of serialization. Enqueue - Concurrency
Latches - Process acquires latch for a short time and there is no ordered queue of waiters. Enqueue -
Obtained using OS Specific locking mechanism, there are waiters.
FIXED AREA
contains several thousand atomic variables. These are small data structures, such as latches and pointers,
which refer to other areas of the SGA. The size of the fixed area is static. It also contains general information
about the state of the database and the instance which the background processes need to access.
OTHER
The following table lists other different areas stored in the shared pool and their purpose:
* PRIVATE SQL AREA - Private SQL areas are non-shared memory areas assigned to unique user
sessions.
* PL/SQL AREA - Used to hold parsed and compiled PL/SQL program units, allowing the execution
plans to be shared by many users.
* CONTROL STRUCTURES - Common control structure information, for example, lock information
DATABASE BUFFER CACHE
The SGA is used to store incoming data (the data buffers as defined by the db_cache_size
parameter), and internal control information that is needed by the database. The amount of
memory to be allocated to the SGA include db_cache_size, shared_pool_size and log_buffer.
When Oracle receives a request to retrieve data, it will first check the internal memory structures to
see if the data is already in the buffer.
Following are its Tasks
- Storage for data blocks that have been retrieved from data files.
- Provides optimization boost for DML operations (UPDATES)
- Managed via the LRU algorithm
keep buffer pool so that the blocks of those segments will not be inadvertently removed, thus
impacting performance.
db_recycle_cache_size - Any segments whose blocks tend to be accessed with less frequency should
be assigned to the recycle pool so that it does not flush the other segments, either in the default
cache or the keep pool.
REDO LOG BUFFER
The redo log buffer is a RAM area (defined by the initialization parameter log_buffer) that works to
save changes to data, in case something fails and Oracle has to put it back into its original state .
When Oracle SQL updates a table (a process called Data Manipulation Language, or DML), redo
images are created and stored in the redo log buffer
- Serves for assistance with database recovery tasks
- Records all changes made to database blocks
- Places changes recorded to redo entries for redo logs
LARGE POOL
This area is only used if shared server architecture, also called multi -threaded server (MTS), is used, or if parallel query is utilized. The large pool holds the user global areas when MTS is used and holds the parallel query execution message queues for parallel query
The large pool is optional memory component and it provides RAM for
- UGA holds session based information for the a shared server
- Oracle XA Interface is involved in case of distributed transactions
- I/O Server Processes
- Parallel Query Buffers
- Oracle Backup and Restore Operations using RMAN.
Caching parsed Java programs, used for java objects, Java methods and other java execution memory.
java_pool_size parameter controls the amount of memory for this area.
The JAVA Pool holds the JAVA execution code in a similar manner to the PL/SQL cache in the shared pool. The JAVA pool is used by many internal routines, such as import and export, and should be sized at approximately 60 megabytes if no other JAVA will be utilized in the user applications.
SORT EXTENT POOL
The SEP tracks extents either in the shared pool area, ie. the UGA in MTS instances and in the PGA
user's process area in non-MTS instances. Thus the SEP is always in the SGA since it tracks sort
extents but the actual sort extents themselves are in several areas.
STREAMS POOL
Cache Oracle Streams objects.
Oracle streams allows data propagation between Oracle Databases (homogeneous) and between
Oracle and non-Oracle databases (heterogeneous environment).
Oracle Streams can be used for:
- Replication
- Message Queuing
- Loading data into a Data Warehouse
- Event Notification
- Data Protection
Flashback lets you view past states of database objects or to return database objects to a previous
state without using point-in-time media recovery
The flashback memory buffer area is used with the flashback features which were first introduced in
Oracle 10g such as the ability to perform flashback transaction query(retrieves data from a past
point in time), flashback table(recovers a table to its state at a past point in time, without having to
perform a point in time recovery), flashback database(restore the whole database back to a point in
time) and flashback versions(shows you different versions of data rows, plus start and end times of a
particular transaction that created that row query), flashback drop( allows you to reverse the effects
of a drop table statement, without resorting to a point-in-time recovery).
There are a number of flashback levels
- row level - flashback query, flashback versions query, flashback transaction query
- table level -flashback table, flashback drop
- database level - flashback database
select * from v$session where type ='BACKGROUND';
Here are some of the most important Oracle background processes:
ARCH - (Optional) Archive process writes filled redo logs to the archive log location(s). In RAC, the
various ARCH processes can be utilized to ensure that copies of the archived redo logs for each
instance are available to the other instances in the RAC setup should they be needed for recovery.
CJQ - Job Queue Process (CJQ) - Used for the job scheduler. The job scheduler includes a main
program (the coordinator) and slave programs that the coordinator executes. The parameter
job_queue_processes controls how many parallel job scheduler jobs can be executed at one time.
CKPT - Checkpoint process writes checkpoint information to control files and data file headers.
CQJ0 - Job queue controller process wakes up periodically and checks the job log. If a job is due, it
spawns Jnnnn processes to handle jobs.
DBWR - Database Writer or Dirty Buffer Writer process is responsible for writing dirty buffers from
the database block cache to the database data files. Generally, DBWR only writes blocks back to the
data files on commit, or when the cache is full and space has to be made for more blocks. The
possible multiple DBWR processes in RAC must be coordinated through the locking and global cache processes to ensure efficient processing is accomplished.
FMON - The database communicates with the mapping libraries provided by storage vendors
through an external non-Oracle Database process that is spawned by a background process called
FMON. FMON is responsible for managing the mapping information. When you specify the
FILE_MAPPING initialization parameter for mapping data files to physical devices on a storage
subsystem, then the FMON process is spawned.
LGWR - Log Writer process is responsible for writing the log buffers out to the redo logs. In RAC,
each RAC instance has its own LGWR process that maintains that instance’s thread of redo logs.
LMON - Lock Manager process
MMON - The Oracle 10g background process to collect statistics for the Automatic Workload
Repository (AWR).
MMNL - This process performs frequent and lightweight manageability-related tasks, such as session history capture and metrics computation.
MMAN - is used for internal database tasks that manage the automatic shared memory. MMAN
serves as the SGA Memory Broker and coordinates the sizing of the memory components.
PMON - Process Monitor process recovers failed process resources. If MTS (also called Shared Server Architecture) is being utilized, PMON monitors and restarts any failed dispatcher or server
processes. In RAC, PMON’s role as service registration agent is particularly important.
Pnnn - (Optional) Parallel Query Slaves are started and stopped as needed to participate in parallel
query operations.
RBAL - This process coordinates rebalance activity for disk groups in an Automatic Storage
Management instance.
SMON - System Monitor process recovers after instance failure and monitors temporary segments
and extents. SMON in a non-failed instance can also perform failed instance recovery for other failed
RAC instance.
WMON - The "wakeup" monitor process
Data Guard/Streams/replication Background processes
DMON - The Data Guard Broker process.
SNP - The snapshot process.
MRP - Managed recovery process - For Data Guard, the background process that applies archived
redo log to the standby database.
ORBn - performs the actual rebalance data extent movements in an Automatic Storage Management
instance. There can be many of these at a time, called ORB0, ORB1, and so forth.
OSMB - is present in a database instance using an Automatic Storage Management disk group. It
communicates with the Automatic Storage Management instance.
RFS - Remote File Server process - In Data Guard, the remote file server process on the standby
database receives archived redo logs from the primary database.
QMN - Queue Monitor Process (QMNn) - Used to manage Oracle Streams Advanced Queuing. See
QMNC background task for 10g and beyond.
Oracle Real Application Clusters (RAC) Background Processes
The following are the additional processes spawned for supporting the multi -instance coordination:
DIAG: Diagnosability Daemon – Monitors the health of the instance and captures the data for
instance process failures.
LCKx - This process manages the global enqueue requests and the cross-instance broadcast.
Workload is automatically shared and balanced when there are multiple Global Cache Service
Processes (LMSx).
LMON - The Global Enqueue Service Monitor (LMON) monitors the entire cluster to manage the
global enqueues and the resources. LMON manages instance and process failures and the associated
recovery for the Global Cache Service (GCS) and Global Enqueue Service (GES). In particular, LMON handles the part of recovery associated with global resources. LMON-provided services are also known as cluster group services (CGS)
LMDx - The Global Enqueue Service Daemon (LMD) is the lock agent process that manages enqueue manager service requests for Global Cache Service enqueues to control access to global enqueues and resources. The LMD process also handles deadlock detection and remote enqueue requests. Remote resource requests are the requests originating from another instance.
LMSx - The Global Cache Service Processes (LMSx) are the processes that handle remote Global
Cache Service (GCS) messages. Real Application Clusters software provides for up to 10 Global Cache Service Processes. The number of LMSx varies depending on the amount of messaging traffic among nodes in the cluster.
The LMSx handles the acquisition interrupt and blocking interrupt requests from the remote
instances for Global Cache Service resources. For cross-instance consistent read requests, the LMSx
will create a consistent read version of the block and send it to the requesting instance. The LMSx
also controls the flow of messages to remote instances.
The LMSn processes handle the blocking interrupts from the remote instance for the Global Cache
Service resources by:
- Managing the resource requests and cross-instance call operations for the shared resources.
- Building a list of invalid lock elements and validating the lock elements during recovery.
- Handling the global lock deadlock detection and Monitoring for the lock conversion timeouts.
http://www.dba-oracle.com/
https://asktom.oracle.com/
http://www.toadworld.com/
http://parthokonar.wordpress.com/2009/09/01/latches-and-enqueues/
http://jonathanlewis.wordpress.com/2007/01/21/shared-sql/
http://oracledba.ezpowell.com/oracle/instanceArchitectureInternalInstanceMemory.html
http://arup.blogspot.in/2013/04/streams-pool-is-only-for-streams-think.html
http://www.datadisk.co.uk/html_docs/oracle/flashback.htm
