|Oracle7 Server Administrator's Guide||
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:
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.
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:
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.
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 = "(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.
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.
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" .
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"
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.
maximum number of concurrent sessions
MTS_MAX_DISPATCHERS = _____________________________________
connections per dispatcher
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" .
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
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.
|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.|
|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.)|
|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.|
SELECT (busy/(busy + idle)) * 100 "% OF TIME BUSY" FROM v$dispatcher;
For example, an instance named TEST might have background processes with the following names:
For more information about the instance identifier and the format of the Oracle7 process names, see your operating system-specific Oracle documentation.
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 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.
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.
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 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.
This section describes the various aspects of terminating sessions, and includes the following topics:
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';
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.
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."
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.
Copyright © 1996 Oracle Corporation.
All Rights Reserved.