Oracle Database 11g – Common Background Processes

To understand
Oracle background processes is to understand how Oracle works–offering
opportunity to tune effectively.

With every version comes the potential for additional
background processes when an Oracle instance is started. If memory serves me
right, back in Oracle 7 there were only 6-10 background processes. Taking a
look at a current Oracle 11g instance there are easily 23 background processes
for JUST a typical installation.

Granted this is for a UNIX environment and not Windows where
a simpler math could be employed. So what are background processes anyway?
Simply, what I think a lot of us forget, they are those system processes that
make up an Oracle instance, are configured through various parameters, and
handle the manipulation and presentation of information from our Oracle
database. Please, let’s just remember they are not the database as they are not
physical components like data files.

As noted previously, not all background processes that COULD
run need to be or will be running for your particular instance. For instance,
you may or may not have archiving turned on and thus you may or may not have
the archiver process active on your system. Not all background processes are
mandatory for an instance. Also note, and the reason for an increasing number
of background processes, each background process has its own unique set of
purposes and tasks. These may change from one version to the next, which is the
reason for this paper. I too have grown lazy in knowing what all the processes
are for. As such, I created this article to refresh my memory–eager to learn
what new processes Oracle has conjured up. So please read along, get a feel for
what the process name is, and see if you find any you are unfamiliar with. Also,
note that these processes are those that are typically running after a fresh,
common, and basic installation.

Now some of us might be saying, how do I know what processes
are running? It is very easy; just issue the following `ps` command from the
Unix prompt and you will quickly be presented a simple listing. Since all
Oracle processes start with the ‘ora_’ prefix, issue the following command:


ps -afe | grep ora_
oracle 2565 1 0 12:54 ? 00:00:01 ora_pmon_db11
oracle 2567 1 0 12:54 ? 00:00:00 ora_vktm_db11
oracle 2571 1 0 12:54 ? 00:00:00 ora_diag_db11
oracle 2573 1 0 12:54 ? 00:00:00 ora_dbrm_db11
oracle 2575 1 0 12:54 ? 00:00:00 ora_psp0_db11
oracle 2579 1 0 12:54 ? 00:00:18 ora_dia0_db11
oracle 2581 1 0 12:54 ? 00:00:00 ora_mman_db11
oracle 2583 1 0 12:54 ? 00:00:01 ora_dbw0_db11
oracle 2585 1 0 12:54 ? 00:00:01 ora_lgwr_db11
oracle 2587 1 0 12:54 ? 00:00:05 ora_ckpt_db11
oracle 2589 1 0 12:54 ? 00:00:04 ora_smon_db11
oracle 2591 1 0 12:54 ? 00:00:00 ora_reco_db11
oracle 2593 1 0 12:54 ? 00:00:10 ora_mmon_db11
oracle 2595 1 0 12:54 ? 00:00:02 ora_mmnl_db11
oracle 2597 1 0 12:54 ? 00:00:00 ora_d000_db11
oracle 2599 1 0 12:54 ? 00:00:00 ora_s000_db11
oracle 2611 1 0 12:54 ? 00:00:00 ora_smco_db11
oracle 2613 1 0 12:54 ? 00:00:00 ora_fbda_db11
oracle 2615 1 0 12:54 ? 00:00:00 ora_qmnc_db11
oracle 2619 1 0 12:55 ? 00:00:00 ora_q000_db11
oracle 2621 1 0 12:55 ? 00:00:00 ora_q001_db11
oracle 2806 1 0 12:59 ? 00:00:05 ora_cjq0_db11
oracle 4469 1 0 16:45 ? 00:00:00 ora_w000_db11

To note from this listing is the process ID for the UNIX process
which is the second value in each row.

If you wanted to look at these processes from within the
Oracle database, you need only issue the following SQL command:


SQL> SELECT spid,program,background FROM v$process
SPID PROGRAM B
—— ———————————————— –
PSEUDO
2565 oracle@ludwig.pinehorse.com (PMON) 1
2567 oracle@ludwig.pinehorse.com (VKTM) 1
2571 oracle@ludwig.pinehorse.com (DIAG) 1
2573 oracle@ludwig.pinehorse.com (DBRM) 1
2575 oracle@ludwig.pinehorse.com (PSP0) 1
2581 oracle@ludwig.pinehorse.com (MMAN) 1
2579 oracle@ludwig.pinehorse.com (DIA0) 1
2583 oracle@ludwig.pinehorse.com (DBW0) 1
2585 oracle@ludwig.pinehorse.com (LGWR) 1
2587 oracle@ludwig.pinehorse.com (CKPT) 1
2589 oracle@ludwig.pinehorse.com (SMON) 1
2591 oracle@ludwig.pinehorse.com (RECO) 1
2593 oracle@ludwig.pinehorse.com (MMON) 1
2595 oracle@ludwig.pinehorse.com (MMNL) 1
2597 oracle@ludwig.pinehorse.com (D000)
2599 oracle@ludwig.pinehorse.com (S000)
2806 oracle@ludwig.pinehorse.com (CJQ0) 1
4469 oracle@ludwig.pinehorse.com (W000) 1
4451 oracle@ludwig.pinehorse.com (TNS V1-V3)
2611 oracle@ludwig.pinehorse.com (SMCO) 1
2613 oracle@ludwig.pinehorse.com (FBDA) 1
2615 oracle@ludwig.pinehorse.com (QMNC) 1
2619 oracle@ludwig.pinehorse.com (q000) 1
2621 oracle@ludwig.pinehorse.com (q001) 1

From the above two listings, please note the equality of the UNIX
process ID and the Oracle SPID. Now for a brief description of some of those
more common processes you should see in your listing:

ora_pmon_<SID> The Process Monitor is responsible for monitoring processes. In this
case pmon will perform recovery for user processes that fail, will rollback
uncommitted transactions, and clean up the buffer cache and free the resources
being consumed.
ora_vktm_<SID> The Virtual
Keeper of Time process is responsible for keeping track of the wall-clock
time and used as a reference-time counter.
ora_diag_<SID> The Diagnosability Process handles hang detection and deadlock resolution.
ora_dbrm_<SID> The Database
Resource Manager process takes care of setting up
resource manager related tasks and resource plans.
ora_psp0_<SID> The Process
Spawner will
spawn Oracle processes as needed.
ora_mman_<SID> The mman process is used for internal database tasks.
ora_dbw0_<SID> The Database
Writer process(s) handle the writing of dirty/modified buffers in the
database buffer cache to datafiles. Depending on the activity of the database,
there may be multiple database writers to keep up with the changes occurring.
ora_lgwr_<SID> The Log
Writer process is responsible for writing data from the redo log buffers to
the redo log files on disk.
ora_ckpt_<SID> The Checkpoint
Process is responsible for signaling the synchronization of all database files
with checkpoint information – ensuring data consistency and faster database
recovery in case of a crash.
ora_smon_<SID> The System
Monitor process is responsible for instance recovery – cleaning up temporary
segments that are not required and coalescing contiguous free extents in dictionary
managed tablespaces.
ora_reco_<SID> The Recoverer
process is responsible for recovering all in-doubt
transactions in a distributed database setup – connecting to remote
databases and resolving pending transactions.
ora_mmon_<SID> The mmon process will perform manageability-related
background tasks such as issuing alerts, taking snapshots, and capturing
statistics values for SQL objects.
ora_mmnl_<SID> The mmnl
process will perform other but light-weight
manageability-related tasks such as capturing session history and metrics
computation.
ora_d000_<SID> The Dispatcher processes will listen to and receive requests from
connected sessions – placing them in the request queue on the next available
server for further processing. Dispatcher processes will also pick up outgoing
responses from a results queue and transmit those results back to the clients.
ora_s000_<SID> The Shared Server process will pick up requests from a call request
queue, make the calls to the database to resolve the request, and then return
the results to a result queue.
ora_smco_<SID> The Space
Management Coordinator process handles the coordination and execution of space management
tasks – spawning wnnn processes to carry out the task.
ora_fbda_<SID> The Flashback
Data Archiver process archives historical data for tracked tables into the
flashback data archives – storing a pre-image of the rows into the flashback
archive and keeping the metadata for those rows.
ora_cjq0_<SID> The Job
Queue process is used
for batch processing – running user jobs within a scheduler service. Able to
run a large number of concurrent jobs, the coordinator process (cjq0) will
spawn job queue slaves (jnnn) to actually run the jobs.

So, the question might be running through your head as to why we should care
about the processes. Processes are at the very core of an Oracle instance. They
are the link between your user applications and the database. If the system
processes hang or consume large amounts of resources you need to find out why.
The best place to start is to understand what each process does and how it
interacts with the Oracle database. For instance, if lgwr was consuming large
amounts of system resources you might conclude that the configuration you are
using for log maintenance is in need of tuning.

»


See All Articles by Columnist
James Koopmann

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles