| |
 |
|
Oracle Background Processes
Oracle Tips by Burleson |
In addition to the SGA region in RAM memory, an
Oracle instance also consists of numerous background processes. It is
important to remember that an Oracle database is really a very large
program running on the server. When the Oracle program needs to
perform a specific task, it will spawn a factotum (slave) task to
service the task. Table 9-1 provides a listing of the Oracle
background processes.
|
Process |
Process Name |
Description |
|
Advanced queuing |
Aq_tnxx |
These are the Oracle8 advanced queuing
processes that are used to thread processes through the Oracle8
instance. |
|
Archive Monitor |
ARCHMON |
This is a process on UNIX that monitors the
archive process and writes the redo logs to the archives. |
|
Archiver Process |
ARCH |
This process is only active if archive
logging is in effect. This process writes the redo log data files
that are filled into the archive log data files. |
|
Callout queues |
EXTPROC |
There will be one callout queue for each
session performing callouts. It was hoped that Oracle8 would
multithread these processes, but this feature remains “in the
works” for multithreaded environments. As of Oracle8.0.2 beta,
callout queues were not working with environments where a
multithreaded server was enabled. |
|
Checkpoint processes |
CKPxx |
These are the checkpoint processes that can
be started to optimize the checkpoint operation for Oracle
logging. |
|
Database Writer |
DBWR |
This process handles data transfer from the
buffers in the SGA to the database files. |
|
Dispatchers |
Dnnn |
This process allows multiple processes to
share a finite number of Oracle servers. It queues and routes
process requests to the next available server. |
|
Distributed Recoverer |
RICO |
This is an Oracle process that resolves
failures involving distributed transactions. |
|
Listener (SQL*Net v1) |
ORASRV |
If you are running SQL*Net version 1, this
process will be running to service TWO_TASK requests. This
parameter was obsoleted in Oracle8. |
|
Listener (Net8) |
TNSLSNR |
If you are running TCP/IP, this process,
known as the TNS listener process, will be running. |
|
Lock Processes |
LCKn |
This process is used for interinstance
locking in an Oracle Parallel Server environment. |
|
Log Writer |
LGWR |
This process transfers data from the redo log
buffers to the redo log database files. |
|
Parallel Query |
Pnnn |
These background processes are started when
Oracle performs a full table scan on a table that is defined as
PARALLEL. There will be one background process for each Parallel
Query slave, as defined by DBA_TABLES.DEGREE |
|
Process Monitor |
PMON |
This process recovers user processes that
have failed and cleans up the cache. This process also recovers
the resources from a failed process. |
|
Servers |
Snnn |
This process makes all the required calls to
the database to resolve user requests. It returns results to the Dnnn
process that calls it. |
|
Snapshot queues |
Snpxx |
These are snapshot process queues. |
|
System Monitor |
SMON |
This process
performs instance recovery on instance startup and is responsible
for cleaning up temporary segments. In a parallel environment,
this process recovers failed nodes. |
Table 2: The Oracle background processes
Most of the Oracle background processes are
fully automated and cannot be adjusted. However, there are several
Oracle parameters that control the background processes.
Important Oracle Parameters for Background Processes
It is important to understand that the settings
for Oracle parameters directly affect the behavior of the Oracle
background processes. Here is a list of some of the most important
Oracle parameters that affect background process behavior.
-
parallel_max_servers This is the maximum number of Parallel
Query background processes to use when performing a Parallel Query.
-
parallel_min_servers This is the minimum number of Parallel
Query processes (Pnnn) to be dedicated to a Parallel Query.
-
db_file_multiblock_read_count This parameter controls the
asynchronous read-ahead feature for Oracle for fast full scans on
indexes. In a fast full scan, Oracle reads an entire B-tree index,
normally to avoid a sort in the TEMP tablespace.
Now that we have a general understanding of the
components that comprise an Oracle instance, let's look deeper into
the Oracle data buffer cache. The data buffer cache is one of the most
important areas of Oracle instance tuning since it has a direct impact
on the amount of disk I/O.
Blocksize and Oracle Disk I/O
I/O is the single most important slowdown in an
Oracle database, and the more data we can get in a single I/O, the
better the overall performance of Oracle. The cost of reading a 2K
block is not significantly higher than the cost of reading an 8K
block, and the db_block_size Oracle parameter defines the block
size for the whole database. However, experiments have shown that
almost every Oracle database will run faster with large block sizes
and even OLTP databases will run faster with the largest supported
block size.
Most neophytes to Oracle will create small
block sizes because they see that their average row length is very
small, and it seems wasteful to read tiny rows into a huge data block.
The problem with this reasoning relates to Oracle's use of indexes.
Even in online transaction processing systems where small rows are
retrieved, we must remember that Oracle indexes are almost always
accessed. The real benefit to large block sizes relates to the Oracle
indexes, and not to the row length. As a general rule, the minimum
acceptable db_block_size is 8K. Oracle data warehouses and
systems that perform many full table scans will often benefit from 16K
block sizes.
NOTE: When changing a block size from 4K to 8K, the
DBA must remember that the size of the SGA will increase because each
block will be twice as large. The maximum block size depends on the
platform, so you should always consult the operating system specific
documentation to find the highest allowed block size for your
platform.
When increasing the block size, it is necessary
to export the entire database and then redefine the database with the
larger block value. This export-import is a time-consuming operation,
but it can often result in huge improvements in speed.
This is an excerpt from "Oracle
High Performance tuning with STATSPACK" by Oracle Press.
 |
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning
tips & scripts.
You can buy it direct from the publisher for 30%-off and get instant access to
the code depot of Oracle tuning scripts. |
|
