Release 8.1.5 for Windows NT
A68694-01
Library |
Product |
Contents |
Index |
| Oracle8i
Enterprise Edition for Windows NT Getting Started
Release 8.1.5 for Windows NT A68694-01 |
|
This chapter describes how to monitor Oracle8i Enterprise
Edition.
Specific topics discussed are:
The following tools enable you to monitor your Oracle8i
database:
Each tool is described in the following sections.
| Additional
Information:
See Oracle8i Tuning for general tuning information and your operating system documentation for additional information on Windows NT Performance Monitor results and optimizing database performance. |
Oracle Performance Monitor is a graphical tool for measuring
the performance of Oracle8i Enterprise Edition 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 Oracle8i database performance elements.
On each computer, you can view the behavior of objects, such
as the buffer cache, data dictionary cache, data files, 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 object types and their respective counters are present
on all systems. Other counters, such as application-specific counters (like
Server Manager), 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 Performance Monitor, values are automatically
set in the registry as described in Appendix
C, "Oracle 8i Configuration Parameters and the Registry".
The Oracle Performance Monitor allows you to monitor only one database instance at a time. For this reason, the registry contains the following values:
Use OPERFCFG to change these values. See section "Modifying
Oracle for Windows NT Performance Monitor Parameters".
|
Note: Oracle Corporation recommends setting the security level on each of these registry values. |
To use Oracle 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-NT Oracle databases by changing
the Hostname registry value so it points to another computer specified
in the TNSNAMES.ORA file.
To access Oracle Performance Monitor:
Choose Start > Programs > Oracle - HOME_NAME > Database
Administration > Oracle Performance Monitor.
The Performance Monitor window appears with the Chart
View:
The Oracle Performance Monitor has four views you can choose
from the View menu:
| Additional
Information:
For complete information about the four views, see your Windows NT documentation. |
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 Performance Monitor.
To add objects to a view:
Below is the Add to Chart dialog box. Note the corresponding
dialog boxes for the other views are different.
All Oracle8i system resources that can be monitored
through Oracle Performance Monitor begin with Oracle8i. These measures
are defined in ORACLE_BASE\ORACLE_HOME\DBS\ PERF.ORA. The
following table shows the Oracle8i objects and their associated
counters. For additional information on these objects, see Oracle8i
Tuning.
|
Note: You can only monitor one instance at a time using Oracle Performance Monitor on a given computer. |
| Object | Counter | Description |
|---|---|---|
|
Oracle8i Buffer Cache |
phyrds/gets % |
The percentage of phyrds/gets is calculated as a Miss ratio. If the Miss counter is higher than 30% to 40%, increase the number of buffers in the buffer cache to improve performance. To make the buffer cache larger, increase the value of the DB_BLOCK_BUFFERS initialization parameter. This value is not time-derived. |
|
Oracle8i 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 SHARED_POOL_SIZE. (See Oracle8i Tuning for more detailed information on tuning memory allocation in the Oracle8i database.) This value is not time-derived. |
|
Oracle8i 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. |
|
|
Oracle8i 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. |
|
Oracle8i 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. |
|
|
Oracle8i 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". |
|
|
Oracle8i Dynamic Space Management |
recursive calls/sec |
Dynamic extension causes Oracle8i to execute SQL statements in addition to those SQL statements issued by user processes. These SQL statements are called recursive calls. If Oracle8i 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 V$SYSSTAT. |
|
Oracle8i 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 V$WAITSTAT. 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. |
|
Oracle8i 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. |
|
Oracle8i 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 Oracle8i 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 Performance Monitor.
To resolve this problem:
Oracle8i Enterprise Edition 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 icons beside each event determine the type of event.
Oracle8i Enterprise Edition events display with a
source of Oracle.orcl. Oracle.orcl consists of the following event IDs:
To use the Event Viewer:
| Additional
Information:
See your Microsoft Windows NT documentation for more information on using the Windows NT Event Viewer. |
Setting AUDIT_TRIAL 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:
|
Warning: Audit information cannot be spooled to a file. The AUDIT_FILE_DEST parameter is not supported in Windows NT and should not be added to the INIT.ORA file. |
Oracle8i Enterprise Edition 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.
Oracle8i 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 Oracle8i
Enterprise Edition 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 SIDALRT.LOG and is found in
the directory specified by the BACKGROUND_DUMP_DEST parameter in the INIT.ORA
initialization parameter file. If the BACKGROUND_DUMP_DEST parameter is
not set, the SIDALRT.LOG file is generated in ORACLE_BASE\ADMIN\DB_NAME\BDUMP.
To view information on Oracle threads using the Oracle Administration Assistant for Windows NT:
