Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 5, 2008

Oracle Database 11g - Common Background Processes

By James Koopmann

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
------ ------------------------------------------------ -
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

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM