Oracle7 Server Administrator's Guide
Tracking Oracle7 Processes
An Oracle7 instance can have many background processes, which you should track if possible. This section describes how to track these processes, and includes the following topics:
See Also: For more information about tuning Oracle7 processes, see the Oracle7 Server Tuning manual.
Monitoring the Processes of an Oracle7 Instance
Monitors provide a means of tracking database activity and resource usage. Selecting the Monitor feature of Server Manager/GUI displays current information about the processes of your Oracle7 database. You can operate several monitors simultaneously. Table 4 - 1 lists the Server Manager monitors that can help you track Oracle7 processes:
Table 4 - 1. Server Manager Monitors
Monitoring Locks
Table 4 - 2 describes two methods of monitoring locking information for ongoing transactions within an instance:
Table 4 - 2. Oracle7 Monitoring Facilities
Monitoring Dynamic Performance Tables
The following views, created on the dynamic performance tables, are useful for monitoring Oracle7 instance processes.
| View (Monitor) Name
| Description
|
| V$CIRCUIT
| Contains information about virtual circuits, which are user connections through dispatchers and servers.
|
| V$QUEUE
| Contains information about the multi-threaded message queues.
|
| V$DISPATCHER
| Contains information about dispatcher processes.
|
| V$SHARED_SERVER
| Contains information about shared server processes.
|
| V$SQLAREA
| Contains statistics about shared SQL area and contains one row per SQL string. Also provides statistics about SQL statements that are in memory, parsed, and ready for execution.
|
| V$SESS_IO
| Contains I/O statistics for each user session.
|
| V$LATCH
| Contains statistics for non-parent latches and summary statistics for parent latches.
|
| V$SYSSTAT
| Contains system statistics.
|
Table 4 - 3. Views for Monitoring Oracle7 Instance Processes
Following is a typical query of one of the dynamic performance tables, V$DISPATCHER. The output displays the processing load on each dispatcher process in the system:
SELECT (busy/(busy + idle)) * 100 "% OF TIME BUSY"
FROM v$dispatcher;
Distinguishing Oracle7 Background Processes from Operating System Background Processes
When you run many Oracle7 databases concurrently on one computer, Oracle7 provides a mechanism for naming the processes of an instance. The background process names are prefixed by an instance identifier to distinguish the set of processes for each instance.
For example, an instance named TEST might have background processes with the following names:
See Also: For more information about views and dynamic performance tables see the Oracle7 Server Reference.
For more information about the instance identifier and the format of the Oracle7 process names, see your operating system-specific Oracle documentation.
Trace Files, the ALERT File, and Background Processes
Each server and background process can write to an associated trace file. When an internal error is detected by a process, it dumps information about the error to its trace file. Some of the information written to a trace file is intended for the database administrator, while other information is for Oracle WorldWide Support. Trace file information is also used to tune applications and instances.
The ALERT file is a special trace file. The ALERT file of a database is a chronological log of messages and errors, which includes the following:
- all internal errors (ORA-600), block corruption errors (ORA-1578), and deadlock errors (ORA-60) that occur
- administrative operations, such as CREATE/ALTER/DROP DATABASE/TABLESPACE/ROLLBACK SEGMENT SQL statements and STARTUP, SHUTDOWN, ARCHIVE LOG, and RECOVER Server Manager statements
- several messages and errors relating to the functions of shared server and dispatcher processes
- errors occurring during the automatic refresh of a snapshot
- the values of all initialization parameters at the time the database and instance start
Oracle7 uses the ALERT file to keep a log of these special operations as an alternative to displaying such information on an operator's console (although many systems display information on the console). If an operation is successful, a "completed" message is written in the ALERT file, along with a timestamp.
Using the Trace Files
You can periodically check the ALERT file and other trace files of an instance to see if the background processes have encountered errors. For example, when the Log Writer process (LGWR) cannot write to a member of a group, an error message indicating the nature of the problem is written to the LGWR trace file and the database's ALERT file. If you see such error messages, a media or I/O problem has occurred, and should be corrected immediately.
Oracle7 also writes values of initialization parameters to the ALERT file, in addition to other important statistics. For example, when you shutdown an instance normally or immediately (but do not abort), Oracle7 writes the highest number of sessions concurrently connected to the instance, since the instance started, to the ALERT file. You can use this number to see if you need to upgrade your Oracle7 session license.
Specifying the Location of Trace Files
All trace files for background processes and the ALERT file are written to the destination specified by the initialization parameter BACKGROUND_DUMP_DEST. All trace files for server processes are written to the destination specified by the initialization parameter USER_DUMP_DEST. The names of trace files are operating system-specific, but usually include the name of the process writing the file (such as LGWR and RECO).
Controlling the Size of Trace Files
You can control the maximum size of all trace files (excluding the ALERT file) using the initialization parameter MAX_DUMP_FILE_SIZE. This limit is set as a number of operating system blocks. To control the size of an ALERT file, you must manually delete the file when you no longer need it; otherwise Oracle7 continues to append to the file. You can safely delete the ALERT file while the instance is running, although you might want to make an archived copy of it first.
Controlling When Oracle7 Writes to Trace Files
Background processes always write to a trace file when appropriate. However, trace files are written on behalf of server processes (in addition to being written to during internal errors) only if the initialization parameter SQL_TRACE is set to TRUE.
Regardless of the current value of SQL_TRACE, each session can enable or disable trace logging on behalf of the associated server process by using the SQL command ALTER SESSION with the SET SQL_TRACE parameter.
The following statement enables writing to a trace file for a particular session:
ALTER SESSION SET SQL_TRACE TRUE;
For the multi-threaded server, each session using a dispatcher is routed to a shared server process, and trace information is written to the server's trace file only if the session has enabled tracing (or if an error is encountered). Therefore, to track tracing for a specific session that connects using a dispatcher, you might have to explore several shared server's trace files. Because the SQL trace facility for server processes can cause significant system overhead, enable this feature only when collecting statistics.
See Also: See "Session and User Licensing" ![[*]](jump.gif)
for details about upgrading your Oracle license.
For more information about messages, see the Oracle7 Server Messages manual.
For information about the names of trace files, see your operating system-specific Oracle documentation.
For complete information about the ALTER SESSION command, see the Oracle7 Server SQL Reference.
Starting the Checkpoint Process
If the Checkpoint process (CKPT) is not enabled, the Log Writer process (LGWR) is responsible for updating the headers of all control files and data files to reflect the latest checkpoint. To reduce the time necessary to complete a checkpoint, especially when a database is comprised of many data files, enable the CKPT background process by setting the CHECKPOINT_PROCESS parameter in the database's parameter file to TRUE. (The default is FALSE.)