Release 2 (9.2)
Part Number A96521-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| Oracle9i Database Administrator's Guide Release 2 (9.2) Part Number A96521-01 |
|
This chapter describes how to manage the processes of an Oracle instance, and contains 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 if your database server is configured for shared server, it can be a shared server process, where a server process can service multiple user processes.
Figure 5-1, "Oracle Dedicated Server Processes" illustrates how dedicated server processes work. In this diagram two user processes are connected to Oracle through dedicated server processes.
In general, it is better to be connected through a dispatcher and use a shared server process. This is illustrated in Figure 5-2, "Oracle Shared Server Processes". A shared server process 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 Oracle is configured for shared server, users must connect using a 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:
Oracle9i Net Services Administrator's Guide for more information about requesting a dedicated server connection |
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 shared server architecture eliminates the need for a dedicated server process for each connection (see Figure 5-2).
In a shared server 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 shared server architecture over the dedicated server model is the reduction of system resources, enabling the support of an increased number of users.
The shared server architecture requires Oracle Net Services. User processes targeting the shared server must connect through Oracle Net Services, 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 shared server. These are discussed in the next section.
| See Also:
Oracle9i Net Services Administrator's Guide for more information about shared server, including additional features such as connection pooling |
You activate shared server by setting database initialization parameters. Shared server requires that an Oracle Net Services listener process be active. This section discusses setting shared server initialization parameters and how to alter them.
This section contains the following topics:
The initialization parameters controlling shared server are:
The number of dispatcher processes started at instance startup is controlled by the DISPATCHERS initialization parameter. You can specify multiple DISPATCHERS parameters in the initialization file, but they must be adjacent to each other. Internally, Oracle will assign an INDEX value to each DISPATCHERS parameter, so that you can later specifically refer to that 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 for each process (which is operating system dependent), and the number of connections required for each 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 DISPATCHERS initialization parameter.
This is a typical example of setting the DISPATCHERS initialization parameter.
DISPATCHERS="(PROTOCOL=TCP)"
To force the IP address used for the dispatchers, enter the following:
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.
To force the exact location of dispatchers, add the PORT as follows:
DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)(PORT=5000))" DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)(PORT=5001))"
The SHARED_SERVERS initialization parameter specifies the number of shared 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 SHARED_SERVERS and MAX_SHARED_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. This could happen when the rate of requests is low, or when the ratio of server usage to request is low. On the other hand, 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 MAX_SHARED_SERVERS to a reasonable value based on your application. Oracle provides good defaults for SHARED_SERVERS and MAX_SHARED_SERVERS for a typical configuration, but the optimal values for these settings can be different depending upon your application.
|
Note: On Windows NT, take care when setting |
MAX_SHARED_SERVERS is a static initialization parameter, so you cannot change it without shutting down your database. However, SHARED_SERVERS is a dynamic initialization parameter and can be changed using an ALTER SYSTEM statement.
You can modify the settings for DISPATCHERS and SHARED_SERVERS dynamically when an instance is running. If you have the ALTER SYSTEM privilege, you can use the ALTER SYSTEM statement to make such changes.
| See Also:
Oracle9i SQL Reference for information about the |
You can control the number of dispatcher processes in the instance. If monitoring the V$QUEUE, V$DISPATCHER and V$DISPATCHER_RATE views indicates 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 DISPATCHERS value, or you can add new DISPATCHERS values. Dispatchers can be added up to the limit specified by MAX_DISPATCHERS.
If you reduce the number of dispatchers for a particular shared server 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 DISPATCHERS.
The following statement dynamically changes the number of dispatcher processes for the TCP/IP protocol to 5, and adds dispatcher processes for the TCP/IP with SSL (TCPS) protocol. There was no DISPATCHERS initialization parameter for the TCPS protocol (the only DISPATCHERS parameter was the one for the TCP protocol), so this statement effectively adds one.
ALTER SYSTEM SET DISPATCHERS = '(PROTOCOL=TCP)(DISPATCHERS=5) (INDEX=0)', '(PROTOCOL=TCPS)(DISPATCHERS=2) (INDEX=1)';
If there are currently fewer than five dispatcher processes for TCP, Oracle creates new ones. If there are currently more than five, 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 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 connections terminated before shutting down.
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 SHARED_SERVERS to 0, Oracle terminates all current servers when they become idle and does not start any new servers until you increase SHARED_SERVERS. Thus, setting SHARED_SERVERS to 0 may be used to effectively disable shared server.
The following statement dynamically sets the minimum number of shared server processes to two:
ALTER SYSTEM SET SHARED_SERVERS = 2;
The following are useful views for obtaining information about your shared server configuration and for monitoring performance.
See Also:
|
To maximize performance and accommodate many users, a multiprocess Oracle system uses some additional processes called background processes. Background processes consolidate functions that would otherwise be handled by multiple Oracle programs running for each user process. Background processes asynchronously perform I/O and monitor other Oracle processes to provide increased parallelism for better performance and reliability.
The following are some basic Oracle background processes, many of which are discussed in more detail elsewhere in this book. The use of additional Oracle database server features or options can cause more background processes to be present. For example, if you use Advanced Queuing, the queue monitor (QMNn) background process is present, or if you specified the FILE_MAPPING initialization parameter for mapping datafiles to physical devices on a storage subsystem, then the LMON process is present.
| Process Name | Description |
|---|---|
|
Database writer (DBWn) |
The database writer writes modified blocks from the database buffer cache to the datafiles. Oracle allows a maximum of 20 database writer processes (DBW0-DBW9 and DBWa-DBWj). The initialization parameter For more information about setting the |
|
Log writer (LGWR) |
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 7, "Managing the Online Redo Log" for information about the log writer process. |
|
Checkpoint (CKPT) |
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. |
|
System monitor (SMON) |
The system monitor performs crash recovery when a failed instance starts up again. In a cluster database (Oracle9i Real Application Clusters), 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"). |
|
Process monitor (PMON) |
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 the dispatcher processes (see below) and server processes and restarts them if they have failed. |
|
Archiver (ARCn) |
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 8, "Managing Archived Redo Logs". |
|
Recoverer (RECO) |
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 Chapter 32, "Managing Distributed Transactions". |
|
Dispatcher (Dnnn) |
Dispatchers are optional background processes, present only when the shared server configuration is used. Shared server was discussed previously in "Configuring Oracle for the Shared Server". |
|
Global Cache Service (LMS) |
In an Oracle Real Application Clusters environment, this process manages resources and provides inter-instance resource control. See: |
|
Coordinator job queue process (CJQ0) |
This is the coordinator of job queue processes for an instance. It monitors the JOB$ table (table of jobs in the job queue) and starts job queue processes (Jnnn) as needed to execute jobs The Jnnn processes execute job requests created by the DBMS_JOBS package. This is the subject of Chapter 10, "Managing Job Queues" Additionally, up to 1000 Jnnn processes can automatically refresh materialized views. They wake up periodically and refresh any materialized views that are scheduled to be refreshed. For information about creating and refreshing materialized views, see: Yet another function of the Jnnn processes is to propagate queued messages to queues on other databases. See Oracle9i Application Developer's Guide - Advanced Queuing for information on propagating queued messages. Unlike many Oracle background processes, if a job queue process or the coordinator (CJQ0) fails, it does not cause instance failure. |
| See Also:
Oracle9i Database Concepts for more information about Oracle's background processes |
This section lists some of the data dictionary views that 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, that are discussed in the section of this book where the process is described. Also presented are scripts and a view for monitoring the status of locks.
See Also:
|
These views provide process and session specific information:
The utllockt.sql script displays, in tree-structured fashion, the sessions in the system that are waiting for locks and the locks that they are waiting for. Using an ad hoc query tool, such as SQL*Plus, the script prints the sessions in the system that are waiting for locks and the corresponding blocking locks. The location of this script file is operating system dependent; see your operating system specific Oracle documentation. A second script, catblock.sql, creates the lock views that utllockt.sql needs, so you must run it before running utllockt.sql.
The following view can be used for monitoring locks:
| View | Description |
|---|---|
|
|
Lists the locks currently held by the Oracle server and outstanding requests for a lock or latch |
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 Support Services. Trace file information is also used to tune applications and instances.
The alert file, or alert log, is a special trace file. The alert file of a database is a chronological log of messages and errors, which includes the following:
ORA-600), block corruption errors (ORA-1578), and deadlock errors (ORA-60) that occurCREATE, ALTER, and DROP statements and STARTUP, SHUTDOWN, and ARCHIVELOG statementsOracle 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.
Initialization parameters controlling the location and size of trace files are:
These parameters are discussed in the sections that follow.
| See Also:
Oracle9i Database Reference for information about initialization parameters that control the writing to trace files |
You should 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, 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 directory specified by the initialization parameter BACKGROUND_DUMP_DEST. All trace files for server processes are written to the destination directory specified by the initialization parameter USER_DUMP_DEST. The names of trace files are operating system specific, but each file usually includes the name of the process writing the file (such as LGWR and RECO).
| See Also:
Your operating system specific Oracle documentation for information about the names 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 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. In the case of the ARCn 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 whenever internal errors occur. Additionally, setting the initialization parameter SQL_TRACE = TRUE causes the SQL trace facility to generate performance statistics for the processing of all SQL statements for an instance and write them to the USER_DUMP_DEST directory.
Optionally, trace files can be generated for server processes at user request. Regardless of the current value of the SQL_TRACE initialization parameter, each session can enable or disable trace logging on behalf of the associated server process by using the SQL statement ALTER SESSION SET SQL_TRACE. This example enables the SQL trace facility for a specific session:
ALTER SESSION SET SQL_TRACE TRUE;
|
Caution: Because the SQL trace facility for server processes can cause significant system overhead resulting in severe performance impact, enable this feature only when collecting statistics. |
For shared 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.
The DBMS_SESSION and DBMS_SYSTEM packages can also be used to control SQL tracing for a session.
| See Also:
Oracle9i Database Performance Tuning Guide and Reference contains information about using the SQL trace facility and using |
This section describes how to manage parallel processing of SQL statements. In this configuration Oracle can divide the work of processing an SQL statement among multiple parallel processes.
The execution of many SQL statements can be parallelized. The degree of parallelism is the number of parallel execution servers that can be associated with a single operation. The degree of parallelism is determined by any of the following:
PARALLEL clause in a statementPARALLEL clause that was used when the object was created or alteredAn example of using parallel execution is contained in "Parallelizing Table Creation".
The following topics are contained in this section:
|
Note: The parallel execution feature described in this section is available with the Oracle9i Enterprise Edition and Oracle9i Personal Edition. |
See Also:
|
With the parallel execution feature, a process known as the parallel execution coordinator dispatches the execution of a pool of parallel execution servers and coordinates the sending of results from all of these parallel execution servers back to the user. Parallel execution server processes remain associated with a statement throughout its execution phase. When the statement is completely processed, these processes become available to process other statements.
Parallel execution can be tuned for you automatically by setting the initialization parameter PARALLEL_AUTOMATIC_TUNING = TRUE. With this setting, Oracle determines the default values for other initialization parameters that affect the performance of parallel execution.
The ALTER SESSION statement can be used to control parallel execution for a session.
All subsequent DML (INSERT, UPDATE, DELETE), DDL (CREATE, ALTER), or query (SELECT) statements will not be parallelized after an ALTER SESSION DISABLE PARALLEL DML|DDL|QUERY statement is issued. They will be executed serially, regardless of any PARALLEL clause or parallel hints associated with the statement.
The following statement disables parallel DDL:
ALTER SESSION DISABLE PARALLEL DDL;
Where a PARALLEL clause or parallel hint is associated with a statement, those DML, DDL, or query statements will execute in parallel after an ALTER SESSION ENABLE PARALLEL DML|DDL|QUERY statement is issued. This is the default for DDL and query statements.
A DML statement can be parallelized only if you specifically issue this statement. The following statement enables parallel processing of DML statements:
ALTER SESSION ENABLE PARALLEL DML;
You can force parallel execution of all subsequent DML, DDL, or query statements for which parallelization is possible with the ALTER SESSION FORCE PARALLEL DML|DDL|QUERY statement. Additionally you can force a specific degree of parallelism to be in effect, overriding any PARALLEL clause associated with subsequent statements. If you do not specify a degree of parallelism in this statement, the default degree of parallelism is used. However, a degree of parallelism specified in a statement through a hint will override the degree being forced.
The following statement forces parallel execution of subsequent statements and sets the overriding degree of parallelism to 5:
ALTER SESSION FORCE PARALLEL DDL PARALLEL 5;
To force the parallelization of DML, it must also be enabled as shown in "Enabling Parallel Execution".
External procedures, are procedures that are called from another program, but are written in a different language. An example would be a PL/SQL program calling one or more C routines that are required to perform special-purpose processing.
These callable routines are stored in a dynamic link library (DLL), or libunit in the case of a Java class method, and are registered with the base language. Oracle provides a special-purpose interface, the call specification (call spec), that enables users to call external procedures from other languages.
Very briefly, to call an external procedure, the application must know the DLL or shared library in which the external procedure resides. It alerts a network listener process, which in turn starts an external procedure agent, which by default is named extproc. Using the network connection established by the listener, the application passes to the external procedure agent the name of the DLL, the name of the external procedure, and any parameters passed in by the application. Then, the external procedure agent loads the DLL and runs the external procedure and passes back to the application any values returned by the external procedure.
The agent must reside on the same computer as the database server.
To control access to DLLs, the database administrator grants execute privileges for the appropriate DLLs to application developers. The application developers write the external procedures and grant execute privilege on specific external procedures to other users.
The environment for calling external procedures, consisting of tnsnames.ora and listener.ora entries, is configured by default during the install of your database. You may need to perform additional network configuration steps for a higher level of security. These are documented in the Oracle9i Net Services Administrator's Guide.
| See Also:
Oracle9i Application Developer's Guide - Fundamentals for information about external procedures |
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 contains 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 system identifier 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 system identifier (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#, STATUS 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:
Oracle9i Database Reference for a description of the status values for a session |
If a user session is processing a transaction (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 © 2001, 2002 Oracle Corporation. All Rights Reserved. |
|