Oracle8 Concepts Release 8.0 A58227-01 |
Library | Product | Contents | Index

# 7
Process Structure

If the good people, in their wisdom, shall see fit to keep me in the background, I have been too familiar with disappointments to be very much chagrined.

Abraham Lincoln, Address at New Salem (1832)

This chapter discusses the processes in an Oracle database system and the different configurations available for an Oracle system. It includes:

- Introduction to Processes
- Single-Process Oracle
- Multiple-Process Oracle
- Variations in Oracle Configuration
- Examples of How Oracle Works
- The Program Interface

## Introduction to Processes

All connected Oracle users must execute two modules of code to access an Oracle database instance:

application or Oracle tool | A database user executes a database application (such as a precompiler program) or an Oracle tool (such as an Oracle Forms application or SQL*Plus), which issues SQL statements to an Oracle database.
Oracle server code | Each user has some Oracle server code executing on his or her behalf, which interprets and processes the application's SQL statements.

These code modules are executed by processes. A process is a "thread of control" or a mechanism in an operating system that can execute a series of steps. (Some operating systems use the terms job or task.) A process normally has its own private memory area in which it runs.

The process structure varies for different Oracle configurations, depending on the operating system and the choice of Oracle options.

## Single-Process Oracle

Single-process Oracle (also called single-user Oracle) is a database system in which one process executes all parts of the Oracle server code and the single user's application program. Different processes do not separate the execution of the Oracle instance from the client application program.

Figure 7-1 shows a single-process Oracle instance. The single process executes all code associated with the database application and Oracle.

Only one user can access an Oracle instance in a single-process environment; multiple users cannot access the database concurrently. For example, Oracle running under the MS-DOS operating system on a PC can be accessed only by a single user because MS-DOS is not capable of running multiple processes.

This configuration is not as common as multiple-process Oracle, because it doesn not take advantage of the distributed processing normally associated with database operations.

#### Figure 7-1 A Single-Process Oracle Instance

## Multiple-Process Oracle

Multiple-process Oracle (also called multiuser Oracle) uses several processes to execute different parts of Oracle code, and a separate process for each connected user. Each process in a multiple-process Oracle instance performs a specific job. By dividing the work of Oracle and database applications into several processes, multiple users and applications can connect to a single database instance simultaneously while the system maintains excellent performance.

Most database systems are multiuser, because one of the primary benefits of a database is managing data needed by multiple users at the same time.

Figure 7-2 illustrates a multiple-process Oracle instance. Each connected user has a separate user process, and several background processes execute Oracle. This figure might represent multiple concurrent users running an application on the same machine as Oracle; this particular configuration usually runs on a mainframe or minicomputer.

#### Figure 7-2 A Multiple-Process Oracle Instance

In a multiple-process system, processes can be categorized into two groups: user processes and Oracle processes. User processes execute the application or Oracle tool code, and Oracle processes execute the Oracle server code.

### User Processes

When a user runs an application program (such as a Pro*C program) or an Oracle tool (such as Oracle Enterprise Manager or SQL*Plus) Oracle creates a user process to run the user's application.

#### Connections and Sessions

The terms "connection" and "session" are closely related to the term "user process", but are very different in meaning.

A connection is a communication pathway between a user process and an Oracle instance. A communication pathway is established using available interprocess communication mechanisms (on a computer that executes both the user process and Oracle) or network software (when different computers execute the database application and Oracle, and communicate via a network).

A session is a specific connection of a user to an Oracle instance via a user process. For example, when a user starts SQL*Plus, the user must provide a valid username and password and then a session is established for that user. A session lasts from the time the user connects until the time the user disconnects or exits the database application.

Multiple sessions can be created and exist concurrently for a single Oracle user using the same username. For example, a user with the username/password of SCOTT/TIGER can connect to the same Oracle instance several times.

In configurations without the multithreaded server, Oracle creates a server process on behalf of each user session; however, with the multithreaded server, many user sessions can share a single server process. See "The Multithreaded Server" on page 7-20 for more information.

### Oracle Processes

In multiple-process systems, two types of processes control Oracle: server processes and background processes.

Oracle creates server processes to handle the requests of user processes connected to the instance. In some situations when the application and Oracle operate on the same machine, it is possible to combine the user process and corresponding server process into a single process to reduce system overhead. However, when the application and Oracle operate on different machines, a user process communicates with Oracle via a separate server process. See "Variations in Oracle Configuration" on page 7-16 for more information.

#### Server Processes

Server processes (or the server portion of combined user/server processes) created on behalf of each user's application may perform one or more of the following:

- Parse and execute SQL statements issued via the application.
- Read necessary data blocks from datafiles on disk into the shared database buffers of the SGA, if the blocks are not already present in the SGA.
- Return results in such a way that the application can process the information.

#### Background Processes

To maximize performance and accommodate many users, a multiprocess Oracle system uses some additional Oracle processes called background processes.

On many operating systems, background processes are created automatically when an instance is started. On other operating systems, the server processes are created as a part of the Oracle installation.

Additional Information: See your Oracle operating-system-specific documentation for details on how these processes are created.

An Oracle instance may have many background processes; not all are always present. The background processes in an Oracle instance include the following:

- Database Writer (DBW0 or DBWn)
- Log Writer (LGWR)
- Checkpoint (CKPT)
- System Monitor (SMON)
- Process Monitor (PMON)
- Archiver (ARCH)
- Recoverer (RECO)
- Lock (LCKn)
- Job Queue (SNPn)
- Queue Monitor (QMNn)
- Dispatcher (Dnnn)
- Server (Snnn)

Figure 7-3 illustrates how each background process interacts with the different parts of an Oracle database, and the rest of this section describes each process.

Additional Information: The Oracle Parallel Server is not illustrated in Figure 7-3; see Oracle8 Parallel Server Concepts and Administration for more information.

#### Figure 7-3 The Background Processes of a Multiple-Process Oracle Instance

##### Database Writer (DBWn)

The database writer process (DBWn) writes the contents of buffers to datafiles. The DBWn processes are responsible for writing modified (dirty) buffers in the database buffer cache to disk. (See "The Database Buffer Cache" on page 6-3.) Although one database writer process (DBW0) is adequate for most systems, you can configure additional processes (DBW1 through DBW9) to improve write performance if your system modifies data heavily. These additional DBWn processes are not useful on uniprocessor systems.

When a buffer in the database buffer cache is modified, it is marked "dirty". The primary job of the DBWn process is to keep the buffer cache "clean" by writing dirty buffers to disk. As buffers are dirtied by user processes, the number of free buffers diminishes. If the number of free buffers drops too low, user processes that must read blocks from disk into the cache are not able to find free buffers. DBWn manages the buffer cache so that user processes can always find free buffers.

The DBWn process writes the least recently used (LRU) buffers to disk. By writing the least recently used dirty buffers to disk, DBWn improves the performance of finding free buffers while keeping recently used buffers resident in memory. For example, blocks that are part of frequently accessed small tables or indexes are kept in the cache so that they do not need to be read in again from disk. The LRU algorithm keeps more frequently accessed blocks in the buffer cache so that when a buffer is written to disk, it is unlikely to contain data that may be useful soon.

The initialization parameter DB_WRITER_PROCESSES specifies the number of DBWn processes. If your system uses multiple DBWn processes, you should adjust the value of the DB_BLOCK_LRU_LATCHES parameter so that each DBWn process has the same number of latches (LRU buffer lists).

Additional Information: See Oracle8 Tuning for advice on setting DB_WRITER_PROCESSES and DB_BLOCK_LRU_LATCHES.

The DBWn process writes dirty buffers to disk under the following conditions:

- When a server process cannot find a clean reusable buffer after scanning a threshold number of buffers, it signals DBWn to write. DBWn writes dirty buffers to disk with a single multiblock write. (The number of blocks written in a multiblock write varies by operating system.)
- When a checkpoint occurs, the log writer process (LGWR) signals DBWn and DBWn writes all buffers that need to be written for the checkpoint to complete. Checkpoints occur at each log switch, when LGWR stops writing to one online redo log file and starts writing to another, and at regular intervals specified by the initialization parameters LOG_CHECKPOINT_INTERVAL and LOG_CHECKPOINT_TIMEOUT.
- DBWn periodically writes buffers to advance the position in the redo thread (log) from which crash or instance recovery needs to begin (incremental checkpointing). This log position is determined by the oldest dirty buffer in the buffer cache. Incremental checkpointing occurs when the number of dirty buffers in the cache is greater than a threshold specified by the initialization parameter DB_BLOCK_MAX_DIRTY_TARGET. See "Incremental Checkpointing" on page 28-4 for more information.

In all cases, DBWn performs batched (multiblock) writes to improve efficiency.

On some platforms, the DBW0 process can have multiple I/O server processes. If one of these processes blocks during a write to one disk, the others can continue writing to other disks. These processes cannot be used on systems with multiple DBWn processes. The initialization parameter DBWR_IO_SLAVES controls the number of I/O server processes.

Additional Information: See your Oracle operating system-specific documentation for information about multiple I/O server processes on your platform. Also see Oracle8 Tuning for information about how to monitor and tune the performance of a single DBW0 process or multiple DBWn processes.

##### Log Writer Process (LGWR)

The log writer process (LGWR) is responsible for redo log buffer management - writing the redo log buffer to a redo log file on disk (see "The Redo Log Buffer" on page 6-6). LGWR writes all redo entries that have been copied into the buffer since the last time it wrote.

The redo log buffer is a circular buffer. When LGWR writes redo entries from the redo log buffer to a redo log file, server processes can then copy new entries over the entries in the redo log buffer that have been written to disk. LGWR normally writes fast enough to ensure that space is always available in the buffer for new entries, even when access to the redo log is heavy.

LGWR writes one contiguous portion of the buffer to disk. LGWR writes:

- a commit record when a user process commits a transaction
- redo log buffers
  - every three seconds
  - when the redo log buffer is one-third full
  - when a DBWn process writes modified buffers to disk, if necessary

    Note: Before DBWn can write a modified buffer, all redo records associated with the changes to the buffer must be written to disk (the write-ahead protocol). If DBWn finds that some redo records have not been written, it signals LGWR to write the redo records to disk and waits for LGWR to complete writing the redo log buffer before it can write out the data buffers.

LGWR writes synchronously to the active mirrored group of online redo log files. If one of the files in the group is damaged or unavailable, LGWR continues writing to other files in the group and logs an error in the LGWR trace file and in the system ALERT file (see "Trace Files and the ALERT File" on page 7-14). If all files in a group are damaged, or the group is unavailable because it has not been archived, LGWR cannot continue to function.

When a user issues a COMMIT statement, LGWR puts a commit record in the redo log buffer and writes it to disk immediately, along with the transaction's redo entries. The corresponding changes to data blocks are deferred until it is more efficient to write them. This is called a "fast commit" mechanism. The atomic write of the redo entry containing the transaction's commit record is the single event that determines the transaction has committed. Oracle returns a success code to the committing transaction, even though the data buffers have not yet been written to disk.

Note: Sometimes, if more buffer space is needed, LGWR writes redo log entries before a transaction is committed. These entries become permanent only if the transaction is later committed.

When a user commits a transaction, the transaction is assigned a system change number (SCN), which Oracle records along with the transaction's redo entries in the redo log. SCNs are recorded in the redo log so that recovery operations can be synchronized in Oracle Parallel Server configurations and distributed databases.

Additional Information: See Oracle8 Parallel Server Concepts and Administration and the Oracle8 Administrator's Guide for more information about SCNs and how they are used.

In times of high activity, LGWR may write to the online redo log file using group commits. For example, assume that a user commits a transaction - LGWR must write the transaction's redo entries to disk and as this happens, other users issue COMMIT statements. However, LGWR cannot write to the online redo log file to commit these transactions until it has completed its previous write operation. After the first transaction's entries are written to the online redo log file, the entire list of redo entries of waiting transactions (not yet committed) can be written to disk in one operation, requiring less I/O than would transaction entries handled individually. Therefore, Oracle minimizes disk I/O and maximizes performance of LGWR. If requests to commit continue at a high rate, then every write (by LGWR) from the redo log buffer may contain multiple commit records.

On some platforms, the LGWR process can have multiple I/O server processes. If one of these processes blocks during a write to one disk, the others can continue writing to other disks. The initialization parameter LGWR_IO_SLAVES controls the number of I/O server processes.

Additional Information: See your Oracle operating system-specific documentation for information about multiple I/O server processes on your platform. For information about how to monitor and tune the performance of LGWR, see Oracle8 Tuning.

##### Checkpoint Process (CKPT)

When a checkpoint occurs, Oracle must update the headers of all datafiles to record the details of the checkpoint. This is done by the CKPT process. The CKPT process does not write blocks to disk; DBWn always performs that work.

The statistic DBWR checkpoints displayed by the System_Statistics monitor in Oracle Enterprise Manager indicates the number of checkpoint requests completed.

Additional Information: See the Oracle8 Administrator's Guide for information about the effects of changing the checkpoint interval. See Oracle8 Parallel Server Concepts and Administration for information about CKPT in an Oracle Parallel Server.

##### System Monitor (SMON)

The system monitor process (SMON) performs instance recovery at instance start up. SMON is also responsible for cleaning up temporary segments that are no longer in use and for coalescing contiguous free extents to make larger blocks of free space available. SMON "wakes up" regularly to check whether it is needed. Other processes can call SMON if they detect a need for SMON to wake up.

In an Oracle Parallel Server environment, SMON also performs instance recovery for a failed CPU or instance.

Additional Information: See Oracle8 Parallel Server Concepts and Administration for more information about SMON in an Oracle Parallel Server.

##### Process Monitor (PMON)

The process monitor (PMON) performs process recovery when a user process fails. PMON is responsible for cleaning up the database buffer cache and freeing resources that the user process was using. For example, it resets the status of the active transaction table, releases locks, and removes the process ID from the list of active processes.

PMON also periodically checks the status of dispatcher and server processes, and restarts any that have died (but not any that Oracle has terminated intentionally).

Like SMON, PMON "wakes up" regularly to check whether it is needed, and can be called if another process detects the need for it.

##### Recoverer Process (RECO)

The recoverer process (RECO) is a background process used with the distributed database configuration that automatically resolves failures involving distributed transactions.

The RECO process of a node automatically connects to other databases involved in an in-doubt distributed transaction. When the RECO process reestablishes a connection between involved database servers, it automatically resolves all in-doubt transactions, removing from each database's pending transaction table any rows that correspond to the resolved in-doubt transactions.

If the RECO process fails to connect with a remote server, RECO automatically tries to connect again after a timed interval. However, RECO waits an increasing amount of time (growing exponentially) before it attempts another connection.

Additional Information: For more information about distributed transaction recovery, see Oracle8 Distributed Database Systems.

The RECO process is present only if the instance permits distributed transactions and if the DISTRIBUTED_TRANSACTIONS parameter is greater than zero. If this initialization parameter is zero, RECO is not created during instance startup.

##### Archiver Process (ARCH)

The archiver process (ARCH) copies online redo log files to a designated storage device once they become full. ARCH is present only when the redo log is used in ARCHIVELOG mode and automatic archiving is enabled.

Additional Information: For information on archiving the online redo log, see "The Redo Log" on page 28-7 and Oracle8 Backup and Recovery Guide. See your Oracle operating system-specific documentation for details of using the ARCH process.

##### Lock Processes (LCKn)

With the Parallel Server option, up to ten lock processes (LCK0, . . ., LCK9) provide interinstance locking. A single LCK process (LCK0) is sufficient for most Oracle Parallel Server systems.

Additional Information: See Oracle8 Parallel Server Concepts and Administration for more information about this background process.

##### Job Queue Processes (SNPn)

With the distributed database configuration, up to thirty-six job queue processes (SNP0, ..., SNP9, SNPA, ..., SNPZ) can automatically refresh table snapshots. These processes wake up periodically and refresh any snapshots that are scheduled to be automatically refreshed. If more than one job queue process is used, the processes share the task of refreshing snapshots.

Unlike other Oracle background processes, failure of an SNPn process does not cause the instance to fail. If an SNPn process fails, Oracle restarts it.

These processes also execute job requests created by the DBMS_JOB package and propagate queued messages to queues on other databases (see "Oracle Advanced Queuing" on page 16-3).

Additional Information: See Oracle8 Administrator's Guide for more information about this background process and job queues.

##### Queue Monitor Processes (QMNn)

The queue monitor process is an optional background process for Oracle Advanced Queuing (Oracle AQ) which monitors the message queues. You can configure up to ten queue monitor processes. These processes, like the SNPn processes, are different from other Oracle background processes in that process failure does not cause the instance to fail.

See "Oracle Advanced Queuing" on page 16-3 for more information on message queues and the queue monitor process.

##### Dispatcher Processes (Dnnn)

The dispatcher processes support multithreaded configuration by allowing user processes to share a limited number of server processes. (See "The Multithreaded Server" on page 7-20.) With the multithreaded server, fewer shared server processes are required for the same number of users; therefore, the multithreaded server can support a greater number of users, particularly in client/server environments where the client application and server operate on different machines.

You can create multiple dispatcher processes for a single database instance; at least one dispatcher must be created for each network protocol used with Oracle. The database administrator should start an optimal number of dispatcher processes depending on the operating system limitation on the number of connections per process, and can add and remove dispatcher processes while the instance runs.

Note: Each user process that connects to a dispatcher must do so through Net8 or SQL*Net Version 2, even if both processes are running on the same machine.

In a multithreaded server configuration, a network listener process waits for connection requests from client applications, and routes each to a dispatcher process. If it cannot connect a client application to a dispatcher, the listener process starts a dedicated server process, and connects the client application to the dedicated server. The listener process is not part of an Oracle instance; rather, it is part of the networking processes that work with Oracle.

Additional Information: See "The Multithreaded Server" on page 7-20 and the Oracle Net8 Administrator's Guide for more information about the network listener.

##### Shared Server Processes (Snnn)

Each shared server process serves multiple client requests in the multithreaded server configuration. For more information, see "Shared Server Processes" on page 7-23.

### Trace Files and the ALERT File

Each server and background process can write to an associated trace file. When a process detects an internal error, it dumps information about the error to its trace file. If an internal error occurs and information is written to a trace file, the administrator should contact Oracle support.

Additional Information: See Oracle8 Error Messages for information about error messages.

All filenames of trace files associated with a background process contain the name of the process that generated the trace file. The one exception to this is trace files generated by job queue processes (SNPn).

Additional information in trace files can provide guidance for tuning applications or an instance. Background processes always write this information to a trace file when appropriate. However, server processes write tuning information to a trace file only if the initialization parameter SQL_TRACE is set to TRUE for the instance or session. (Information about internal errors is always written to trace files.)

Each session can enable or disable trace logging on behalf of the associated server process by using the SQL command ALTER SESSION with the SQL_TRACE parameter. For example, the following statement enables writing to a trace file for the session:

    ALTER SESSION SET SQL_TRACE = TRUE;

Each database also has an ALERT file. The ALERT file of a database is a chronological log of messages and errors, including

- all internal errors (ORA-600), block corruption errors (ORA-1578), and deadlock errors (ORA-60) that occur
- administrative operations, such as the SQL statements CREATE/ALTER/DROP DATABASE/TABLESPACE/ROLLBACK SEGMENT and the Oracle Enterprise Manager or Server Manager statements STARTUP, SHUTDOWN, ARCHIVE LOG, and RECOVER
- several messages and errors relating to the functions of shared server and dispatcher processes
- errors during the automatic refresh of a snapshot

Oracle uses the ALERT file to keep a record of these events as an alternative to displaying the information on an operator's console. (Many systems also display this information on the console.) If an administrative operation is successful, a message is written in the ALERT file as "completed" along with a timestamp.

## Variations in Oracle Configuration

In a multiple-process Oracle instance, the code for connected users can be configured in one of three ways:

single-task Oracle | For each user, both the database application and the Oracle server code are combined in a single user process.
dedicated server (two-task Oracle) | For each user, the database application is run by a different process (a user process) than the one that executes the Oracle server code (a dedicated server process).
multithreaded server | The database application is run by a different process (a user process) than the one that executes the Oracle server code; each server process that executes Oracle server code (a shared server process) can serve multiple user processes.

The following sections describe each variation in more detail.

Additional Information: Some operating systems offer a choice of configurations; see your Oracle operating-system-specific documentation for more details on your options.

### Single-Task Configuration

Figure 7-4 illustrates the single-task Oracle configuration. In this configuration, the database application and the Oracle server code all run in the same process, called a user process.

#### Figure 7-4 Oracle Using Combined User/Server Processes

This configuration of Oracle is feasible only in operating systems that can maintain a separation between the database application and the Oracle code in a single process (such as on the VAX VMS operating system). This separation is required for data integrity and privacy. Some operating systems, such as UNIX, cannot provide this separation and thus must have separate processes run application code and server code.

Note: The program interface is responsible for the separation and protection of the Oracle server code and is responsible for passing data between the database application and the Oracle user program. See "The Program Interface" on page 7-27.

Only one Oracle connection is allowed at any time by a process using the single-task configuration. However, in a user-written program it is possible to maintain this type of connection while concurrently connecting to Oracle using a network (Net8) interface.

### Dedicated Server (Two-Task) Configuration

Figure 7-5 illustrates Oracle running on two computers using the dedicated server architecture. In this configuration, a user process executes the database application on one machine and a server process executes the associated Oracle server on another machine.

#### Figure 7-5 Oracle Using Dedicated Server Processes

The user and server processes are separate, distinct processes. The separate server process created on behalf of each user process is called a dedicated server process (or shadow process) because this server process acts only on behalf of the associated user process.

This configuration maintains a one-to-one ratio between the number of user processes and server processes. Even when the user is not actively making a database request, the dedicated server process remains (though it is inactive and may be paged out on some operating systems).

Figure 7-5 shows user and server processes running on separate computers connected across a network. However, the dedicated server architechture is also used if the same computer executes both the client application and the Oracle server code but the host operating system could not maintain the separation of the two programs if they were run in a single process. (UNIX is a common example of such an operating system.)

In the dedicated server configuration, the user and server processes communicate using different mechanisms:

- If the system is configured so that the user process and the dedicated server process run on the same computer, the program interface uses the host operating system's interprocess communication mechanism to perform its job.
- If the user process and the dedicated server process run on different computers, the program interface provides the communication mechanisms (such as the network software and Net8) between the programs.

  Additional Information: These communications links are operating system and installation dependent; see your Oracle operating-system-specific documentation and the Net8 documentation for more information.

Dedicated server architecture can sometimes result in inefficiency. Consider an order entry system with dedicated server processes. A customer places an order as a clerk enters the order into the database. For most of the transaction, the clerk is talking to the customer while the server process dedicated to the clerk's user process remains idle. The server process is not needed during most of the transaction, and the system is slower for other clerks entering orders. For applications such as this, the multithreaded server architecture may be preferable.

See "An Example of Oracle Using Dedicated Server Processes" on page 7-25 for a detailed illustration of the use of dedicated servers.

### The Multithreaded Server

The multithreaded server configuration allows many user processes to share very few server processes. The user processes connect to a dispatcher background process, which routes client requests to the next available shared server process.

The advantage of the multithreaded server configuration is that system overhead is reduced, increasing the number of users that can be supported. A small number of shared server processes can perform the same amount of processing as many dedicated server processes, and the amount of memory required for each user is relatively small.

A number of different processes are needed in a multithreaded server system:

- a network listener process that connects the user processes to dispatchers or dedicated servers (the listener process is part of Net8, not Oracle).
- one or more dispatcher processes
- one or more shared server processes

The multithreaded server requires Net8 or SQL*Net Version 2.

Note: To use shared servers, a user process must connect through Net8 or SQL*Net Version 2, even if the process runs on the same machine as the Oracle instance.

When an instance starts, the network listener process opens and establishes a communication pathway through which users connect to Oracle. Then, each dispatcher process gives the listener process an address at which the dispatcher listens for connection requests. At least one dispatcher process must be configured and started for each network protocol that the database clients will use.

When a user process makes a connection request, the listener examines the request and determines whether the user process can use a shared server process. If so, the listener returns the address of the dispatcher process that has the lightest load and the user process connects to the dispatcher directly.

Some user processes cannot communicate with the dispatcher (such as those that connect using pre-Version 2 SQL*Net) so the network listener process cannot connect them to a dispatcher. In this case, or if the user process requests a dedicated server (see "Restricted Operations of the Multithreaded Server" on page 7-24), the listener creates a dedicated server and establishes an appropriate connection.

Additional Information: See the Oracle Net8 Administrator's Guide for more information about the network listener.

#### Dispatcher Request and Response Queues

A request from a user is a single program interface call that is part of the user's SQL statement. When a user makes a call, its dispatcher places the request on the request queue, where it is picked up by the next available shared server process.

The request queue is in the SGA and is common to all dispatcher processes of an instance. The shared server processes check the common request queue for new requests, picking up new requests on a first-in-first-out basis. One shared server process picks up one request in the queue and makes all necessary calls to the database to complete that request.

When the server completes the request, it places the response on the calling dispatcher's response queue. Each dispatcher has its own response queue in the SGA. The dispatcher then returns the completed request to the appropriate user process.

For example, in an order entry system each clerk's user process connects to a dispatcher and each request made by the clerk is sent to that dispatcher, which places the request in the request queue. The next available shared server process picks up the request, services it, and puts the response in the response queue. When a clerk's request is completed, the clerk remains connected to the dispatcher but the shared server process that processed the request is released and available for other requests. While one clerk is talking to a customer, another clerk can use the same shared server process.

Figure 7-6 illustrates how user processes communicate with the dispatcher across the program interface and how the dispatcher communicates users' requests to shared server processes.

#### Figure 7-6 The Multithreaded Server Configuration and Shared Server Processes

#### Shared Server Processes

Shared server processes and dedicated server processes provide the same functionality, except that shared server processes are not associated with a specific user process. Instead, a shared server process serves any client request in the multithreaded server configuration.

The PGA of a shared server process does not contain user-related data (which needs to be accessible to all shared server processes). The PGA of a shared server process contains only stack space and process-specific variables. "Program Global Areas (PGA)" on page 6-13 provides more information about the content of a PGA in different types of instance configurations.

All session-related information is contained in the SGA. Each shared server process needs to be able to access all sessions' data spaces so that any server can handle requests from any session. Space is allocated in the SGA for each session's data space. You can limit the amount of space that a session can allocate by setting the resource limit PRIVATE_SGA to the desired amount of space in the user's profile. See Chapter 25, "Controlling Database Access" for more information about resource limits and profiles.

Oracle dynamically adjusts the number of shared server processes based on the length of the request queue. The number of shared server processes that can be created ranges between the values of the initialization parameters MTS_SERVERS and MTS_MAX_SERVERS.

#### Artificial Deadlocks

With a limited number of shared server processes, the possibility of an "artificial" deadlock can arise. An artificial deadlock can occur in the following situation:

1. One user acquires an exclusive lock on a resource by issuing a SELECT statement with the FOR UPDATE clause or a LOCK TABLE statement.
2. The shared server process that processes the locking request is released once the statement completes.
3. Other users attempt to access the locked resource. Each shared server process is bound to the user process it is serving until the necessary locked resource becomes available. Eventually, all shared servers may be bound to user processes waiting for locked resources.
4. The original user attempts to submit a new request (such as a COMMIT or ROLLBACK statement) to release the previously acquired lock, but cannot because all shared server processes are currently being used.

When Oracle detects an artificial deadlock, new shared server processes are automatically created as needed until the original user submits a request that releases the locked resources causing the artificial deadlocks. If the maximum number of shared server processes (as specified by the MTS_MAX_SERVERS parameter) have been started, the database administrator must manually resolve the deadlock by disconnecting a user. This releases a shared server process, resolving the artificial deadlock.

If artificial deadlocks occur too frequently on your system, you should increase the value of MTS_MAX_SERVERS.

#### Restricted Operations of the Multithreaded Server

Certain administrative activities cannot be performed while connected to a dispatcher process, including shutting down or starting an instance and media recovery. An error message is issued if you attempt to perform these activities while connected to a dispatcher process.

These activities are typically performed when connected with administrator privileges. When you want to connect with administrator privileges in a system configured with multithreaded servers, you must state in your connect string that you want to use a dedicated server process (SRVR=DEDICATED) instead of a dispatcher process.

Additional Information: See your Oracle operating system-specific documentation or the Oracle Net8 Administrator's Guide for the proper connect string syntax.

See "An Example of Oracle Using the Multithreaded Server" on page 7-26 for a detailed illustration of the use of the multithreaded server configuration.

## Examples of How Oracle Works

Now that the memory structures, processes, and varying configurations of an Oracle database system have been discussed, it is helpful to see how all the parts work together. The following sections demonstrate and contrast the two-task and multithreaded server Oracle configurations.

### An Example of Oracle Using Dedicated Server Processes

The following example is a simple illustration of the dedicated server architecture. These steps show only the most basic level of operations that Oracle performs.

1. A database server machine is currently running Oracle using multiple background processes.
2. A user process on a client workstation runs a database application such as SQL*Plus. The client application attempts to establish a connection to the server using a Net8 driver.
3. The database server is currently running the proper Net8 driver. The network listener process on the database server detects the connection request from the client database application and creates a dedicated server process on the database server on behalf of the user process.
4. The user executes a single SQL statement. For example, the user inserts a row into a table.
5. The dedicated server process receives the statement. At this point, two paths can be followed to continue processing the SQL statement:
   - If the shared pool contains a shared SQL area for an identical SQL statement, the server process uses the existing shared SQL area to execute the client's SQL statement.
   - If the shared pool does not contain a shared SQL area for an identical SQL statement, a new shared SQL area is allocated for the statement in the shared pool.

   In either case, a private SQL area is created in the session's PGA and the dedicated server process checks the user's access privileges to the requested data.

6. The server process retrieves data blocks from the actual datafile, if necessary, or uses data blocks already stored in the buffer cache in the SGA of the instance.
7. The server process executes the SQL statement stored in the shared SQL area. Data is first changed in the SGA. It is permanently written to disk when the DBW0 process determines it is most efficient to do so. The LGWR process records the transaction in the online redo log file only on a subsequent commit request from the user.
8. If the request is successful, the server sends a message across the network to the user. If it is not successful, an appropriate error message is transmitted.
9. Throughout this entire procedure, the other background processes are running and watching for any conditions that require intervention. In addition, Oracle is managing other transactions and preventing contention between different transactions that request the same data.

### An Example of Oracle Using the Multithreaded Server

The following example is a simple illustration of the multithreaded server architecture:

1. A database server is currently running Oracle using the multithreaded server configuration.
2. A user process on a client workstation runs a database application such as SQL*Forms. The client application attempts to establish a connection to the database server using the proper Net8 driver.
3. The database server machine is currently running the proper Net8 driver. The network listener process on the database server detects the connection request of the user process and determines how the user process should be connected. If the user is using Net8 or SQL*Net Version 2, the listener informs the user process to reconnect using the address of an available dispatcher process.

   Note: If the user process connects with SQL*Net Version 1 or 1.1, the SQL*Net listener creates a dedicated server process on behalf of the user process and the remainder of the example operates as described in the preceding example. (User processes must connect with Net8 or SQL*Net Version 2 to use a shared server process.)

4. The user issues a single SQL statement. For example, the user updates a row into a table.
5. The dispatcher process places the user process's request on the request queue, which is in the SGA and shared by all dispatcher processes.
6. An available shared server process checks the common dispatcher request queue and picks up the next SQL statement on the queue. It then processes the SQL statement as described in Steps 5, 6, and 7 of the previous example. (In Step 5, parts of the session's private SQL area are created in the SGA.)
7. Once the shared server process finishes processing the SQL statement, the process places the result on the response queue of the dispatcher process that sent the request.
8. The dispatcher process checks its response queue and sends completed requests back to the user process that made the request.

## The Program Interface

The program interface is the software layer between a database application and Oracle. The program interface:

- provides a security barrier, preventing destructive access to the SGA by client user processes
- acts as a communication mechanism, formatting information requests, passing data, and trapping and returning errors
- converts and translates data, particularly between different types of computers or to external user program datatypes

The Oracle code acts as a server, performing database tasks on behalf of an application (a client), such as fetching rows from data blocks. It consists of several parts, provided by both Oracle software and operating-system-specific software.

### Program Interface Structure

The program interface consists of the following pieces:

- Oracle call interface (OCI) or the Oracle runtime library (SQLLIB)
- the client or user side of the program interface (also called the UPI)
- various Net8 drivers (protocol-specific communications software)
- operating system communications software
- the server or Oracle side of the program interface (also called the OPI)

Both the user and Oracle sides of the program interface execute Oracle software, as do the drivers.

Net8 is the portion of the program interface that allows the client application program and the Oracle server to reside on separate computers in your communication network.

### The Program Interface Drivers

Drivers are pieces of software that transport data, usually across a network. They perform operations like connect, disconnect, signal errors, and test for errors. Drivers are specific to a communications protocol. There is always a default driver.

You may install multiple drivers (such as the asynchronous or DECnet drivers), and select one as the default driver, but allow an individual user to use other drivers by specifying the desired driver at the time of connection. Different processes can use different drivers. A single process can have concurrent connections to a single database or to multiple databases (either local or remote) using different Net8 drivers.

The installation and configuration guide and Net8 documentation for your system contains details about choosing and installing drivers and adding new drivers after installation. The Net8 documentation describes selecting a driver at runtime while accessing Oracle.

Additional Information: See Oracle Net8 Administrator's Guide for more information about Net8.

### Operating System Communications Software

The lowest level software connecting the user side to the Oracle side of the program interface is the communications software, which is provided by the host operating system. DECnet, TCP/IP, LU6.2, and ASYNC are examples.

Additional Information: The communication software may be supplied by Oracle Corporation but is usually purchased separately from the hardware vendor or a third party software supplier. See your Oracle operating-system-specific documentation for more information about the communication software of your system.

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index