The Blog from the DBA Classroom

By: Joel Goodman

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:

  1. To support features of the Oracle Server such as Data Guard, Advanced Queuing or Job Scheduling
  2. To improve performance of the server such as time management, I/O slaves or connection pooling
  3. To enable parallel execution of certain logic such as Logical Standby Apply, Archiving Logging or Streams
  4. 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:

  1. V$PROCESS
  2. V$BGPROCESS

To look at the performance overheads of the background processes we can use:

  1. V$SYS_TIME_MODEL
  2. 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

Advertisements

2 Responses to “Do you have an Oracle Background?”

  1. Dion Cho said

    The hierachical version of the time model query seems very clever, even though it would have version dependency.

  2. very useful info, thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: