Oracle8i Administrator's Guide
Release 2 (8.1.6)






Prev Next

Managing Oracle Processes

This chapter describes how to manage the processes of an Oracle instance, and includes the following topics:

Server Processes

Oracle creates server processes to handle the requests of user processes connected to an instance. A server process can be either a dedicated server process, where one server process services only one user process, or it can be a shared server process, where a server process can service multiple user processes. Shared server processes are part of Oracle's multi-threaded server(MTS) architecture.

See Also:

For conceptual information about server processes, see Oracle8i Concepts. 

Dedicated Server Processes

Figure 4-1 illustrates how dedicated server processes works.

In general, it is better to be connected through a dispatcher to use a shared server process; it can be more efficient because it keeps the number of processes required for the running instance low. In the following situations, however, users and administrators should explicitly connect to an instance using a dedicated server process:

To request a dedicated server connection when the server is configured for MTS, users must connect using a NET8 net service name that is configured to use a dedicated server. Specifically, the net service name value should include the SERVER=DEDICATED clause in the connect descriptor.

See Also:

For a complete description of the Net8 net service name, see the Net8 Administrator's Guide and your operating system-specific Oracle documentation.  

Figure 4-1 Oracle Dedicated Server Processes

Multi-Threaded Server Processes

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 on the telephone talking to the customer and 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 because the idle server process is holding system resources.

The multi-threaded server architecture eliminates the need for a dedicated server process for each connection (see Figure 4-2).

Figure 4-2 Oracle Multi-Threaded Server Processes

In a multi-threaded server (MTS) configuration, client user processes connect to a dispatcher. A dispatcher can support multiple client connections concurrently. Each client connection is bound to a virtual circuit. A virtual circuit is a piece of shared memory used by the dispatcher for client database connection requests and replies. The dispatcher places a virtual circuit on a common queue when a request arrives.

An idle shared server picks up the virtual circuit from the common queue, services the request, and relinquishes the virtual circuit before attempting to retrieve another virtual circuit from the common queue. This approach enables a small pool of server processes to serve a large number of clients. A significant advantage of MTS architecture over the dedicated server model is the reduction of system resources, enabling the support of an increased number of users.

The multi-threaded server architecture requires Net8. User processes targeting the multi-threaded server must connect through Net8, even if they are on the same machine as the Oracle instance.

There are several things that must be done to configure your system for MTS. These are discussed in the following section.

See Also:

To learn more about MTS, including additional features such as connection pooling, see the Net8 Administrator's Guide

Configuring Oracle for the Multi-Threaded Server

MTS is activated by the setting of database initialization parameters, and requires that a Net8 listener process be active. This section discusses the setting of initialization parameters and how to alter them. For specifics relating to Net8, see the Net8 Administrator's Guide.

Initialization Parameters for MTS

The initialization parameters controlling MTS are:

Parameter  Description 



Configures dispatcher processes in the multi-threaded server architecture. 

Optional. If you do not specify the following parameters, Oracle selects appropriate defaults. 


Specifies the maximum number of dispatcher processes allowed to be running simultaneously. 


Specifies the number of server processes that you want to create when an instance is started up. 


Specifies the maximum number of shared server processes allowed to be running simultaneously. 


Specifies the total number of virtual circuits that are available for inbound and outbound network sessions.  


Specifies the total number of MTS user sessions to allow. Setting this parameter enables you to reserve user sessions for dedicated servers. 

Other initialization parameters affected by MTS that may require adjustment. 


Specifies the size in bytes of the large pool allocation heap. MTS may force the default value to be set too high causing performance problems or the database won't start. See the Oracle8i Reference for further details.  


Specifies the maximum number of sessions that can be created in the system. May need to be adjusted for MTS. See the Oracle8i Reference for further details.  

See Also:

For detailed descriptions of settings and defaults for these parameters see the Net8 Administrator's Guide and Oracle8i Reference. 

MTS_DISPATCHERS: Setting the Initial Number of Dispatchers

The number of dispatcher processes started at instance startup is controlled by the MTS_DISPATCHERS initialization parameter. At least one dispatcher process is created for every communication protocol specified in the parameter. You can specify multiple MTS_DISPATCHERS parameters in the initialization file, but they must be adjacent to each other. Internally, Oracle will assign an INDEX value to each MTS_MISPATCHERS parameter, so that you can later specifically refer to that MTS_DISPATCHERS parameter in an ALTER SYSTEM statement.

The appropriate number of dispatcher processes for each instance depends upon the performance you want from your database, the host operating system's limit on the number of connections per process (which is operating system dependent), and the number of connections required per network protocol. The instance must be able to provide as many connections as there are concurrent users on the database system. After instance startup, you can start more dispatcher processes if needed. This is discussed in "Adding and Removing Dispatcher Processes".

A ratio of 1 dispatcher for every 1000 connections works well for typical systems, but round up to the next integer. For example, if you anticipate 1500 connections at peak time, then you may want to configure 2 dispatchers. Being too aggressive in your estimates is not beneficial, because configuring too many dispatchers can degrade performance. Use this ratio as your guide, but tune according to your particular circumstances.

The following are some examples of setting the MTS_DISPATCHERS initialization parameter.

Example 4-1

To force the IP address used for the dispatchers, enter the following:


This will start two dispatchers that will listen in on the IP address, which must be a valid IP address for the host that the instance is on, which must be a card that is accessible to the dispatchers.

Example 4-2

To force the exact location of dispatchers, add the PORT as follows:


MTS_SERVERS: Setting the Initial Number of Shared Servers

The MTS_SERVERS parameter specifies the number of server processes that you want to create when an instance is started up. 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.

Typical systems seem to stabilize at a ratio of one shared server for every ten connections. For OLTP applications, the connections-to-servers ratio could be higher, because the rate of requests could be low, or the ratio of server usage to request could be low. In applications where the rate of requests is high, or the server usage-to-request ratio is high, the connections-to-server ratio could be lower.

Set MTS_MAX_SERVERS to a reasonable value based on your application. Oracle provides good defaults for MTS_SERVERS and MTS_MAX_SERVERS for a typical configuration, but the optimal values for these settings can be different depending upon your application.


On Windows NT, take care when setting MTS_MAX_SERVERS to a high value: each server is a thread in a common process. 

MTS_MAX_SERVERS is a static initialization parameter, so you cannot change it without shutting down your database. However, MTS_SERVERS is a dynamic initialization parameter and can be changed using an ALTER SYSTEM statement.

Modifying Dispatcher and Server Processes

You can modify the settings for MTS_DISPATCHERS and MTS_SERVERS dynamically when an instance is running. If you have the ALTER SYSTEM privilege, you can use the ALTER SYSTEM statement to make such changes.

See Also:

For information about the ALTER SYSTEM statement, see the Oracle8i SQL Reference. 

Changing the Minimum Number of Shared Server Processes

After starting an instance, you can change the minimum number of shared server processes by using the SQL statement ALTER SYSTEM. Oracle will eventually terminate servers that are idle when there are more shared servers than the minimum limit you specify.

If you set MTS_SERVERS to 0, Oracle will terminate all current servers when they become idle and will not start any new servers until you increase MTS_SERVERS. Thus, setting MTS_SERVERS to 0 may be used to effectively disable the multi-threaded server.

The following statement dynamically sets the number of shared server processes to two:


Adding and Removing Dispatcher Processes

You can control the number of dispatcher processes in the instance. If the V$QUEUE, V$DISPATCHER and V$DISPATCHER_RATE views indicate that the load on the dispatcher processes is consistently high, starting additional dispatcher processes to route user requests may improve performance. In contrast, if the load on dispatchers is consistently low, reducing the number of dispatchers may improve performance.

To change the number of dispatcher processes, use the SQL statement ALTER SYSTEM.

You can start new dispatcher processes for an existing MTS_DISPATCHERS value, or you may add new MTS_DISPATCHERS values. You can add dispatchers up to the limit specified by MTS_MAX_DISPATCHERS.

If you reduce the number of dispatchers for a particular MTS dispatcher value, the dispatchers are not immediately removed. Rather, as users disconnect, Oracle is eventually able to terminate dispatchers down to the limit you specify in MTS_DISPATCHERS.

The following statement dynamically changes the number of dispatcher processes for the TCP/IP protocol to 5, and adds dispatcher processes for the SPX protocol. There was no MTS_DISPATCHES initialization parameter for the SPX protocol (the only MTS dispatchers parameter was the one for the TCP protocol), so this statement effectively adds one.


If there are currently fewer than 5 dispatcher processes for TCP, Oracle creates new ones. If there are currently more than 5, Oracle terminates some of them after the connected users disconnect.


The INDEX keyword can be used to identify which MTS_DISPATCHERS parameter to modify. The INDEX value can range from 0 to n, where n is one less than the defined number of MTS_DISPATCHER parameters. If your ALTER SYSTEM statement specifies an INDEX value equal to n+1, a new MTS_DISPATCHERS parameter is added. To identify the index number assigned to an MTS_DISPATCHERS parameter, query the CONF_INDX value in the V$DISPATCHER view.  

Shutting Down Specific Dispatcher Processes

It is possible to shut down specific dispatcher processes. To identify the name of the specific dispatcher process to be shut down, use the V$DISPATCHER dynamic performance view.

SELECT name, network FROM v$dispatcher;

----  -------------------------------------------------------------------

Each dispatcher is uniquely identified by a name of the form Dnnn.

To shut down dispatcher D002, issue the following statement:


The IMMEDIATE keyword stops the dispatcher from accepting new connections and Oracle immediately terminates all existing connections through that dispatcher. After all sessions are cleaned up, the dispatcher process shuts down. If IMMEDIATE were not specified, the dispatcher would wait until all of its users disconnected and all of its database links terminated before shutting down.

Monitoring MTS

The following are useful views for obtaining information about your MTS configuration and for monitoring performance.

View  Description 


Provides information on the dispatcher processes, including name, network address, status, various usage statistics, and index number. 


Provides rate statistics for the dispatcher processes. 


Contains information on the multi-thread message queues. 


Contains information on the shared server processes. 


Contains information about virtual circuits, which are user connections to the database through dispatchers and servers. 


Contains information for tuning MTS. 


Contains size information about various system global area (SGA) groups. May be useful when tuning MTS. 


Detailed statistical information about the SGA, useful for tuning. 


Lists statistics to help tune the reserved pool and space within the shared pool. 

See Also:

All of these views are described in detail in the Oracle8i Reference.

For specific information about monitoring and tuning the multi-threaded server, see Oracle8i Designing and Tuning for Performance

Tracking Oracle Background Processes

An Oracle instance can have many background processes. This section presents general methods of monitoring and tracking these processes, and includes the following topics:

What are the Oracle Background Processes

Briefly, these are the Oracle background processes.

Monitoring the Processes of an Oracle Instance

This section lists some of the views which you can use to monitor an Oracle instance. These views are more general in their scope. There are other views, more specific to a process, which are discussed in the section of this book where the process is described. Also presented are views and scripts for monitoring the status of locks.

See Also:

All of these views are described in detail in the Oracle8i Reference.

Oracle8i Designing and Tuning for Performance provides information for resolving performance problems and conflicts which may be revealed through the monitoring of these views. 

Process and Session Views

These views provide process and session specific information.

View  Description 


Contains information about the currently active processes. 


Lists session information for each current session. 


Contains I/O statistics for each user session. 


This view displays the status of various operations that run for longer than 6 seconds (in absolute time). These operations currently include many backup and recovery functions, statistics gathering, and query execution, and more operations are added for every Oracle release. 


Lists the resources or events for which active sessions are waiting. 


Contains session statistics. 


Provides information about current and maximum global resource utilization for some system resources. 


Contains statistics about shared SQL area and contains one row per SQL string. Also provides statistics about SQL statements that are in memory, parsed, and ready for execution. 


Contains statistics for non-parent latches and summary statistics for parent latches. 

Monitoring Locks

The UTLLOCKT.SQL script displays a simple character lock wait-for graph in tree-structured fashion. Using an ad hoc query tool, such as SQL*Plus, the script prints the sessions in the system that are waiting for locks and the corresponding blocking locks. The location of this script file is operating system dependent; see your operating system-specific Oracle documentation. A second script, CATBLOCK.SQL, creates the lock views that UTLLOCKT.SQL needs, so you must run it before running UTLLOCKT.SQL.

The following view can be used for monitoring locks.

View  Description 


Lists the locks currently held by the Oracle server and outstanding requests for a lock or latch. 

Trace Files, the Alert Log, and Background Processes

Each server and background process can write to an associated trace file. When an internal error is detected by a process, it dumps information about the error to its trace file. Some of the information written to a trace file is intended for the database administrator, while other information is for Oracle Worldwide Support. Trace file information is also used to tune applications and instances.

The alert log is a special trace file. The alert log of a database is a chronological log of messages and errors, which includes the following:

Oracle uses the alert log to keep a log of these special operations as an alternative to displaying such information on an operator's console (although many systems display information on the console). If an operation is successful, a "completed" message is written in the alert log, along with a timestamp.

Using the Trace Files

You can periodically check the alert log and other trace files of an instance to see if the background processes have encountered errors. For example, when the Log Writer process (LGWR) cannot write to a member of a group, an error message indicating the nature of the problem is written to the LGWR trace file and the database's alert log. If you see such error messages, a media or I/O problem has occurred, and should be corrected immediately.

Oracle also writes values of initialization parameters to the alert log, in addition to other important statistics. For example, when you shut down an instance normally or immediately (but do not abort), Oracle writes the highest number of sessions concurrently connected to the instance, since the instance started, to the alert log. You can use this number to see if you need to upgrade your Oracle session license.

Specifying the Location of Trace Files

All trace files for background processes and the alert log are written to the destination specified by the initialization parameter BACKGROUND_DUMP_DEST. All trace files for server processes are written to the destination specified by the initialization parameter USER_DUMP_DEST. The names of trace files are operating system specific, but usually include the name of the process writing the file (such as LGWR and RECO).

Controlling the Size of Trace Files

You can control the maximum size of all trace files (excluding the alert log) using the initialization parameter MAX_DUMP_FILE_SIZE. This limit is set as a number of operating system blocks. To control the size of an alert log, you must manually delete the file when you no longer need it; otherwise Oracle continues to append to the file. You can safely delete the alert log while the instance is running, although you might want to make an archived copy of it first.

Controlling When Oracle Writes to Trace Files

Background processes always write to a trace file when appropriate. In the case of the LGWR background process, it is possible, through an initialization parameter, to control the amount and type of trace information that is produced. This is described in"Controlling Trace Output Generated by the Archivelog Process". Other background processes do not have this flexibility.

Trace files are written on behalf of server processes (in addition to being written to during internal errors) only if the initialization parameter SQL_TRACE is set to TRUE. Regardless of the current value of SQL_TRACE, each session can enable or disable trace logging on behalf of the associated server process by using the SQL statement ALTER SESSION with the SET SQL_TRACE parameter.


For the multi-threaded server, each session using a dispatcher is routed to a shared server process, and trace information is written to the server's trace file only if the session has enabled tracing (or if an error is encountered). Therefore, to track tracing for a specific session that connects using a dispatcher, you might have to explore several shared server's trace files. Because the SQL trace facility for server processes can cause significant system overhead, enable this feature only when collecting statistics.

See Also:

For information about the names of trace files, see your operating system-specific Oracle documentation.

For information about initialization parameters that control the writing to trace files, see the Oracle8i Reference. 

Managing Processes for the Parallel Query Option

This section describes how, with the parallel query option, Oracle can perform parallel processing. In this configuration Oracle can divide the work of processing certain types of SQL statements among multiple query server processes. The following topics are included:

Managing the Query Servers

When you start your instance, the Oracle database server creates a pool of query server processes available for any query coordinator. Specify the number of query server processes that Oracle creates at instance startup via the initialization parameter PARALLEL_MIN_SERVERS.

Query server processes remain associated with a statement throughout its execution phase. When the statement is completely processed, its query server processes become available to process other statements. The query coordinator process returns any resulting data to the user process issuing the statement.

Variations in the Number of Query Server Processes

If the volume of SQL statements processed concurrently by your instance changes drastically, the Oracle database server automatically changes the number of query server processes in the pool to accommodate this volume.

If this volume increases, then Oracle automatically creates additional query server processes to handle incoming statements. The maximum number of query server processes for your instance is specified by the initialization parameter PARALLEL_MAX_SERVERS.

If this volume subsequently decreases, Oracle terminates a query server process if it has been idle for the period of time specified by the initialization parameter PARALLEL_SERVER_IDLE_TIME. Oracle does not reduce the size of the pool below the value of PARALLEL_MIN_SERVERS, no matter how long the query server processes have been idle.

If all query servers in the pool are occupied and the maximum number of query servers has been started, a query coordinator processes the statement sequentially.

See Also:

For more information about monitoring an instance's pool of query servers and determining the appropriate values of the initialization parameters, see Oracle8i Designing and Tuning for Performance

Managing Processes for External Procedures

You may have shared libraries of C functions that you wish to call from an Oracle database. This section describes how to set up an environment for calling those external procedures.


Although not required, it is recommended that you perform these tasks during installation. 

The database administrator grants execute privileges for appropriate libraries to application developers, who in turn create external procedures and grant execute privilege on the specific external procedures to other users.

Setting up an Environment for Calling External Routines

Follow these steps to set up an environment for calling external routines.

  1. Edit the tnsnames.ora file by adding an entry that enables you to connect to the listener process (and subsequently, the EXTPROC process).

  2. Edit the listener.ora file by adding an entry for the "external procedure listener."

  3. Start a separate listener process to exclusively handle external procedures.

  4. The EXTPROC process spawned by the listener inherits the operating system privileges of the listener, so Oracle strongly recommends that you restrict the privileges for the separate listener process. The process should not have permission to read or write to database files, or the Oracle server address space.

    Also, the owner of this separate listener process should not be ORACLE (which is the default owner of the server executable and database files).

  5. If not already installed, place the EXTPROC executable in $ORACLE_HOME/bin.

Be aware that the external library (DLL file) must be statically linked. In other words, it must not reference any external symbols from other external libraries (DLL files). These symbols are not resolved and can cause your external procedure to fail.

Sample Entry in tnsnames.ora

The following is a sample entry for the external procedure listener in tnsnames.ora.

extproc_connection_data = (DESCRIPTION =
                              (ADDRESS = (PROTOCOL=IPC)
                             (CONNECT_DATA = (SID = extproc_agent)

In this example, and all callouts for external procedures, the entry name EXTPROC_CONNECTION_DATA cannot be changed; it must be entered exactly as it appears here. The key you specify, in this case EXTPROC_KEY, must match the KEY you specify in the listener.ora file. Additionally, the SID name you specify, in this case EXTPROC_AGENT, must match the SID_NAME entry in the listener.ora file.

Sample Entry in listener.ora

The following is a sample entry for the external procedure in listener.ora.



     (SID_DESC = (SID_NAME=extproc_agent)

In this example, the PROGRAM must be EXTPROC, and cannot be changed; it must be entered exactly as it appears in this example. The SID_NAME must match the SID name in the tnsnames.ora file. The ORACLE_HOME must be set to the directory where your Oracle software is installed. The EXTPROC executable must reside in $ORACLE_HOME/bin.

See Also:

For more information about external procedures, see the PL/SQL User's Guide and Reference.

For more information about the tnsnames.ora and listener.ora files, see the Net8 Administrator's Guide.  

Terminating Sessions

In some situations, you might want to terminate current user sessions. For example, you might want to perform an administrative operation and need to terminate all non-administrative sessions.

This section describes the various aspects of terminating sessions, and includes the following topics:

When a session is terminated, the session's transaction is rolled back and resources (such as locks and memory areas) held by the session are immediately released and available to other sessions.

Terminate a current session using the SQL statement ALTER SYSTEM KILL SESSION.

The following statement terminates the session whose SID is 7 and serial number is 15:


Identifying Which Session to Terminate

To identify which session to terminate, specify the session's index number and serial number. To identify the index (SID) and serial number of a session, query the V$SESSION dynamic performance view.

The following query identifies all sessions for the user JWARD:

SELECT sid, serial#
  FROM v$session
  WHERE username = 'JWARD';

-----  ---------  --------
    7         15  ACTIVE 
   12         63  INACTIVE

A session is ACTIVE when it is making a SQL call to Oracle. A session is INACTIVE if it is not making a SQL call to Oracle.

See Also:

For a description of the status values for a session, see Oracle8i Reference. 

Terminating an Active Session

If a user session is making a SQL call to Oracle (ACTIVE status) when it is terminated, the transaction is rolled back and the user immediately receives the following message:

ORA-00028: your session has been killed

If, after receiving the ORA-00028 message, a user submits additional statements before reconnecting to the database, Oracle returns the following message:

ORA-01012: not logged on

If an active session cannot be interrupted (it is performing network I/O or rolling back a transaction), the session cannot be terminated until the operation completes. In this case, the session holds all resources until it is terminated. Additionally, the session that issues the ALTER SYSTEM statement to terminate a session waits up to 60 seconds for the session to be terminated; if the operation that cannot be interrupted continues past one minute, the issuer of the ALTER SYSTEM statement receives a message indicating that the session has been "marked" to be terminated. A session marked to be terminated is indicated in V$SESSION with a status of KILLED and a server that is something other than PSEUDO.

Terminating an Inactive Session

If the session is not making a SQL call to Oracle (is INACTIVE) when it is terminated, the ORA-00028 message is not returned immediately. The message is not returned until the user subsequently attempts to use the terminated session.

When an inactive session has been terminated, STATUS in the V$SESSION view is KILLED. The row for the terminated session is removed from V$SESSION after the user attempts to use the session again and receives the ORA-00028 message.

In the following example, an inactive session is terminated. First, V$SESSION is queried to identify the SID and SERIAL# of the session, then the session is terminated.

SELECT sid,serial#,status,server
   FROM v$session
   WHERE username = 'JWARD';

-----  --------  ---------  ---------
    7        15  INACTIVE   DEDICATED
   12        63  INACTIVE   DEDICATED
2 rows selected.
Statement processed.

SELECT sid, serial#, status, server
   FROM v$session
   WHERE username = 'JWARD';

-----  --------  ---------  ---------
    7        15  KILLED     PSEUDO
   12        63  INACTIVE   DEDICATED
2 rows selected.

Prev Next
Copyright © 1999 Oracle Corporation.

All Rights Reserved.