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
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
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.
Add additional RAM
Add additional CPU’s to server
init.ora parameters i.e. db_file_multiblock_read_count
Parameters in the sqlnet.ora file
in the protocol.ora file
Table 1: UNIX components and Oracle
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