Oracle UNIX the Recoverer Background Process (RECO) Administration Oracle UNIX/Linux Tips by Burleson Consulting

The recoverer background process (RECO)

RECO is a background process for distributed transactions. The RECO process manager two-phase commits to track and resolve in-doubt transactions.

In UNIX, the RECO process awakens whenever a single transaction spans two geographical locations.  For example, the following transaction requires a two-phase commit:

Update    -- Local update transaction

   emp
set
   sal = sal * 1.1
where
   dept = 30;

update   -- Remote update transaction
   emp@new_york
set
   sal = sal * 1.1
where
   dept = 40;

In this case, the RECO process will ensure that both the remote update and the local update complete or rollback as a single unit.

You will see the RECO process running whenever the distributed option of Oracle is installed, but the RECO process will only show activity when remote updates are being executed as part of a two-phase commit.

Oracle snapshot background process (SNPnnn)

The Oracle snapshot processes are an integral part of Oracle advanced replication and Oracle materialized views.  The SNP processes are used to refresh replicated and pre-aggregated data.  The SNP processes will awaken when signaled by the implementation dbms_job routine.  Snapshots and materialized views are scheduled for periodic refreshes with the dbms_job package, and the SNP process will awaken whenever a request is made to refresh a snapshot or materialized view.

Now that we understand the functions of the major Oracle background processes, and how the background processes interact with UNIX, let?s do a brief review of the tools that we can use to watch CPU consumption for the Oracle background processes.

Monitoring Oracle CPU consumption in UNIX

As we noted in Chapter 2, most Oracle DBA?s use the glance, watch or top utilities to monitor CPU consumption on their Oracle server. Since this text is targeted at UNIX, it is important to understand that there is a difference between how the background processes/threads implemented on UNIX and NT. On UNIX, a separate operating system process is created to run each of the background functions listed above. On NT, they are run as different threads within the same process.

As a quick review from earlier chapters, any of the following ps commands can be used to display the top CPU consumers from UNIX.

root> ps auxgw|sort +2|tail

USER       PID %CPU %MEM   SZ  RSS    TTY STAT    STIME  TIME COMMAND
oracle   14922  0.6  1.0 8300 5720      - A    01:01:46  2:57 oracleprod
oracle   22424  0.6  1.0 8328 6076      - A    07:48:43  0:21 oracleprod
oracle   44518  0.8  1.0 8080 5828      - A    08:47:47  0:02 oracleprod
oracle   20666  1.0  1.0 8304 6052      - A    08:15:19  0:22 oracleprod
oracle   13168  1.6  1.0 8196 5760      - A    05:33:06  3:15 oracleprod
oracle   17402  2.5  1.0 8296 6044      - A    07:27:04  2:06 oracleprod
oracle   25754  2.5  1.0 8640 6388      - A    08:10:03  1:03 oracleprod
oracle   41616  4.5  1.0 8312 6052      - A    07:00:59  4:57 oracleprod

Yet another approach uses the egrep command to display the top CPU consumers.  In the example below we see an Oracle process called from SQL*Forms using 18.9% of the CPU:

root> ps augxww|egrep "RSS| "|head

USER       PID %CPU %MEM   SZ  RSS  TTY STAT    STIME  TIME COMMAND
oracle     516 18.9  0.0   16    4   - A      Nov 21 194932:05 runform45
oracle   41616  4.4  1.0 8312 6052   - A    07:00:59  4:57
oracle   20740  2.7  1.0 8140 5888   - A    08:52:32  0:02
oracle   17402  2.4  1.0 8296 6044   - A    07:27:04  2:06
oracle   25754  2.4  1.0 8640 6388   - A    08:10:03  1:03
oracle   13168  1.6  1.0 8196 5760   - A    05:33:06  3:15
oracle   20666  1.0  1.0 8304 6052      - A    08:15:19  0:22
oracle   14922  0.6  1.0 8300 5720      - A    01:01:46  2:57
oracle   44518  0.6  1.0 8080 5828      - A    08:47:47  0:02

Here is another more sophisticated permutation of the ps command to display the top 20 CPU consumers in UNIX.  Note that in this command we eliminate the Oracle background processes by using grep to only find LOCAL processes, and then we sort on column two (actually the third column because UNIX starts numbering columns from zero), to sort the result set in descending order of CPU consumption.

root> ps -ef|grep LOCAL|cut -c1-15,42-79|sort -rn +2 | head -20

  oracle 27825   3:45 oraclePROD (DESCRIPTION=(LOCAL=
  oracle 25856   2:49 oraclePROD (DESCRIPTION=(LOCAL=
  oracle 28018   1:00 oraclePROD (DESCRIPTION=(LOCAL=
  oracle 27787   0:90 oraclePROD (DESCRIPTION=(LOCAL=
  oracle 27750   0:83 oraclePROD (DESCRIPTION=(LOCAL=
  oracle 27447   0:71 oraclePROD (DESCRIPTION=(LOCAL=
  oracle 26519   0:63 oraclePROD (DESCRIPTION=(LOCAL=
  oracle 25896   0:51 oraclePROD (DESCRIPTION=(LOCAL=
  oracle 25894   0:50 oraclePROD (DESCRIPTION=(LOCAL=
  oracle 25892   0:41 oraclePROD (DESCRIPTION=(LOCAL=
   oracle 25890   0:40 oraclePROD (DESCRIPTION=(LOCAL=
  oracle 25888   0:39 oraclePROD (DESCRIPTION=(LOCAL=
  oracle 25886   0:11 oraclePROD (DESCRIPTION=(LOCAL=
  oracle 25884   0:11 oraclePROD (DESCRIPTION=(LOCAL=
  oracle 25882   0:11 oraclePROD (DESCRIPTION=(LOCAL=
  oracle 25880   0:10 oraclePROD (DESCRIPTION=(LOCAL=
  oracle 25878   0:02 oraclePROD (DESCRIPTION=(LOCAL=
  oracle 25876   0:02 oraclePROD (DESCRIPTION=(LOCAL=

As we also remember from Chapter 2, many Oracle DBA use the glance, watch, sar or top UNIX utilities to monitor the behavior of Oracle processes in UNIX. Please see Chapter 2 for details on using these tools in your UNIX environment.

Oracle interaction with the UNIX server CPU?s

In UNIX, Oracle utilizes as many CPUs as are available on the database server. This is completely transparent to the Oracle DBA since the UNIX task dispatcher manages this interaction according to the NICE values of the background processes.

However, you can take a dump of important Oracle background processes and see the detailed interaction with UNIX. Let?s use the PMON process as an example and see how to generate and read a process dump.

Getting a background process dump from Oracle

Oracle has delivered a hidden tool within server manager (SQL*Plus in Oracle8 and beyond), that allows you to view specific internal Oracle structures and the UNIX system calls. The following procedure provides a process dump for the Oracle database and shows all statistics for any Oracle background process.

STEP 1 ? We start by issuing the oradebug setorapid command to process 2.  In this example, process number 2 is the Oracle process monitor (PMON)

SQL> connect system/mnager as sysdba;
SQL> oradebug setorapid 2

Unix process pid: 25159, image: ora_pmon_test

STEP 2 ? Next we issue the oradebug procstat command to generate the statistics

SQL> connect system/manager as sysdba;
SQL> oradebug procstat

Statement processed.

STEP 3 ? Now we can use the oradebug TRACEFILE_NAME command to see the location of our trace file

SQL> oradebug TRACEFILE_NAME
/app/oracle/admin/orcl/bdump/pmon_25159.trc

Now we have the name of our trace file form the PMON process. Below is the listing from this trace file.  As you can see, this provides detailed information regarding important PMON activities.

SQL> !cat /u01/app/oracle/admin/mysid1/bdump/mysid1_pmon_25159.trc
Dump file /u01/app/oracle/admin/mysid1/bdump/mysid1_pmon_25159.trc
Oracle8i Enterprise Edition Release 8.1.6.1.0 - 64bit Production

With the Partitioning option

JServer Release 8.1.6.1.0 - 64bit Production
ORACLE_HOME = /u01/app/oracle/product/8.1.6_64

ystem name:    SunOS
Node name:      burleson-01
Release:        5.8
Version:        Generic_108528-03
Machine:        sun4u
Instance name: mysid1
Redo thread mounted by this instance: 1
Oracle process number: 2
Unix process pid: 25159, image: oracle@burleson-01 (PMON)

*** 2001-07-04 12:13:25.042
*** SESSION ID:(1.1) 2001-07-04 12:13:24.979
----- Dump of Process Statistics -----
User level CPU time = 121
System call CPU time = 47
Other system trap CPU time = 0
Text page fault sleep time = 0
Data page fault sleep time = 0
Kernel page fault sleep time = 0
User lock wait sleep time = 0
All other sleep time = 562367527
Wait-cpu (latency) time = 0
Minor page faults = 0
Major page faults = 346
Swaps = 0
Input blocks = 251
Output blocks = 0
Messages sent = 0
Messages received = 0
Signals received = 1827660
Voluntary context switches = 1828280
Involuntary context switches = 58531459
System calls = 141934171
Chars read and written = 28650
Process heap size = 1425416
Process stack size = 565248

Now, let?s take a closer look at these interaction statistics.  The process

statistics section we see the amount of time that PMON spent in USER versus SYSTEM CPU time.  As we may know, USER CPU time is incurred on behalf of specific UNIX processes, while SYETM CPU time is incurred when performing a system-wide service.

We also see details about the context switches between UNIX and Oracle, as well as details about RAM memory usage and other internal details about the interaction between PMON and UNIX. These detailed statistics can be very useful in cases where you suspect inappropriate behavior within a background process and you need to see detailed about the interaction between the background process and UNIX.

Now that we see how to inspect the Oracle interaction with the UNIX processors, let?s take a look at the interaction between Oracle and RAM memory on the UNIX server.

Oracle interaction with the UNIX disk I/O sub-system

We have already covered the use of STATSPACK and the UNIX iostat utility to see disk reads and writes from Oracle, but we are now ready to take a closer look at the interaction between Oracle and the UNIX disk sub-system.

Remember, the tools we presented in Chapter 4 give us techniques for monitoring disk I/O over time periods, but we still need details about I/O waits and other internal UNIX events that are associated with disk I/O.

Once a background process issues an I/O request, it manifests itself in UNIX as an I/O object. To UNIX, an I/O object represents a byte stream and UNIX is not aware that it is reading a database file. Within UNIX, various operations are defined on the byte stream:

      read()- Read to the UNIX stream

      write()- Write to the UNIX stream

      close() ? Close the UNIX stream

While these I/O details are well hidden inside the bowels of Oracle, there are some techniques that we can use to see I/O details.

Checking for Oracle file wait conditions

As we know, Oracle provides clues about data files that are experiencing a disproportional amount of I/O activity and this information is critical when load balancing the I/O sub-system for any Oracle database.  As a review, we can compare I/O values for individual data files in the stats$filestatxs table with the system-wide I/O values in the stats$sysstat table to identify any data files that experience a disproportional amount of I/O activity. Oracle also records wait statistics that tracks the wait_count for all data files in the V$ views and also inside STATSPACK.

The basic information for this information is in the v$waitstat view, but STATSPACK users now have the ability to store file wait information and create reports that display waiting files.  The following script can be run to detect those files that have more than 800 wait events per hour.

rpt_iowait.sql
break on snapdate skip 2

column snapdate format a16
column filename format a40

select
   to_char(snap_time,'yyyy-mm-dd HH24') snapdate,
   old.filename,
   new.wait_count-old.wait_count waits
from
   perfstat.stats$filestatxs old,
   perfstat.stats$filestatxs new,
   perfstat.stats$snapshot   sn
where
   snap_time > sysdate-&1
and
   new.wait_count-old.wait_count > 800
and
   new.snap_id = sn.snap_id
and
   old.filename = new.filename
and
   old.snap_id = sn.snap_id-1
and
   new.wait_count-old.wait_count > 0
;

Here is a sample listing from this script.  This is a valuable tool for the Oracle professional to see when their database is experiencing excessive wait conditions.

***********************************************************
When there is high I/O waits, disk bottlenecks may exist
Run iostats to find the hot disk and shuffle files to
remove the contention
***********************************************************

SNAPDATE         FILENAME                              WAITS           
---------------- ----------------------------------- ----------          
2001-01-28 23    /u03/oradata/PROD/applsysd01.dbf         169 
                 /u04/oradata/PROD/applsysx01.dbf         722           
                 /u03/oradata/PROD/rbs01.dbf             3016           

2001-01-30 16    /u03/oradata/PROD/mrpd01.dbf             402           

2001-01-31 23    /u03/oradata/PROD/applsysd01.dbf         319           
                 /u04/oradata/PROD/applsysx01.dbf         402           

As we see most of the details about disk I/O are hidden from view, but Oracle does provide a window into detail about those time where UNIX could not complete an immediate I/O.

Now let?s wrap-up this chapter and review the main points and concepts surrounding Oracle?s interaction with UNIX.

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.