Oracle9i Database Administrator's Guide Release 1 (9.0.1) for Windows Part Number A90164-01 |
|
This chapter describes how to monitor Oracle9i for Windows.
This chapter contains these topics:
Table 4-1 describes tools that enable you to monitor your Oracle9i database:
Oracle for Windows NT Performance Monitor is a graphical tool for measuring the performance of Oracle9i for Windows objects on a local server or other servers on a network. This tool is the same in appearance and operation as the Windows NT Performance Monitor, except it has been preloaded with Oracle9i database performance elements.
On each computer, you can view the behavior of objects, such as the buffer cache, data dictionary cache, datafiles, threads, and processes. An object is a graphical representation of an element in your system. Every element, resource, and device in your system can be represented as an object.
There is a set of counters associated with each object. A counter is a unit of measurement used by the Performance Monitor to display activity. The type of activity the counter measures is dependent upon the type of object.
Certain types of objects and their respective counters are present on all systems. Other counters, such as application-specific counters, appear only if the computer is running the associated software.
Each of these objects has an associated set of counters that provide information about device usage, queue lengths, delays, and information used to measure throughput and internal congestion.
When you install Oracle for Windows NT Performance Monitor, values are automatically set in the registry as described in "Configuration Parameters and the Registry" of Oracle9i Database Getting Started for Windows guide.
The Oracle for Windows NT Performance Monitor lets you monitor only one database instance at a time. For this reason, the registry contains the following values:
Use the OPERFCFG utility to change these values. Oracle Corporation recommends setting the security level on each of these registry values.
See Also:
"Configuration Parameters and the Registry", of the Oracle9i Database Getting Started for Windows guide, for instructions on how to use the OPERFCFG utility. |
To use Oracle for Windows NT Performance Monitor for another database instance on the same computer or a UNIX computer, change the values appropriately in the registry. You can also monitor non-Windows NT Oracle databases by changing the Hostname
registry value so it points to another computer specified in the tnsnames.ora
file.
To access Oracle for Windows NT Performance Monitor:
Choose Start > Programs > Oracle - HOME_NAME
> Configuration and Migration Tools > Oracle for Windows NT Performance Monitor.
Figure 4-1 shows the Performance Monitor window displaying the Chart View:
The Oracle for Windows NT Performance Monitor has four views you can choose from the View menu. Table 4-2 describes these views:
For each view (Chart, Alert, Log, and Report), you can decide on the objects you want to monitor and save those settings to a file. When an object is chosen, it is assigned a counter, a color, and added to the status bar at the bottom of Oracle for Windows NT Performance Monitor.
To add objects to a view:
The Add to (Chart, Alert, Log, Report) dialog box appears.
Below is the Add to Chart dialog box. Note the corresponding dialog boxes for the other views are different.
Below are the elements of the Add to Chart dialog box. The other views' dialog boxes have similar features.
The selections you have chosen to monitor are displayed.
All Oracle9i system resources that can be monitored through Oracle for Windows NT Performance Monitor begin with Oracle9i. These measures are defined in ORACLE_BASE\ORACLE_HOME\
dbs\perf.ora
. Table 4-3 shows the Oracle9i objects and their associated counters. For additional information on these objects, see Oracle9i Database Performance Guide and Reference.
Object | Counter | Description |
---|---|---|
Oracle9i Buffer Cache |
phyrds/gets % |
The percentage of phyrds/gets is calculated as a Miss ratio. The lower the Miss counter, the better. To improve performance, increase the number of buffers in the buffer cache, if memory is available on the computer. This value is not time-derived. |
Oracle9i Redo Log Buffer |
redo log space requests |
The value of this counter must be near zero. If this value increments consistently, processes have had to wait for space in the redo log buffer. In this case, it may be necessary to increase the size of the redo log buffer. |
Oracle9i Data Dictionary Cache |
getmisses/gets % |
The value of this counter must be less than 10 or 15% for frequently accessed data dictionary caches. If the ratio continues to increase above this threshold while your application is running, increase the amount of memory available to the data dictionary cache.
To increase the memory available to the cache, increase the value of the initialization parameter This value is not time-derived. |
Oracle9i Library Cache |
reloads/pins % |
The percentage of SQL statements, PL/SQL blocks, and object definitions that required reparsing. Total Reloads must be near zero. If the ratio of Reloads to Pins is greater than 1%, then reduce the library cache misses. This value is not time-derived. |
Oracle9i Data Files |
Disk contention occurs when multiple processes try to access the same disk simultaneously. There are many ways of reducing disk contention, depending on the results from monitoring disk activity. Some corrective actions include:
These values are time-derived. |
|
Oracle9i DBWR stats1 |
|
These counters are helpful in tuning the Buffer Cache. |
|
Buffers scanned/sec is the number of buffers the DBWR scanned per second. The buffers scanned are on the LRU (Least Recently Used) list. |
|
|
LRU scans/sec is the number of times the DBWR scanned the (Least Recently Used) buffer list per second. |
|
Oracle9i DBWR stats2 |
|
These counters are helpful in determining how much work the DBWR has been requested to perform. |
|
Timeouts/sec is the number of times the DBWR timed-out per second. The DBWR is on a three second timeout interval. If the DBWR has not been posted within a three second interval, it times out. |
|
|
Checkpoints/sec is the number of checkpoint messages processed by the database writer per second. Whenever a checkpoint occurs, the DBWR must be messaged (posted) to "write dirty buffers to disk." |
|
Oracle9i Dynamic Space Management |
recursive calls/sec |
Dynamic extension causes Oracle9i to execute SQL statements in addition to those SQL statements issued by user processes. These SQL statements are called recursive calls. If Oracle9i makes excessive recursive calls while an application is running, it may be necessary to determine the cause.
Examine the recursive calls statistic through the dynamic performance table |
Oracle9i Free List |
free list waits/ requests % |
Contention for free lists is reflected by contention for free data blocks in the buffer cache. You can determine if contention for free lists is reducing performance by querying If the number of free list waits for free blocks is greater than 1% of the total number of requests, consider adding more free lists to reduce contention. |
Oracle9i Sorts |
The default sort area size is adequate to hold all the data for most sorts. However, if your application often performs large sorts on data that does not fit into the sort area, then you may want to increase the sort area size. |
If no data or Oracle9i objects appear in the Objects list of the Add to Chart dialog box, either:
The operf.log
file located in ORACLE_BASE\ORACLE_HOME\
dbs
contains error messages about Oracle for Windows NT Performance Monitor.
To resolve this problem:
ORACLE_BASE
\
ORACLE_HOME
\dbs\operf.log
file for error messages.
Hostname
, Password
, and Username
. See HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet of the Oracle9i Database Getting Started for Windows for further information about these values.
Oracle9i for Windows problems and other significant occurrences are recorded as events. These events are recorded in an application event log. View and manage these recorded events in the Event Viewer.
To access the Event Viewer:
The Event Viewer window appears.
Figure 4-2 displays the Application view window.
Table 4-4 describes the icons beside each event and the type of event.
Oracle9i for Windows NT events display with a source of Oracle.orcl. Oracle.orcl consists of the following event IDs described in Table 4-5:
To use the Event Viewer:
The Event Detail dialog box appears with more information about the selected event:
Setting AUDIT_TRAIL
to db
or os
causes more records to be written to the Event Viewer. This can fill up the Event Viewer log file. Follow these procedures to increase the log file size.
To increase log file size:
The Event Log Settings dialog box appears.
You are returned to the Event Viewer.
Oracle9i for Windows background threads use trace files to record occurrences and exceptions of database operations, as well as errors. Background thread trace files are created regardless of whether the background_dump_dest
parameter is set in the init.ora
initialization parameter file. If BACKGROUND_DUMP_DEST
is set, the trace files are stored in the directory specified. If the parameter is not set, the trace files are stored in the ORACLE_BASE\ADMIN\db_name
\bdump
directory.
Oracle9i database creates a different trace file for each background thread. The name of the trace file contains the name of the background thread, followed by the extension.TRC
. Sample trace file syntax includes:
where sid represents the name of the instance.
Trace files are also created for user threads if the USER_DUMP_DEST
parameter is set in the initialization parameter file. The trace files for the user threads have the form oraxxxxx.trc
, where xxxxx
is a 5-digit number indicating the Windows NT thread ID
.
The alert file contains important information about error messages and exceptions that occur during database operations. Each Oracle9i for Windows NT instance has one alert file; information is appended to the file each time you start the instance. All threads can write to the alert file.
For example, when automatic archiving of redo logs is halted because no disk space is available, a message is placed in the alert file. The alert file is the first place to check if something goes wrong with the database and the cause is not immediately obvious.
The alert file is named sid
ALRT.LOG
and is found in the directory specified by the BACKGROUND_DUMP_DEST
parameter in the initialization parameter file. If the BACKGROUND_DUMP_DEST
parameter is not set, the sid
ALRT.LOG
file is generated in ORACLE_BASE\
admin\
db_name
\bdump
. Alert files should be deleted or archived periodically.
To view information on Oracle threads using the Oracle Administration Assistant for Windows NT:
HOME_NAME
> Configuration and Migration Tools > Oracle Administration Assistant for Windows.
sid
, where sid
is a specific instance name, such as orcl
.
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|