Release 8.1.5
A67772-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Administrator's Guide Release 8.1.5 A67772-01 |
|
This chapter describes how to manage the processes of an Oracle instance, and includes the following topics:
When a user process executes the database application, and a separate, distinct server process executes the associated Oracle server on behalf of each user, the separate server process is a dedicated server process (see Figure 4-1). Oracle is automatically installed for this configuration. If your operating system can support Oracle in this configuration, it may also support multi-threaded server processes.
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 SERVER=DEDICATED clause in their Net8 TNS connect string.
See Also: For a complete description of Net8 connect string syntax, see your operating system-specific Oracle documentation and your Net8 Administrator's Guide.
For more information about initialization parameters and parameter files, see the Oracle8i Reference.
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). A small number of shared server processes can perform the same amount of processing as many dedicated server processes. Also, since the amount of memory required for each user is relatively small, less memory and process management are required, and more users can be supported.
To set up your system in a multi-threaded server configuration, start a network listener process and set the MTS_DISPATCHERS parameter (which is a required parameter that sets the initial number of dispatchers).
After setting this initialization parameter, restart the instance, which at this point will use the multi-threaded server configuration. 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.
See Also: For more information about starting and managing the network listener process, see Oracle8i Distributed Database Systems and the Oracle Net8 Administrator's Guide.
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. After instance startup, you can start more dispatcher processes if needed.
See Also: For more information about dispatcher processes, see "Adding and Removing 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.
number maximum number of concurrent sessions of = CEIL (--------------------------------------------------------------------------) dispatchers connections per dispatcher
For example, assume that your system typically has 900 users concurrently connected via TCP/IP and 600 users connected via SPX, and supports 255 connections per process. In this case, the MTS_DISPATCHERS parameter should be set as follows:
MTS_DISPATCHERS = "(PROTOCOL=TCP) (DISPATCHERS=4)" MTS_DISPATCHERS = "(PROTOCOL=SPX) (DISPATCHERS=3)"
To force the IP address used for the dispatchers, enter the following:
MTS_DISPATCHERS="(ADDRESS=(PARTIAL=TRUE)(PROTOCOL=TCP)\ (HOST=144.25.16.201))(DISPATCHERS=2)"
This will start two dispatchers that will listen in on HOST=144.25.16.201, which must be a card that is accessible to the dispatchers.
To force the exact location of dispatchers, add the PORT as follows:
MTS_DISPATCHERS="(ADDRESS=(PARTIAL=TRUE)(PROTOCOL=TCP)\ (HOST=144.25.16.201)(PORT=5000))(DISPATCHERS=1)" MTS_DISPATCHERS="(ADDRESS=(PARTIAL=TRUE)(PROTOCOL=TCP)\ (HOST=144.25.16.201)(PORT=5001))(DISPATCHERS=1)"
|
Note: You can specify multiple MTS_DISPATCHERS in the INIT.ORA file, but they must be adjacent to each other. Also, MTS_DISPATCHERS defaults to 1. |
This section describes changes you can make after starting an instance, and includes the following topics:
After starting an instance, you can change the minimum number of shared server processes by using the SQL command 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 disables the multi-threaded server.
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
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; you can start additional dispatchers until the number of dispatchers equals MTS_MAX_DISPATCHER. 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 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_DISPATCHERS parameter, or you may add new MTS_DISPATCHERS configurations. Therefore, you can add dispatchers for protocols for which there are dispatchers, and you can start dispatchers for protocols for which there are currently no dispatchers.
If you reduce the number of dispatchers for a particular protocol, the dispatchers are not immediately removed. Rather, Oracle eventually terminates dispatchers 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 shows how to add a dispatcher process for the SPX protocol (where previously there was only one MTS_DISPATCHER configuration):
ALTER SYSTEM SET MTS_DISPATCHERS = '(INDEX=1) (PRO=SPX)';
See Also: For more information about tuning the multi-threaded server, see Oracle8i Tuning.
An Oracle 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 Oracle processes, see Oracle8i Tuning.
Monitors provide a means of tracking database activity and resource usage. You can operate several monitors simultaneously. Table 4-1 lists the Enterprise Manager monitors that can help you track Oracle processes:
Table 4-2 describes two methods of monitoring locking information for ongoing transactions within an instance:
The following views, created on the dynamic performance tables, are useful for monitoring Oracle 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;
When you run many Oracle databases concurrently on one computer, Oracle 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 Oracle8i Reference.
For more information about the instance identifier and the format of the Oracle process names, see your operating system-specific Oracle documentation.
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:
Oracle 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.
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.
Oracle also writes values of initialization parameters to the ALERT file, 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 file. You can use this number to see if you need to upgrade your Oracle session license.
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).
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 Oracle 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.
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.
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: 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 Oracle8i SQL Reference.
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.)
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:
See Also: For more information about the parallel query option, see Oracle8i Tuning.
When you start your instance, the Oracle Server creates a pool of query server processes available for any query coordinator. Specify the number of query server processes that the Oracle 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.
If the volume of SQL statements processed concurrently by your instance changes drastically, the Oracle Server automatically changes the number of query server processes in the pool to accommodate this volume.
If this volume increases, the Oracle 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 Oracle 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 Oracle 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 Oracle8i Tuning.
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.
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.
To Set Up an Environment for Calling External Procedures
Also, the owner of this separate listener process should not be "oracle" (which is the default owner of the server executable and database files).
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.
The following is a sample entry for the external procedure listener in tnsnames.ora.
extproc_connection_data = (DESCRIPTION = (ADDRESS = (PROTOCOL=IPC) (KEY=extproc_key) ) (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.
The following is a sample entry for the external procedure in listener.ora.
EXTERNAL_PROCEDURE_LISTENER = (ADDRESS_LIST = (ADDRESS = (PROTOCOL=ipc) (KEY=extproc_key) ) ) ... SID_LIST_EXTERNAL_PROCEDURE_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME=extproc_agent) (ORACLE_HOME=/oracle) (PROGRAM=extproc) ) )
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.
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:
ALTER SYSTEM KILL SESSION '7,15';
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 a SQL call to Oracle. A session is INACTIVE if it is not making a SQL call to Oracle.
See Also: For a complete description of the status values for a session, see Oracle8i Tuning.
If a user session is making a SQL call to Oracle (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, Oracle 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."
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 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 administrator terminates an inactive session:
SELECT sid,serial#,status,server FROM v$session WHERE username = 'JWARD'; SID SERIAL# STATUS SERVER ---------- -------- --------- --------- 7 15 INACTIVE DEDICATED 12 63 INACTIVE DEDICATED 2 rows selected. ALTER SYSTEM KILL SESSION '7,15'; Statement processed. SELECT sid, serial#, status, server FROM v$session WHERE username = 'JWARD'; SID SERIAL# STATUS SERVER --------- -------- --------- --------- 7 15 KILLED PSEUDO 12 63 INACTIVE DEDICATED 2 rows selected.
