Oracle7 Server Administrator's Guide Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index



Go to previous file in sequence Go to next file in sequence

Managing Oracle7 Processes


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

See Also: For more information about performing specific tasks using Server Manager/GUI or Server Manager/LineMode, see the Oracle Server Manager User's Guide.

Configuring Oracle7 for Dedicated Server Processes

When a user process executes the database application on one machine, and a server process executes the associated Oracle server on another machine, you have separate, distinct processes. The separate server process created on behalf of each user is a dedicated server process (see Figure 4 - 1). Oracle7 is automatically installed for this configuration. If your operating system can support Oracle7 in this configuration, it may also support multi-threaded server processes.

Figure 4 - 1. Oracle Dedicated Server Processes

To start an instance in a dedicated server configuration, set the following initialization parameters (in the parameter file) to "null", or omit them from the file altogether:

When to Connect to a Dedicated Server Process

If possible, users should connect to an instance via a dispatcher. This 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, users must include the SRVR=DEDICATED clause in their SQL*Net TNS connect string.

See Also: For a complete description of SQL*Net connect string syntax, see your operating system-specific Oracle documentation and your SQL*Net documentation.

For more information about initialization parameters and parameter files, see the Oracle7 Server Reference.

Configuring Oracle7 for 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.

The multi-threaded server configuration eliminates the need for a dedicated server process for each connection (see Figure 4 - 2). A small number of shared server processes can perform the same amount of processing as many dedicated server processes. Also, the amount of memory required for each user is relatively small. Because less memory and process management are required, more users can be supported.

Figure 4 - 2. Oracle Multi-Threaded Sever Processes

To set up your system in a multi-threaded server configuration, start a network listener process and set the following initialization parameters:

After setting these initialization parameters, restart the instance, which at this point will use the multi-threaded server configuration. The multi-threaded server architecture requires SQL*Net Version 2. User processes targeting the multi-threaded server must connect through SQL*Net, even if they are on the same machine as the Oracle7 instance.

See Also: For more information about starting and managing the network listener process, see Oracle7 Server Distributed Systems, Volume I and the Oracle Network Manager Administrator's Guide.

SHARED_POOL_ SIZE: Allocating Additional Space in the Shared Pool for Shared Server

When users connect through the multi-threaded server, Oracle7 needs to allocate additional space in the shared pool for storing information about the connections between the user processes, dispatchers, and servers. For each user who will connect using the multi-threaded server, add 1K to the setting of the parameter SHARED_POOL_SIZE.

See Also: For more information about this parameter, see the Oracle7 Server Reference.

For more information about tuning, see the Oracle7 Server Tuning manual.

MTS_LISTENER_ ADDRESS: Setting the Listener Process Address

Within the database's parameter file, set the initialization parameter MTS_LISTENER_ADDRESS for each port to which the database will connect. The parameter supports the following syntax:

MTS_LISTENER_ADDRESS = "(addr)"

In the syntax above, addr is an address at which the listener will listen for connection requests for a specific protocol. The parameter file may contain multiple addresses.

The following examples specify listener addresses:

MTS_LISTENER_ADDRESS = "(ADDRESS=(PROTOCOL=tcp)(PORT=5000)\
   (HOST=ZEUS)"
MTS_LISTENER_ADDRESS = "(ADDRESS=(PROTOCOL=decnet)\
   (OBJECT=OUTA)(NODE=ZEUS)"

Each address specified in the database's parameter file must also be specified in the corresponding listener's configuration file. You specify addresses differently for various network protocols.

See Also: For more information about specifying addresses for the network listener process, see your operating system-specific Oracle documentation and your SQL*Net documentation.

MTS_SERVICE: Specifying Service Names for Dispatchers

Specify the name of the service associated with dispatchers using the parameter MTS_SERVICE. A user requests the multi-threaded server by specifying this service name in the connect string. A service name must be unique; if possible, use the instance's SID (system identifier).

If you do not set the MTS_SERVICE parameter, its value defaults to the DB_NAME parameter. (If DB_NAME is also not set, Oracle7 returns the error ORA-00114, "missing value for system parameter mts_service," when you start the database.)

If the dispatcher's service name is TEST_DB, the parameter would be set as follows:

MTS_SERVICE = "test_db"

A connect string for connecting to this dispatcher looks like the following:

SQLPLUS scott/tiger@\
   (DESCRIPTION=(ADDRESS=(PROTOCOL=decnet)(NODE=hq)\
   (OBJECT=mts7))(CONNECT_DATA=(SID=test_db)))

See Also: For more information about connect strings used with the multi-threaded server configuration, see your operating system-specific Oracle or SQL*Net documentation.

MTS_DISPATCHERS: Setting the Initial Number of Dispatchers

The number of dispatcher processes started at instance startup is controlled by the parameter MTS_DISPATCHERS. Estimate the number of dispatchers to start for each network protocol before instance startup.

When setting the MTS_DISPATCHERS parameter, you can include any valid protocol.

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; the more dispatchers you have, the better potential database performance users will see, since they will not have to wait as long for dispatcher service.

After instance startup, you can start more dispatcher processes if needed; however, you can only start dispatchers that use protocols mentioned in the database's parameter file. For example, if the parameter file starts dispatchers for protocol_A and protocol_B, you cannot later start dispatchers for protocol_C without changing the parameter file and restarting the instance.

See Also: For more information about dispatcher processes, see "Adding and Removing Dispatcher Processes" [*].

Calculating the Initial Number of Dispatcher Processes

Once you know the number of possible connections per process for your operating system, calculate the initial number of dispatcher processes to create during instance startup, per network protocol, using the following formula Here, connections per dispatcher is operating system-dependent:

number                 maximum number of concurrent sessions
of           = CEIL   (--------------------------------------------------------------------------)
dispatchers                connections per dispatcher

For example, assume that your system typically has 80 users concurrently connected via TCP/IP and 40 users connected via DECNet. In this case, the MTS_DISPATCHERS parameter should be set as follows:

MTS_DISPATCHERS = "TCP, 3"
MTS_DISPATCHERS = "DECNET, 3"

MTS_MAX_ DISPATCHERS: Setting the Maximum Number of Dispatchers

The parameter MTS_MAX_DISPATCHERS sets the maximum number of dispatcher processes (of all network protocols combined) that can be started for the duration of an instance.

You can create as many dispatcher processes as you need, but the total number of processes, including dispatchers, cannot exceed the host operating system's limit on the number of running processes.

Estimating the Maximum Number of Dispatches

To estimate the maximum number of dispatcher processes an instance will require, use the following formula:

                          maximum number of concurrent sessions
MTS_MAX_DISPATCHERS =     _____________________________________
                          connections per dispatcher

MTS_SERVERS: Setting the Initial Number of Shared Server Processes

A number of shared server processes start at instance startup, as determined by the parameter MTS_SERVERS. The appropriate number of initial shared server processes for a database system depends on how many users typically connect to it, and how much processing each user requires. If each user makes relatively few requests over a period of time, then each associated user process is idle for a large percentage of time. In that case, one shared server process can serve 10 to 20 users. If each user requires a significant amount of processing, a higher ratio of server processes to user processes is needed to handle requests.

If you want Oracle7 to use shared servers, you must set MTS_SERVERS to at least 1. If you omit the parameter or set it to 0, Oracle7 does not start any shared servers at all. However, you can subsequently set MTS_SERVERS to a number greater than 0 while the instance is running.

It is best to estimate fewer initial shared server processes. Additional shared servers start automatically when needed and are deallocated automatically if they remain idle for too long. However, the initial servers always remain allocated, even if they are idle. If you set the initial number of servers high, your system might incur unnecessary overhead. Experiment with the number of initial shared server processes and monitor shared servers until you find the ideal system performance for typical database activity.

See Also: For more information about changing the number of shared servers, see "Changing the Minimum Number of Shared Server Processes" [*].

MTS_MAX_SERVERS: Setting the Maximum Number of Shared Server Processes

The maximum number of shared server processes that can be started for the duration of an instance is established during instance startup by the parameter MTS_MAX_SERVERS. In general, set this parameter to allow an appropriate number of shared server processes at times of highest activity. Experiment with this limit and monitor shared servers to determine an ideal setting for this parameter.

Modifying Server Processes

This section describes changes you can make after starting an instance, and includes the following topics:

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 command ALTER SYSTEM.

Oracle7 eventually terminates dispatchers and servers that are idle longer than the minimum limit you specify.

If you set MTS_SERVERS to 0, Oracle7 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 effectively disables the multi-threaded server temporarily.

To control the minimum number of shared server processes, you must have the ALTER SYSTEM privilege.

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

ALTER SYSTEM SET MTS_SERVERS = 2

Adding and Removing Dispatcher Processes

You can control the number of dispatcher processes in the instance. If the V$QUEUE and V$DISPATCHER views indicate that the load on the dispatcher processes is consistently high, start additional dispatcher processes to route user requests without waiting; you may start new dispatchers until the number of dispatchers equals MTS_MAX_DISPATCHER. In contrast, if the load on dispatchers is consistently low, reduce the number of dispatchers.

To change the number of dispatcher processes, use the SQL command ALTER SYSTEM. Changing the number of dispatchers for a specific protocol has no effect on dispatchers for other protocols.

You can start new dispatcher processes for protocols specified in the MTS_LISTENER_ADDRESS parameter and in the MTS_DISPATCHERS parameter. Therefore, you can add dispatchers only for protocols for which there are dispatchers; to start dispatchers for protocols for which there are currently no dispatchers, shutdown the database, change the parameter file, and restart the database.

If you reduce the number of dispatchers for a particular protocol, the dispatchers are not immediately removed. Rather, Oracle7 eventually terminates dispatchers that are idle for too long, down to the limit you specify in MTS_DISPATCHERS.

To control the number of dispatcher processes, you must have the ALTER SYSTEM privilege.

The following example adds a dispatcher process where the number of dispatchers was previously three:

ALTER SYSTEM
	SET MTS_DISPATCHERS = 'TCPIP,4';

See Also: For more information about tuning the multi-threaded server, see the Oracle7 Server Tuning manual.

Tracking Oracle7 Processes

An Oracle7 instance can have many background processes, which you should track if possible. This section describes how to track these processes, and includes the following topics:

See Also: For more information about tuning Oracle7 processes, see the Oracle7 Server Tuning manual.

Monitoring the Processes of an Oracle7 Instance

Monitors provide a means of tracking database activity and resource usage. Selecting the Monitor feature of Server Manager/GUI displays current information about the processes of your Oracle7 database. You can operate several monitors simultaneously. Table 4 - 1 lists the Server Manager monitors that can help you track Oracle7 processes:

Monitor Name Description
Process The Process monitor summarizes information about all Oracle7 processes, including client-server, user, server, and background processes, currently accessing the database via the current database instance.
Session The Session monitor shows the session ID and status of each connected Oracle7 session.
Table 4 - 1. Server Manager Monitors

Monitoring Locks

Table 4 - 2 describes two methods of monitoring locking information for ongoing transactions within an instance:

Monitor Name Description
Server Manager Monitors The Monitor feature of Server Manager/GUI provides two monitors for displaying lock information for an instance: Lock and Latch Monitors.
UTLLOCKT.SQL The UTLLOCKT.SQL script displays a simple character lock wait-for graph in tree-structured fashion. Using an ad hoc query tool (such as Server Manager or SQL*Plus), the script prints the sessions in the system that is 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.)
Table 4 - 2. Oracle7 Monitoring Facilities

Monitoring Dynamic Performance Tables

The following views, created on the dynamic performance tables, are useful for monitoring Oracle7 instance processes.

View (Monitor) Name Description
V$CIRCUIT Contains information about virtual circuits, which are user connections through dispatchers and servers.
V$QUEUE Contains information about the multi-threaded message queues.
V$DISPATCHER Contains information about dispatcher processes.
V$SHARED_SERVER Contains information about shared server processes.
V$SQLAREA 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.
V$SESS_IO Contains I/O statistics for each user session.
V$LATCH Contains statistics for non-parent latches and summary statistics for parent latches.
V$SYSSTAT Contains system statistics.
Table 4 - 3. Views for Monitoring Oracle7 Instance Processes

Following is a typical query of one of the dynamic performance tables, V$DISPATCHER. The output displays the processing load on each dispatcher process in the system:

SELECT (busy/(busy + idle)) * 100 "% OF TIME BUSY"
   FROM v$dispatcher;

Distinguishing Oracle7 Background Processes from Operating System Background Processes

When you run many Oracle7 databases concurrently on one computer, Oracle7 provides a mechanism for naming the processes of an instance. The background process names are prefixed by an instance identifier to distinguish the set of processes for each instance.

For example, an instance named TEST might have background processes with the following names:

See Also: For more information about views and dynamic performance tables see the Oracle7 Server Reference.

For more information about the instance identifier and the format of the Oracle7 process names, see your operating system-specific Oracle documentation.

Trace Files, the ALERT File, 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 file is a special trace file. The ALERT file of a database is a chronological log of messages and errors, which includes the following:

Oracle7 uses the ALERT file 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 file, along with a timestamp.

Using the Trace Files

You can periodically check the ALERT file 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 file. If you see such error messages, a media or I/O problem has occurred, and should be corrected immediately.

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

Specifying the Location of Trace Files

All trace files for background processes and the ALERT file 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 file) 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 file, you must manually delete the file when you no longer need it; otherwise Oracle7 continues to append to the file. You can safely delete the ALERT file while the instance is running, although you might want to make an archived copy of it first.

Controlling When Oracle7 Writes to Trace Files

Background processes always write to a trace file when appropriate. However, 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 command ALTER SESSION with the SET SQL_TRACE parameter.

The following statement enables writing to a trace file for a particular session:

ALTER SESSION SET SQL_TRACE TRUE;

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: See "Session and User Licensing" [*] for details about upgrading your Oracle license.

For more information about messages, see the Oracle7 Server Messages manual.

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

For complete information about the ALTER SESSION command, see the Oracle7 Server SQL Reference.

Starting the Checkpoint Process

If the Checkpoint process (CKPT) is not enabled, the Log Writer process (LGWR) is responsible for updating the headers of all control files and data files to reflect the latest checkpoint. To reduce the time necessary to complete a checkpoint, especially when a database is comprised of many data files, enable the CKPT background process by setting the CHECKPOINT_PROCESS parameter in the database's parameter file to TRUE. (The default is FALSE.)

Managing Processes for the Parallel Query Option

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

See Also: For more information about the parallel query option, see the Oracle7 Server Tuning manual.

Managing the Query Servers

When you start your instance, the Oracle7 Server creates a pool of query server processes available for any query coordinator. Specify the number of query server processes that the Oracle7 Server 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 Oracle7 Server automatically changes the number of query server processes in the pool to accommodate this volume.

If this volume increases, the Oracle7 Server 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, the Oracle7 Server terminates a query server process if it has been idle for the period of time specified by the initialization parameter PARALLEL_SERVER_IDLE_TIME. The Oracle7 Server 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 the Oracle7 Server Tuning manual.

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 either the Disconnect Session menu item of Server Manager, or the SQL command ALTER SYSTEM...KILL SESSION.

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

ALTER SYSTEM KILL SESSION '7,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 numbers of a session, query the V$SESSION dynamic performance table.

The following query identifies all sessions for the user JWARD:

SELECT sid, serial#
	FROM v$session
	WHERE username = 'JWARD';
SID       SERIAL#    STATUS
--------- ---------- --------
        7         15 ACTIVE
       12         63 INACTIVE

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

See Also: For a complete description of the status values for a session, see Oracle7 Server Tuning.

Terminating an Active Session

If a user session is making an SQL call to Oracle7 (is ACTIVE) 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, Oracle7 returns the following message:

ORA-01012: not logged on

If an active session cannot be interrupted (for example, 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 an SQL call to Oracle7 (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 view V$SESSION 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, the DBA terminates an inactive session:

SVRMGR> SELECT sid, serial#, status, server
     2>    FROM v$session
     3>    WHERE username = 'JWARD';
SID        SERIAL# STATUS   SERVER
---------- ------- -------- ---------
         7      15 INACTIVE DEDICATED
        12      63 INACTIVE DEDICATED
2 rows selected.
SVRMGR> ALTER SYSTEM KILL SESSION '7,15';
Statement processed.
SVRMGR> SELECT sid, serial#, status, server
     2>    FROM v$session
     3>    WHERE username = 'JWARD';
SID        SERIAL# STATUS   SERVER
---------- ------- -------- ---------
         7      15 KILLED   PSEUDO
        12      63 INACTIVE DEDICATED
2 rows selected.




Go to previous file in sequence Go to next file in sequence
Prev Next
Oracle
Copyright © 1996 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index