Oracle interfaces with the UNIX Server

This chapter is concerned with the interface layer between the Oracle and the UNIX operating system.  As we know, Oracle interacts with the I/O sub-system to read and write from disk, and Oracle also interfaces with the RAM memory and CPU on the database server.

While only a process dump can directly display the interactions between Oracle and the server, Oracle provides several tools including background process dumps to show us the nature of these interactions between Oracle and UNIX. The topics in this chapter will include:

* The interaction between the Oracle background processes and CPU consumption

* The interaction between Oracle and the RAM memory regions, including PGA and SGA memory

* The interaction between Oracle and the disk devices

We have already covered the main tools for monitoring CPU and memory consumption in Chapter 2, and this chapter takes the basic concepts from Chapter 2 and expands on some Oracle-centric techniques that you can use for observing the interaction between Oracle and the UNIX OS.

An important consideration when using Oracle with UNIX is that the interaction between UNIX and Oracle is automatic and largely beyond the control of the Oracle DBA.  For example, UNIX is designed to allow any Oracle process to use any available CPU, and the assignment of UNIX processes to CPU’s is automatic and uncontrollable.  Table 6-1 shows some of the few options available to the Oracle DBA when  tuning the interface between Oracle and UNIX.

UNIX Component
DBA consumption control
RAM
init.ora parameters

Alter session commands

Alter system commands

Parameters in the protocol.ora file

Table 1: UNIX components and Oracle adjustments

Let’s begin with an overview of the Oracle background processes and see how we can measure the interaction of the background processes and Oracle server resources.

The Oracle background processes in UNIX

As Oracle has evolved, the number and complexity of the background processes has increased in complexity.  For our discussion, we have separated the Oracle background processes into the main background process and the processes that are optional. For our discussion we will limit our discussion to those background processes that are the most important for understanding the interaction between Oracle and UNIX.

The main Oracle background processes

However, we always see the following background processes, regardless of the version of Oracle of the OS environment (Figure 6-1)

Please re-draw fig6_1.gif

Figure 1: The main Oracle background processes

The System Monitor background process (SMON)

The SMON background process performs all system monitoring functions on the Oracle database. The SMON process performs a “warm start” each time that Oracle is re-started, ensuring that any in-flight transaction at the time of the last shutdown are recovered. For example, if Oracle crashed hard with a power failure, the SMON process is attached at startup time, and detects any uncompleted work, using the rollback segments to recover the transactions. In addition, SMON performs periodic cleanup of temporary segments that are no longer needed, and also perform tablespace operations, coalescing contiguous free extents into larger extents.

In UNIX, the SMON process will always be executing, and you can tell when SMON is performing a coalesce operation because the CPU usage for the process will increase.

The Process Monitor background process (PMON)

The PMON background process is responsible for interfacing with the rollback segments to rollback any abnormally terminated transactions.  Just like SMON, the PMON process is always executing in UNIX, but it remain largely idle except when recovering aborted transactions.  A later section will show you how to dump the PMON process and see details about it’s interaction with UNIX.

The Log Writer background process (LGWR)

The LGWR background process is the first in a series on redo log processes that writes the contents of the redo log buffer to the online redo log files. It writes to the online redo log files in batches and the entries always contain the most up-to-date status of the database.  In UNIX, the LGWR process will perform the writes from the RAM log_buffer to the online redo log file even if your database is not in ARCHIVELOG mode.

The Database Writer background process (DBWR)

The DBWR background process is responsible for managing the interaction between the Oracle RAM data buffers (and the dictionary cache) and the physical disks. The DBWR process performs batch writes of the changed blocks back to the data files.

In UNIX, the DBWR process is asynchronous.  This means that a database write does not always result in an immediate physical I/O by the DBWR.  Rather, the DBWR process may wait until a set of “dirty” block have accumulated in the data buffers, and then write out the entire set of blocks in a single operation.

When using a sophisticated back-end storage system such as EMC, the nature of database writes becomes even more complex.  Often, an EMC box will defer write to the physical disks in order to optimizer I/O throughput.  To do this, the EMC box will send an immediate acknowledgement back to the DBWR process that the I/O has been completed, when in reality the data block resides in RAM storage in the EMC disk cache.  This complex interaction often leads to finger-pointing between EMC and Oracle whenever a bug causes a disk write to fail.

The archiver background process(ARCH)

The ARCH background process is invoked when your database is running in ARCHIVELOG mode.  If you are archiving your redo logs, the redo logs are touched by several background processes.  First, the LGWR process copies the log_buffer contents to the online redo log files, and then the ARCH process copies the online redo log files to the archived redo log filesystem on UNIX.  The ARCH process commonly offloads the most recent online redo log file whenever a log switch operation occurs in Oracle.  You can observe a log switch in the Oracle alert log, and the online redo logs should be sized such that you do not experience a log switch more than once every 15 minutes.

In UNIX, the ARCH process is I/O intensive and you can use top, glance or watch to observe the ARCH process writing the data blocks to the archived redo log file.

Parallel Query background process (Pnnn)

The Oracle parallel query slave processes are used whenever Oracle invokes a parallel full-table scan.  Oracle partitions the target table and then fires-off a UNIX process for each table partition.  When not in use, the parallel query processes disappear from UNIX.  Since most Oracle database that perform parallel query are doing full-table scans against very large tables, the Oracle DBA can watch the parallel query slaves appear in the UNIX environment.  For example, if you are doing a query with parallel degree five, you can use the UNIX ps command to watch Oracle direct UNIX to create six parallel query slaves, with names like ora_p000_prodsid through ora_p005_prodsid. 

The extra parallel query process is the parallel query coordinator, and this UNIX process will remain alive until all of the factotum process have completed their sub-table scans.  Once all of the data has been retrieved, you can sometime observe Oracle invoke a disk sort in the TEMP tablespace.  Whenever a parallel large-table full-table scan contains an order by or a group by on a large result set, the Oracle parallel query coordinator, will pass the unsorted result set to Oracle, where the Oracle DBA can watch the creation of temporary segments in the TEMP tablespace to sort the result set.