Instructor's Notes

(If applicable)

• Lesson 3: Oracle 9i Architecture Part 2
• In this chapter we will take a closer look at the processes associated with an instance. This will allow you to have a deeper understanding of how the instance controls the database. Read on…

Instructor's Notes

(If applicable)

• In order to understand the concepts taught in this chapter, you will need to know the architecture concepts taught in previous lesson, plus SQL, SQL*Plus (an alternative query tool will do) and how to query the Oracle data dictionary.

Instructor's Notes

(If applicable)

• Before we look at the individual processes that make Oracle work, take a moment to review the “big picture”. (We first presented this in the lesson Architecture Part I: Operating System Files)
• Remember that the background processes (ovals in the diagram) and the memory areas (boxes) together make up the Oracle instance. The files make up the Oracle database.
• OK, let’s turn our attention to the background processes…

Instructor's Notes

(If applicable)

• There are two main categories of processes: background and server.

1. Oracle uses background processes to perform database operations such as writing dirty (updated) blocks in the buffer cache to database files. (You’ll learn more about these services as you progress through this lesson.) The background processes are part of the instance and are started at instance startup. Some, like DBWn, PMON, SMON, CKPT and LGWR are required. Others, such as ARCn, are optional.
2. Server processes act directly on behalf of the clients. For example, they accept, parse and execute the queries executed by users. During execution, they read blocks from the data files. Two types of server processes are available:

• Dedicated server processes – These are called dedicated because they are dedicated to a single user for the life of the user’s session.
• Shared Server Processes – These processes are shared among many clients. A dispatcher process is started to control access to the shared servers. The DBA must take steps to configure the “Shared Server” environment.


• Notes for this slide continue on the next page…

Instructor's Notes

(If applicable)

• Supplemental Notes
• On a Windows server, the processes are really just threads in one large process. If you look at the Processes tab of the Task Manager, you will see just one process, “ORACLE.EXE”. You could use the TLIST utility supplied with the Windows Resource Toolkit to see the separate threads within the process.
• In Unix (or Linux), you can see the processes running with the process status command:
• [oracle@BigBoy oracle]$ ps -aef | grep ora_

Instructor's Notes

(If applicable)

• The query above against V$BGPROCESS shows the active background processes on my server.
• Let’s take at look at each of the major background processes in turn…

Instructor's Notes

(If applicable)

• The DBWn process writes dirty (i.e. updated) data blocks from the data block buffer cache to database files. DBWn will write out the Oracle blocks at various times - when the instance is shutdown, when the database buffer pool does not have any free blocks to load new database blocks and when a checkpoint occurs. A checkpoint signals DBWn to write all modified blocks to the datafiles, and updates all datafiles and control files to indicate that the checkpoint has been taken.
• DBWn uses a Least Recently Used algorithm to help keep recently accessed blocks remain in the buffer cache. That is, the least recently used block will be flushed from the buffer cache first, leaving recently accessed blocks in the cache. Oracle also supports a separate RECYCLYE cache, which is an area that does not adhere to this; i.e. recently accessed blocks in the recycle pool can be flushed right away.
• To eliminate waiting* when writing large numbers of dirty blocks to multiple datafiles, more than one DBWn process can be started via the DB_WRITER_PROCESSES parameter. In this case, the processes will be DBW0, DBW1, DBW2, etc., not just DBW0 .
• * Waiting can occur if a redo log file is needed before the checkpoint operation completes. If starting multiple DBWn processes, be sure to adjust the DB_BLOCK_LRU_LATCHES parameter. See the Tom Kyte text, expert one-to-one Oracle, page 93, for a complete description.

Instructor's Notes

(If applicable)

• The log writer background process writes the log entries in the redo log buffer (memory) to the online redo log files. Every change made in the database is logged - i.e. a log entry is written to the redo log buffer.
• LGWR will write log entries to a redo log file when:
• A transaction commits
• The redo buffer is 1/3 full
• Every 3 seconds
• The redo log files are sequential files; writing to these files is a relatively inexpensive operation, when compared to the writing of the dirty data blocks from the buffer cache to the database files.
• Since the redo log files contain all changes made to the database, they can be used to recover a database after either an instance failure or a media failure. Recovery from an instance failure is automatic. Recovery from a media failure requires restoring lost media from previous backups, and then applying changes since the backup from the redo logs (such recovery is actually somewhat more complex – we will cover this topic in detail later in the Introduction to RMAN lesson).

Instructor's Notes

(If applicable)

• The system monitor process has several functions:
• SMON performs instance recovery automatically when the instance or server crashes. This process includes applying all changes from the active log to the datafiles (changes that where not already written to the datafiles), then rolling back any changes that were not committed before the instance crashed.
• SMON deletes unnecessary temporary segments which are created when disk space is required to support a sort operation. These additional temporary sort area segments may be required for many reasons - for example when an ORDER BY clause is specified for a large result set or the CREATE INDEX command is executed against a large table.
• SMON will also periodically and automatically coalesce contiguous free extents into a larger single extent for dictionary-managed files. (You will learn more about dictionary-managed files in the Tablespaces lesson later.) This is beneficial because the larger free extent is more likely to be reused by a subsequent request for a free extent. Note that this only occurs for dictionary-managed tablespaces where the PCTINCREASE setting is > 0.
• Finally, SMON will also shrink rollback segments to the size specified in the OPTIMAL parameter, if rollback segments are used (9i provides an Automatic UNDO Management Feature). See the Managing UNDO lesson for more information.

Instructor's Notes

(If applicable)

• The process monitor background process will clean up after a user process failure. An example of user process failure is when a user cancels a command, perhaps due to lock contention. For example. a user could cancel a process (command) with a CTRL+C from a SQL*Plus session. Note that this does not necessarily mean the session failed. PMON will rollback any uncommitted work, and release any locks, latches and memory. This will be done automatically as the PMON process wakes up periodically.
• PMON will also monitor other Oracle processes and, if feasible, restart a failed process. E.g. PMON might restart a failed shared server process. However, if the LGWR process failed, this would be seen as a critical failure, and the instance would crash.
• Finally, PMON also registers the instance with the SQL*Net listener running on the server (if available). It would send the listener the instance name so that the listener could route future connection attempts to the instance.

Instructor's Notes

(If applicable)

• The CKPT process initiates the process of checkpointing by signaling DBWn to start writing modified blocks in the buffer cache to disk. Checkpoints are initiated when a log switch occurs. It can also be forced with the ALTER SYSTEM CHECKPOINT command.
• When the checkpoint is complete, CKPT then updates the control file and the headers of the data files to record that a checkpoint has occurred.

Instructor's Notes

(If applicable)

• The archiver process (ARCn) copies an online redo log to one or more archive locations. This occurs when the online log is inactive - i.e. another online log is currently in use.
• ARCn is an optional process; however, if the database is in ARCHIVELOG mode – it should be if you want to be able to recover – the database will hang if it is not started. This is because in this mode Oracle will not overwrite (destroy) a log if it has not been archived. Without ARCn running in the background, there is no process to do the archiving.
• Start the archiver with the LOG_ARCHIVE_START=TRUE parameter in your database parameter file.

Instructor's Notes

(If applicable)

• Oracle8i introduced archive log duplexing via the LOG_ARCHIVE_DUPLEX_DEST or LOG_ARCHIVE_DEST_N parameters. Up to 10 locations can be specified.
• The archive location can also be a standby database, which can then optionally automatically apply the logs. See the Oracle9i Data Guard Concepts and Administration manual for more information on the Data Guard option and standby databases.
• Because so many destinations can be specified, which translates into work, multiple ARCn processes can be started. This is a tuning issue. For more information refer to the Oracle9i Database Reference (see the LOG_ARCHIVE_MAX_PROCESSES parameter) and Chapter 22 of the Oracle9i Database Performance Tuning Guide and Reference.

Instructor's Notes

(If applicable)

• Additional processes may be running on your server. These include:
• Dnnn – One or more dispatcher processes must be started if the Shared Server mode (formerly called Multithreaded Server or MTS) is desired. Shared servers are useful in environments with thousands of users, many with high think (idle) time. The dispatcher process places a client request on a request queue; the next available shared server process processes the request.
• Snnn – One or more shared server processes. This process, shared by more than one user, will act on behalf of a user. For example, a shared server process, like a dedicated server process, will process SQL statements (parse, execute and fetch) and perform physical and logical I/O to access the data a user requests.
• RECO – This process cleans up after a failed distributed transaction. Distributed transactions utilize two-phase commit protocol. See the Oracle9i Concepts Guide for more information.

Instructor's Notes

(If applicable)

• Jnnn – The Job Queue Processes run batch jobs scheduled with the DBMS_JOB supplied package. See the Oracle9i Supplied PL/SQL Packages and Types Reference for more information.
• CJQn – This process is not documented in the Oracle9i Concepts Release 2 manual, but its name implies it coordinates the Jnnn processes.
• QMNn – This process monitors the message queues used by the Advanced Queuing feature. See the Oracle9i Application Developer's Guide - Advanced Queuing manual for more information.

Instructor's Notes

(If applicable)

• Architecture: Processes Workshop
• Hands-on Exercises
  1. Connect to your instance with your DBA-level user.
  2. What background processes are active on your server? If you are using a UNIX (or Linux) server, use both an operating system command and a SQL statement to list active processes.
  3. What is the maximum number of archive processes that can be started on your instance?
• Written Exercises
  1. What process will write dirty blocks back to a database file when a checkpoint occurs?
  2. What process will parse and execute SQL statements on behalf of a user?
  3. What process will, when a COMMIT is issued, write the redo log buffer contents to the redo log file?