Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
  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_serversThis is the maximum number of Parallel Query background processes to use when performing a Parallel Query.

  • parallel_min_serversThis 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.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter