Release 2 (8.1.6)
Part Number A76956-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Administrator's Guide Release 2 (8.1.6) Part Number A76956-01 |
|
This chapter describes how to manage the processes of an Oracle instance, and includes the following topics:
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.
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. |
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).
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. |
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.
The initialization parameters controlling MTS are:
| Parameter | Description |
|---|---|
|
Required |
|
|
MTS_DISPATCHERS |
Configures dispatcher processes in the multi-threaded server architecture. |
|
Optional. If you do not specify the following parameters, Oracle selects appropriate defaults. |
|
|
MTS_MAX_DISPATCHERS |
Specifies the maximum number of dispatcher processes allowed to be running simultaneously. |
|
MTS_SERVERS |
Specifies the number of server processes that you want to create when an instance is started up. |
|
MTS_MAX_SERVERS |
Specifies the maximum number of shared server processes allowed to be running simultaneously. |
|
MTS_CIRCUITS |
Specifies the total number of virtual circuits that are available for inbound and outbound network sessions. |
|
MTS_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. |
|
|
LARGE_POOL_SIZE |
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. |
|
SESSIONS |
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. |
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.
To force the IP address used for the dispatchers, enter the following:
MTS_DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)\ (HOST=144.25.16.201))(DISPATCHERS=2)"
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.
To force the exact location of dispatchers, add the PORT as follows:
MTS_DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)(HOST=144.25.16.201)(PORT=5000))" MTS_DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)(HOST=144.25.16.201)(PORT=5001))"
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.
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.
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.
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:
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. 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.
ALTER SYSTEM SET MTS_DISPATCHERS = '(PROTOCOL=TCP)(DISPATCHERS=5) (INDEX=0)', '(PROTOCOL=SPX)(DISPATCHERS=2) (INDEX=1)';
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.
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; NAME NETWORK ---- ------------------------------------------------------------------- D000 (ADDRESS=(PROTOCOL=tcp)(HOST=rbaylis-hpc.us.oracle.com)(PORT=3499)) D001 (ADDRESS=(PROTOCOL=tcp)(HOST=rbaylis-hpc.us.oracle.com)(PORT=3531)) D002 (ADDRESS=(PROTOCOL=tcp)(HOST=rbaylis-hpc.us.oracle.com)(PORT=3532))
Each dispatcher is uniquely identified by a name of the form Dnnn.
To shut down dispatcher D002, issue the following statement:
ALTER SYSTEM SHUTDOWN IMMEDIATE 'D002';
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.
The following are useful views for obtaining information about your MTS configuration and for monitoring performance.
|
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. |
An Oracle instance can have many background processes. This section presents general methods of monitoring and tracking these processes, and includes the following topics:
Briefly, these are the Oracle background processes.
The Database Writer writes modified blocks from the database buffer cache to the datafiles. Although one database writer process (DBW0) is sufficient for most systems, you can configure additional processes (DBW1 through DBW9) to improve write performance for a system that modifies data heavily. The initialization parameter DB_WRITER_PROCESSES specifies the number of DBWn processes.
The log writer process writes redo log entries to disk. Redo log entries are generated in the redo log buffer of the system global area (SGA), and LGWR writes the redo log entries sequentially into an online redo log file. If the database has a multiplexed redo log, LGWR writes the redo log entries to a group of online redo log files. See Chapter 6, "Managing the Online Redo Log" for information about the log writer process.
At specific times, all modified database buffers in the system global area are written to the datafiles by DBWn; this event is called a checkpoint. The checkpoint process is responsible for signalling DBWn at checkpoints and updating all the datafiles and control files of the database to indicate the most recent checkpoint.
The system monitor performs crash recovery when a failed instance starts up again. In a multiple instance system (one that uses Oracle Parallel Server), the SMON process of one instance can perform instance recovery for other instances that have failed. SMON also cleans up temporary segments that are no longer in use and recovers dead transactions skipped during crash and instance recovery because of file-read or offline errors. These transactions are eventually recovered by SMON when the tablespace or file is brought back online.
SMON also coalesces free extents within the database's dictionary-managed tablespaces to make free space contiguous and easier to allocate (see "Coalescing Free Space in Dictionary-Managed Tablespaces").
The process monitor performs process recovery when a user process fails. PMON is responsible for cleaning up the cache and freeing resources that the process was using. PMON also checks on dispatcher (see below) and server processes and restarts them if they have failed. For information about PMON, see Oracle8i Concepts.
One or more archiver processes copy the online redo log files to archival storage when they are full or a log switch occurs. Archiver processes are the subject of Chapter 7, "Managing Archived Redo Logs".
The recoverer process is used to resolve distributed transactions that are pending due to a network or system failure in a distributed database. At timed intervals, the local RECO attempts to connect to remote databases and automatically complete the commit or rollback of the local portion of any pending distributed transactions. For information about this process and how to start it, see Oracle8i Distributed Database Systems.
Dispatchers are optional background processes, present only when the multi-threaded server (MTS) configuration is used. MTS was discussed previously in "Configuring Oracle for the Multi-Threaded Server".
In an Oracle Parallel Server, a lock process provides inter-instance locking. For information about this background process, see Oracle8i Parallel Server Setup and Configuration Guide, Oracle8i Parallel Server Administration, Deployment, and Performance, and Oracle8i Parallel Server Concepts.
In a distributed database configuration, up to 36 job queue processes can automatically refresh table snapshots. They wake up periodically and refresh any snapshots that are scheduled to be refreshed. For information about creating and refreshing snapshots, see Oracle8i Replication, Oracle8i Replication Management API Reference, and Getting Started with Replication Manager.
Another function of these processes is to propagate queued messages to queues on other databases. See Oracle8i Application Developer's Guide - Advanced Queuing) for information on propagating queued messages.
These processes also execute job requests created by the DBMS_JOBS package. This is the subject of Chapter 8, "Managing Job Queues".
Unlike most Oracle background processes, if an SNP process fails, it does not cause instance failure.
The queue monitor process is an optional background process for Oracle Advanced Queuing. You can configure up to 10 queue monitor processes. Like the SNPn processes, if these processes fail, they do not cause instance failure. The AQ_TM_PROCESSES initialization parameter specifies the creation of queue monitor processes at instance startup. For information about Advanced Queuing, see Oracle8i Application Developer's Guide - Advanced Queuing.
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. |
These views provide process and session specific information.
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 |
|---|---|
|
V$LOCK |
Lists the locks currently held by the Oracle server and outstanding requests for a lock or latch. |
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.
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.
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).
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.
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.
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 information about initialization parameters that control the writing to trace files, see the Oracle8i Reference. |
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:
For more information about the parallel query option, see Oracle8i Designing and Tuning for Performance and Oracle8i Concepts.
See Also:
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.
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. |
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.
Follow these steps to set up an environment for calling external routines.
tnsnames.ora file by adding an entry that enables you to connect to the listener process (and subsequently, the EXTPROC process).
listener.ora file by adding an entry for the "external procedure listener."
Also, the owner of this separate listener process should not be ORACLE (which is the default owner of the server executable and database files).
$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.
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.
For more information about the |
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 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'; 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.
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.
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'; 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.
|
 Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|