Do you have an Oracle Background?
Posted by Joel Goodman on 22/11/2010
In my recent 11gR2 Grid Infrastructure course, a discussion arose concerning an old chestnut of a topic: that of Oracle Database Instance background processes. This discussion arises quite often on courses and seminars due to the plethora of “background” processes added to the Oracle architecture since Oracle 7.
Back in Oracle 7, there were four mandatory background processes: DBWR, LGWR, SMON and PMON. Even the CKPT process was not required as LGWR could perform what CKPT did. And the RECO process was only started if the DISTRIBUTED_TRANSACTIONS parameter was set. There were also some additional background processes in Oracle 7 if one used Parallel Server, Shared Server (then called Multithreaded Server) but in general things were quite simple then.
But since Oracle 8.0, many new background processes have been added for different reasons:
- To support features of the Oracle Server such as Data Guard, Advanced Queuing or Job Scheduling
- To improve performance of the server such as time management, I/O slaves or connection pooling
- To enable parallel execution of certain logic such as Logical Standby Apply, Archiving Logging or Streams
- To Implement ASM in both RDBMS and ASM instances from 10g onwards
As of Oracle Database 11g R2 there are a huge number of possible background processes although any one RDBMS or ASM instance will only ever have a subset of all the possible backgrounds that are built into the architecture.
A good place to begin with backgrounds is the documentation. In Appendix F of the Oracle Database Reference, there is a table listing all the backgrounds defined by Oracle with the name and description of the process function. Some backgrounds such as PMON, VKRM or VKTM have specific names as there is only ever one of them in an instance. Some processes have generic names, when more than one such process may exist in an instance.
To determine which background processes exist in a specific RDBMS or ASM instance requires the use of two dynamic performance views:
- V$PROCESS
- V$BGPROCESS
To look at the performance overheads of the background processes we can use:
- V$SYS_TIME_MODEL
- V$SERVICE_STATS
First we should look at v$process and v$bgprocess:
SYS@tutor5d_nledu02 SQL> desc v$process Name Null? Type ----------------------------------------- -------- ---------------------------- ADDR RAW(4) PID NUMBER SPID VARCHAR2(24) USERNAME VARCHAR2(15) SERIAL# NUMBER TERMINAL VARCHAR2(30) PROGRAM VARCHAR2(48) TRACEID VARCHAR2(255) TRACEFILE VARCHAR2(513) BACKGROUND VARCHAR2(1) LATCHWAIT VARCHAR2(8) LATCHSPIN VARCHAR2(8) PGA_USED_MEM NUMBER PGA_ALLOC_MEM NUMBER PGA_FREEABLE_MEM NUMBER PGA_MAX_MEM NUMBER
The background column will contain a “1” for processes considered to be background processes according to this view. The examples that follow is from a simple RDBMS instance with no RAC, Data Guard, Streams, ASM or other advanced configurations:
SQL> select count(*) from v$process where background = '1'; COUNT(*) ---------- 21 1 row selected.
SQL> select program, background
2 from v$process order by 2;
PROGRAM B
---------------------------------------- -
oracle@nledu02.nl.oracle.com (PMON) 1
oracle@nledu02.nl.oracle.com (q000) 1
oracle@nledu02.nl.oracle.com (q001) 1
oracle@nledu02.nl.oracle.com (VKTM) 1
oracle@nledu02.nl.oracle.com (DIAG) 1
oracle@nledu02.nl.oracle.com (DBRM) 1
oracle@nledu02.nl.oracle.com (PSP0) 1
oracle@nledu02.nl.oracle.com (DIA0) 1
oracle@nledu02.nl.oracle.com (MMAN) 1
oracle@nledu02.nl.oracle.com (DBW0) 1
oracle@nledu02.nl.oracle.com (LGWR) 1
oracle@nledu02.nl.oracle.com (CKPT) 1
oracle@nledu02.nl.oracle.com (SMON) 1
oracle@nledu02.nl.oracle.com (RECO) 1
oracle@nledu02.nl.oracle.com (MMON) 1
oracle@nledu02.nl.oracle.com (MMNL) 1
oracle@nledu02.nl.oracle.com (SMCO) 1
oracle@nledu02.nl.oracle.com (ARC0) 1
oracle@nledu02.nl.oracle.com (FBDA) 1
oracle@nledu02.nl.oracle.com (QMNC) 1
oracle@nledu02.nl.oracle.com (W000) 1
oracle@nledu02.nl.oracle.com
PSEUDO
23 rows selected.
There last two processes are not considered background in the output. One is the program called “oracle@nledu02.nl.oracle.com” which is the server process used by the SQL*PLUS client used for the query, and the other is the PSEUDO process. PSEUDO is not a real process and is used so that sessions in the session array may always have a process associated with them for reasons such as:
- If the real process fails
- Shared Server is used and the session is currently not associated with a server process when waiting for the next input.
A more informative view though, is v$bgprocess which contains the process name as well as a description of the process. Here is the description:
SQL> desc v$bgprocess Name Null? Type ----------------------------------------- -------- ---------------------------- PADDR RAW(4) PSERIAL# NUMBER NAME VARCHAR2(5) DESCRIPTION VARCHAR2(64) ERROR NUMBER
This view externalises some of the contents of an array held in the fixed SGA, which contains a slot for each possible background process. Those processes that exist in the instance where the query is done, will have a pointer to the process memory structure in the v$bgprocess.paddr column. To see how this works a simple count of the slots in the bgprocess array is sufficient:
SQL> select count (*) from v$bgprocess; COUNT(*) ---------- 211 1 row selected.
This shows the number of entries in the array held in the SGA to be 211 for this version of Oracle and not the number of backgrounds in this instance. The number you get will vary depending on version and possibly on patch level. To see which background processes really exist, the query must filter out rows that have no process address. Since the v$bgprocess.paddr column is a raw datatype, use the hextoraw function in the filter as follows:
SQL> select count (*) from v$bgprocess where paddr != hextoraw(0); COUNT(*) ---------- 19 1 row selected.
The results show that 19 background processes exist in this instance according to this view. To see them listed use the following query:
SQL> select name,description from v$bgprocess where paddr != hextoraw(0) order by 1 NAME DESCRIPTION ----- -------------------------------------------------- ARC0 Archival Process 0 CJQ0 Job Queue Coordinator CKPT checkpoint DBRM Resource Manager process DBW0 db writer process 0 DIA0 diagnosibility process 0 DIAG diagnosibility process FBDA Flashback Data Archiver Process LGWR Redo etc. MMAN Memory Manager MMNL Manageability Monitor Process 2 MMON Manageability Monitor Process PMON process cleanup PSP0 process spawner 0 QMNC AQ Coordinator RECO distributed recovery SMCO Space Manager Process SMON System Monitor Process VKTM Virtual Keeper of TiMe process 19 rows selected.
This shows the value of this view as it has a brief description of the background process which would not be available by using the unix ps command:
tutor5d> pgrep -lf tutor5d 18620 ora_w000_tutor5d 18666 ora_pmon_tutor5d 18668 ora_vktm_tutor5d 18672 ora_diag_tutor5d 18674 ora_dbrm_tutor5d 18676 ora_psp0_tutor5d 18678 ora_dia0_tutor5d 18680 ora_mman_tutor5d 18682 ora_dbw0_tutor5d 18684 ora_lgwr_tutor5d 18686 ora_ckpt_tutor5d 18688 ora_smon_tutor5d 18690 ora_reco_tutor5d 18692 ora_mmon_tutor5d 18694 ora_mmnl_tutor5d 18724 ora_arc0_tutor5d 18732 ora_fbda_tutor5d 18740 ora_qmnc_tutor5d 18793 ora_q000_tutor5d 18795 ora_q001_tutor5d 19686 ora_smco_tutor5d 32308 oracletutor5d (LOCAL=NO)
The output of the pgrep command shows the server process and 21 other processes but does not show any description.
One interesting anomaly in the use of the two views is that v$process and v$bgprocess do not appear to agree on which processes are backgrounds. The number of backgrounds listed in the query of v$process above was 21, but v$bgprocess queried a moment later had only 19. To resolve this an outer join between the two views is helpful as follows:
SQL> select p.pid,b.name,p.program,b.description 2 from v$bgprocess b, v$process p 3 where b.paddr(+) = p.addr 4 and p.background = '1' 5 order by name 6 / Hit Any Key to Continue PID NAME PROGRAM DESCRIPTION ---------- ----- ---------------------------------------- ---------------------------------------- 17 ARC0 oracle@nledu02.nl.oracle.com (ARC0) Archival Process 0 11 CKPT oracle@nledu02.nl.oracle.com (CKPT) checkpoint 5 DBRM oracle@nledu02.nl.oracle.com (DBRM) Resource Manager process 9 DBW0 oracle@nledu02.nl.oracle.com (DBW0) db writer process 0 7 DIA0 oracle@nledu02.nl.oracle.com (DIA0) diagnosibility process 0 4 DIAG oracle@nledu02.nl.oracle.com (DIAG) diagnosibility process 18 FBDA oracle@nledu02.nl.oracle.com (FBDA) Flashback Data Archiver Process 10 LGWR oracle@nledu02.nl.oracle.com (LGWR) Redo etc. 8 MMAN oracle@nledu02.nl.oracle.com (MMAN) Memory Manager 15 MMNL oracle@nledu02.nl.oracle.com (MMNL) Manageability Monitor Process 2 14 MMON oracle@nledu02.nl.oracle.com (MMON) Manageability Monitor Process 2 PMON oracle@nledu02.nl.oracle.com (PMON) process cleanup 6 PSP0 oracle@nledu02.nl.oracle.com (PSP0) process spawner 0 19 QMNC oracle@nledu02.nl.oracle.com (QMNC) AQ Coordinator 13 RECO oracle@nledu02.nl.oracle.com (RECO) distributed recovery 16 SMCO oracle@nledu02.nl.oracle.com (SMCO) Space Manager Process 12 SMON oracle@nledu02.nl.oracle.com (SMON) System Monitor Process 3 VKTM oracle@nledu02.nl.oracle.com (VKTM) Virtual Keeper of TiMe process 22 oracle@nledu02.nl.oracle.com (q000) 20 oracle@nledu02.nl.oracle.com (W000) 24 oracle@nledu02.nl.oracle.com (q001) 21 rows selected.
This shows that for my instance, processes q000, q001 which are Advanced Queuing server class processes which act as slaves for the AQ coordinator process QMNC, and W000 which is a Space Management Slave of the SMC0 Space Management Coordinator process, are considered backgrounds by v$process but not by v$bgprocess. This anomaly whereby the two views disagree on certain processes has been present in Oracle for several releases. All the permanent background processes seem to be listed as such in both views but some of the slaves are not. Note that you may get different results with these queries depending on the Oracle version, release and options chosen. They are all listed as backgrounds in the documentation and in my experience the differences between the two views is always a matter of only two or three processes.
Now we can turn our attention to performance overheads of background processes using v$sys_time_model and v$service_stats
The Oracle “Services” model has a built in service called sys$background to account for overheads of all the background processes since instance startup. Use view v$service_stats to query this:
SQL> desc v$service_stats Name Null? Type ----------------------------------------- -------- ---------------------------- SERVICE_NAME_HASH NUMBER SERVICE_NAME VARCHAR2(64) STAT_ID NUMBER STAT_NAME VARCHAR2(64) VALUE NUMBER
SQL> col service_name for a20 SQL> col stat_name for a40 SQL> break on service_name skip 1 SQL> SQL> select service_name,stat_name,value from v$service_stats order by service_name; SERVICE_NAME STAT_NAME VALUE -------------------- ---------------------------------------- ------------------ SYS$BACKGROUND logons cumulative 112 user I/O wait time 206452263 DB time 0 DB CPU 0 parse count (total) 0 parse time elapsed 0 execute count 0 sql execute elapsed time 0 opened cursors cumulative 21091 session logical reads 3609388 physical reads 53270 physical writes 40315 redo size 356952612 user commits 3 workarea executions - optimal 2616 workarea executions - onepass 0 workarea executions - multipass 0 session cursor cache hits 17718 user rollbacks 0 db block changes 3157298 gc cr blocks received 0 gc cr block receive time 0 gc current blocks received 0 gc current block receive time 0 cluster wait time 0 concurrency wait time 2082940 application wait time 0 user calls 0 .......
The same stats are produced for the built in service sys$users, which is used for locally connected sessions and for work performed by job slaves using the DBMS_JOB package, where no service may be assigned to the job. There are also stats for each dba defined service. The proportion of each resource consumed by the backgrounds, may be determined by comparing the stats for the sys$background service to the aggregate of all other services.
The v$sys_time_model view breaks down time based on whether it is used by background or foreground processes and then within those categories, it is subdivided further:
SQL> desc v$sys_time_model Name Null? Type ----------------------------------------- -------- ---------------------------- STAT_ID NUMBER STAT_NAME VARCHAR2(64) VALUE NUMBER SQL> select stat_name,value from v$sys_time_model; STAT_NAME VALUE ------------------------------------------------------------ ------------------ DB time 24176481 DB CPU 2449619 background elapsed time 420118995 background cpu time 43627383 sequence load elapsed time 6984 parse time elapsed 9181724 hard parse elapsed time 8845975 sql execute elapsed time 24707076 connection management call elapsed time 325163 failed parse elapsed time 1966 failed parse (out of shared memory) elapsed time 0 hard parse (sharing criteria) elapsed time 26532 hard parse (bind mismatch) elapsed time 481 PL/SQL execution elapsed time 581091 inbound PL/SQL rpc elapsed time 0 PL/SQL compilation elapsed time 2102098 Java execution elapsed time 0 repeated bind elapsed time 12157 RMAN cpu time (backup/restore) 0 19 rows selected.
Using this allows us to see what proportion of time is used by the backgrounds in a hierarchical fashion. The “background elapsed time” statistic includes the “background cpu time” and the “RMAN cpu time” statistics. This may be compared to the “DB time” statistic which includes the time for all the remaining statistics reported in the output.
My colleague Harald van Breederode from Oracle Netherlands and Eric Valk also from the Netherlands produced this very useful sql case statement, to enable the hierarchical view of v$sys_time_model output.
SYS@tutor5d_nledu02 SQL> rem This does not show the hierarchy so lets try again SYS@tutor5d_nledu02 SQL> rem using hierarchical sql SYS@tutor5d_nledu02 SQL> pause SYS@tutor5d_nledu02 SQL> SYS@tutor5d_nledu02 SQL> select lpad( ' ', ( level - 1 ) * 2 ) || 2 stat_name as stat_name 3 , value 4 , case stat_name 5 when 'background elapsed time' then '1' 6 when 'background cpu time' then '1-1' 7 when 'RMAN cpu time (backup/restore)' then '1-1-1' 8 when 'DB time' then '2' 9 when 'DB CPU' then '2-1' 10 when 'connection management call elapsed time' then '2-2' 11 when 'sequence load elapsed time' then '2-3' 12 when 'sql execute elapsed time' then '2-4' 13 when 'parse time elapsed' then '2-5' 14 when 'hard parse elapsed time' then '2-5-1' 15 when 'hard parse (sharing criteria) elapsed time' then '2-5-1-1' 16 when 'hard parse (bind mismatch) elapsed time' then '2-5-1-1-1' 17 when 'failed parse elapsed time' then '2-5-2' 18 when 'failed parse (out of shared memory) elapsed time' then '2-5-2-1' 19 when 'PL/SQL execution elapsed time' then '2-6' 20 when 'inbound PL/SQL rpc elapsed time' then '2-7' 21 when 'PL/SQL compilation elapsed time' then '2-8' 22 when 'Java execution elapsed time' then '2-9' 23 when 'repeated bind elapsed time' then '2-A' 24 else '9' 25 end as pos 26 from ( 27 select /*+ no_merge */ 28 stat_name 29 , stat_id 30 , value 31 , case 32 when stat_id in (2411117902) 33 then 2451517896 34 when stat_id in (268357648) 35 then 3138706091 36 when stat_id in (3138706091) 37 then 372226525 38 when stat_id in (4125607023) 39 then 1824284809 40 when stat_id in (2451517896) 41 then 4157170894 42 when stat_id in (372226525,1824284809) 43 then 1431595225 44 when stat_id in (3649082374,4157170894) 45 then null 46 else 3649082374 47 end parent_stat_id 48 from v$sys_time_model 49 ) 50 connect by prior stat_id = parent_stat_id 51 start with parent_stat_id is null 52 order by pos; Hit Any Key to Continue STAT_NAME VALUE ------------------------------------------------------------ ------------------ background elapsed time 420192980 background cpu time 43632383 RMAN cpu time (backup/restore) 0 DB time 24177525 DB CPU 2450619 connection management call elapsed time 325163 sequence load elapsed time 6984 sql execute elapsed time 24707750 parse time elapsed 9181821 hard parse elapsed time 8845975 hard parse (sharing criteria) elapsed time 26532 hard parse (bind mismatch) elapsed time 481 failed parse elapsed time 1966 failed parse (out of shared memory) elapsed time 0 PL/SQL execution elapsed time 581197 inbound PL/SQL rpc elapsed time 0 PL/SQL compilation elapsed time 2102098 Java execution elapsed time 0 repeated bind elapsed time 12157 19 rows selected.
The values are in microseconds.
So now I hope you are on the road to a better background in Oracle backgrounds.
Joel
November 2010
The Blog from the DBA Classroom 
Dion Cho said
The hierachical version of the time model query seems very clever, even though it would have version dependency.
emre baransel said
very useful info, thanks!