Oracle8 Enterprise Edition Getting Started 
Release 8.0.5 for Windows NT 

Prev Next

Monitoring a Database

This chapter describes how to monitor Oracle8 Enterprise Edition.

Specific topics discussed are:

Database Monitoring Overview

The following tools enable you to monitor your Oracle8 database:

This Tool  Enables You To... 

Oracle8 Performance Monitor 

Monitor database objects, such as CPU usage, buffer cache, and background processes. 

Event Viewer 

Monitor database events. 

Trace Files 

Record occurrences and exceptions of database operations. 

Alert Files 

Record important information about error messages and exceptions during database operations. 

Oracle Enterprise Manager's Performance Management Packs 

Monitor and tune using tools with real-time graphical performance information. 


Each tool is described in the following sections.

Additional Information:  

See Oracle8 Tuning for general tuning information and your operating system documentation for additional information on Windows NT Performance Monitor results and optimizing database performance. 


Using Oracle8 Performance Monitor

Oracle8 Performance Monitor is a graphical tool for measuring the performance of Oracle8 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 Oracle8 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.


To use Oracle8 Performance Monitor to monitor and analyze Oracle8 database performance, you must first install Oracle8 Performance Utility. The Oracle8 Performance Utility enables the Windows NT Performance Monitor to monitor Oracle8 database objects.

Registry Information

When you install Oracle8 Performance Utility, values are automatically set in the registry as described in Appendix C, "Configuration Parameters and the Registry".

The Oracle8 Performance Monitor allows you to monitor only one database instance at a time. For this reason, the registry contains the following values:

To use Oracle8 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.

Accessing Oracle8 Performance Monitor

To access Oracle8 Performance Monitor:

Choose Start > Programs > Oracle for Windows NT - [HOME_NAME] > Oracle8 Performance Monitor.

The Performance Monitor window appears with the Chart View:


The Oracle8 Performance Monitor has four views you can choose from the View menu:

View  Description 

Chart View 

Displays database activity in real-time. 

Alert View 

Lets you know when certain minimum performance criteria are not being met, or maximum criteria are being exceeded. 

Log View 

Maintains continuous records on performance. 

Report View 

Saves information about specific criteria. 


Additional Information:  

For complete information about the four views, see your Windows NT documentation. 


Monitoring Oracle8 Objects

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 Oracle8 Performance Monitor.

To add objects to a view:

  1. Choose Add To (Chart, Alert, Log, Report) from the Edit menu.
  2. 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.

  3. Select the objects you want to monitor, then click Add.
  4. Below are the elements of the Add to Chart dialog box. The other views' dialog boxes have similar features.

    Element  Description 

    Computer list box 

    Specify the computer you want to monitor. 

    Object drop-down list box 

    Select an object to monitor.  

    Note: If no data or Oracle8 objects appear, either the database is not running, or an invalid host string or password has been entered. If the database is not started, exit Oracle8 Performance Monitor, start the database, and restart Oracle8 Performance Monitor. 

    Counter list box 

    Select a counter (or multiple counters) for the object you have selected. Note that the contents of the Counter box change depending upon your selection in the Object box.  

    If you want details on how a counter works, highlight the counter and choose Explain. 

    Instance box 

    Select an instance for this counter. 

    Color box 

    Choose a color for the display of the selected counter. 

    Scale box 

    Choose the scale at which you want to display the counter. 

    Width box 

    Specify the width of the line on the graph. 

    Style box 

    Choose a different style for your graph line. 


  5. Click Done when you are finished.
  6. The selections you have chosen to monitor are displayed.

Understanding Oracle8 Performance Objects

All Oracle8 system resources that can be monitored through Oracle8 Performance Monitor begin with Oracle8. These measures are defined in ORACLE_HOME\DBS\ PERF80.ORA. The following table shows the Oracle8 objects and their associated counters. For additional information on these objects, see Oracle8 Tuning.


You can only monitor one instance at a time using Oracle8 Performance Monitor on a given computer. 

Object  Counter  Description 

Oracle8 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. 

Oracle8 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 Oracle8 Tuning for more detailed information on tuning memory allocation in the Oracle8 database.)  

This value is not time-derived. 

Oracle8 Data Files 

  • phyrds/sec 
  • phywrts/sec 

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: 

  • distributing I/O 
  • separating data files and redo log files 
  • separating tables and indexes 
  • striping table data 

These values are time-derived. 

Oracle8 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. 

Oracle8 DBWR stats1 


These counters are helpful in tuning the Buffer Cache. 


  • buffers scanned/sec 

Buffers scanned/sec is the number of buffers the DBWR scanned per second. The buffers scanned are on the LRU list. 


  • LRU scans/sec 

LRU_scans/sec is the number of times the DBWR scanned the Least Recently Used buffer list per second. 

Oracle8 DBWR stats2 


These counters are helpful in determining how much work the DBWR has been requested to perform. 


  • timeouts/sec 

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 

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". 

Oracle8 Dynamic Space Management 

recursive calls/sec 

Dynamic extension causes Oracle8 to execute SQL statements in addition to those SQL statements issued by user processes. These SQL statements are called recursive calls. If Oracle8 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. 

Oracle8 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. 

Oracle8 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. 

Oracle8 Sorts 

  • sorts in memory/sec 
  • sorts on disk/sec 

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. 


Oracle8 Performance Monitor Troubleshooting Information

If no data or Oracle8 objects appear in the Objects list of the Add to Chart dialog box, either:

The OPERF80.LOG file located in ORACLE_HOME\DBS contains error messages about Oracle8 Performance Monitor.

To resolve this problem:

  1. Check the ORACLE_HOME\DBS\OPERF80.LOG file for error messages.
  2. Resolve the problem as follows:
  3. Restart Oracle8 Performance Monitor.

Using the Event Viewer

Oracle8 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.

Accessing the Event Viewer

To access the Event Viewer:

  1. Choose Start > Programs > Administrative Tools > Event Viewer.
  2. The Event Viewer window appears.

  3. Choose Application from the Log menu.
  4. The Application view displays the following information:

Reading the Event Viewer

The icons beside each event determine the type of event.

Icon  Event Type  Description 

red (stop sign) 


Indicates an error. Always check these icons. 

blue (informational) 


Indicates a non-critical system event. You can ignore these icons unless you want to track a specific event. 

yellow (exclamation point) 


Indicates a special event, such as the termination of an instance or the shutdown of services. Investigate these icons, but they are usually non-critical. 


Oracle8 Enterprise Edition events display with a source of Oracle80.orcl. Oracle80.orcl consists of the following event IDs:

Event ID  Description 

IDs other than 34 

Specifies general database activities, such as an instance being started or stopped. 


Specifies an audit trail event. These events are recorded if the AUDIT_TRAIL parameter is set to DB (TRUE) or OS in the INITSID.ORA file.  

The OS option enables system-wide auditing and causes audited records to be written to the Event Viewer.  

The DB option enables system-wide auditing and causes audited records to be written to the database audit trail (the SYS.AUD$ table). Some records, however, are written to the Event Viewer. 


Using the Event Viewer

To use the Event Viewer:

  1. Look at the icons.
  2. Double-click an icon to analyze (especially red icons).
  3. The Event Detail dialog box appears with more information about the selected event:

Additional Information:  

See your Microsoft Windows NT documentation for more information on using the Windows NT Event Viewer. 


Managing the 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:

  1. Choose Log Settings from the Log menu.
  2. The Event Log Settings dialog box appears.

  3. Adjust the setting in the Maximum Log Size field to an appropriate level.
  4. Click OK.
  5. You are returned to the Event Viewer.

Using Trace and Alert Files

Oracle8 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 INITSID.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_HOME\RDBMS80\TRACE directory.

Oracle8 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 Oracle8 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 INITSID.ORA initialization parameter file. If the BACKGROUND_DUMP_DEST parameter is not set, the SIDALRT.LOG file is generated in ORACLE_HOME\RDBMS80\TRACE.

Using Performance Management Packs

Oracle Corporation offers three optional performance management packs that are integrated into Oracle Enterprise Manager:

The three packs together offer a powerful set of tools for monitoring performance, analyzing performance findings, and implementing improvements in your Oracle8 database environment. These packs are not on your Oracle8 Enterprise Edition for Windows NT CD-ROM. They must be purchased separately.

Oracle Diagnostics Pack

Oracle Diagnostics Pack consists of the following tools:

Oracle Performance Manager

Oracle Performance Manager is a tool for monitoring database performance in real time. It provides dozens of predefined charts for displaying a wide variety of database performance statistics regarding:

  • users 
  • buffers 
  • throughput 
  • caches 
  • tablespaces 
  • I/O 
  • redo logs 



Additional Information:  

See Oracle Enterprise Manager Performance Monitoring and Planning Guide for specific information on using this tool. 


Oracle Capacity Planner

Oracle Capacity Planner provides you with a comprehensive set of database and operating system statistics that can be collected. These collections are completely customizable, enabling you to select any set of statistics to collect and the interval at which they can be sampled.

Additional Information:  

See Oracle Enterprise Manager Performance Monitoring and Planning Guide for specific information on using this tool. 


Oracle Advanced Events

Oracle Advanced Events enables you to automatically monitor and detect problems on remote servers that may not be accessible from a graphical monitor. Oracle Advanced Events features include autonomous database and node monitoring, user-defined event threshold and monitoring intervals, user-defined event sets, multifaceted event notification, and automated problem correction.

Additional Information:  

See Oracle Enterprise Manager Performance Monitoring and Planning Guide for specific information on using this tool. 


Oracle TopSessions

Oracle TopSessions enables you to monitor how connected sessions use database instance resources in real time. You can obtain an overview of session activity by displaying the top n sessions sorted by a statistic of your choice. For any given session, you can drill down for more detail. You can further customize the information you display by specifying manual or automatic data refresh, the rate of automatic refresh, and the number of sessions to display.

In addition to these monitoring capabilities, Oracle TopSessions provides a methodology for identifying and correcting certain database performance problems. For example, when a sudden file I/O load is detected, you can first identify the sessions contributing to most of the problem, and then isolate the executing SQL statements in user applications for those sessions. You can then analyze the SQL explain plans for those SQL statements to determine how best to resolve the problem.

Additional Information:  

See Oracle Enterprise Manager Performance Monitoring and Planning Guide and Oracle Enterprise Manager Getting Started with Oracle TopSessions and Oracle Lock Manager for specific information on using this tool. 


Oracle Lock Manager

Oracle Lock Manager enables you to monitor locks, which are mechanisms that prevent destructive interaction between users accessing the same resource.

The main window displays a multi-column list that includes a row for each current lock in the database. The information displayed in the columns includes:

Oracle Trace

Oracle Trace is a general-purpose data collection product that captures data for specific software events, such as an application transaction, a user log on, or any event particular to the software product. With Oracle Trace, you can collect a wide variety of data, such as performance statistics, diagnostics data, system resource usage, and business transaction details.

Two Oracle Corporation products are currently enabled for Oracle Trace collection:

The Oracle8 Enterprise Edition performance data collected by Oracle Trace includes SQL statements, detailed statistics on SQL events, transactions events, and other useful information.

Oracle Trace provides a graphical Oracle Trace Manager application to create, schedule, and administer Oracle Trace collections for host products containing the Oracle Trace application programming interface (API). You can store data collected by Oracle Trace in Oracle8 database tables for access by SQL reporting tools and other products.

Oracle Trace has an API that contains data collection service calls. Software developers can use the Oracle Trace API to pre-configure their products for Oracle Trace data collection. Users of a product containing the Oracle Trace API calls, such as the Oracle8 database, can then automatically use Oracle Trace to collect data about specific events that occur in that product.

Most Oracle Trace users perform collections for products that already include the Oracle Trace API. Therefore, most users only need to be familiar with the data that can be collected for the host product and how to use the Oracle Trace Collection Manager application to create and administer data collections.

Additional Information:  

See Oracle Enterprise Manager Oracle Trace User's Guide for specific information on using this tool. 


Oracle Tuning Pack

Oracle Tuning Pack consists of the following tools:

Oracle Tablespace Manager

Oracle Tablespace Manager enables you to monitor and manage tablespace fragmentation. You can display an overview of table space usage information, either for all tablespaces in a database, or for the data file(s) within a tablespace.

To find out more about a given tablespace or data file, you can display how storage has been allocated for its segments. Clicking a segment in a segment list displays the extents in the tablespace or data file storing data for that segment. If data storage for a segment is fragmented, you can defragment it. You can also use the coalescing feature of Oracle Tablespace Manager to join adjacent free blocks.

Additional Information:  

See Oracle Enterprise Manager Performance Monitoring and Planning Guide and Oracle Enterprise Manager Getting Started with Oracle Tablespace Manager for specific information on using this tool. 


Oracle SQL Analyze

Oracle SQL Analyze examines your database SQL workload to determine which statements significantly impact performance. Data for all open cursors is captured and evaluated dynamically based upon user-selected criteria, such as resource usage and I/Os per statement. Extensive details are provided for examining SQL statements, including:

Oracle Expert

Oracle Expert enables you to optimize the performance of your database environment. It assists you with the initial Oracle8 database configuration and the collection and evaluation of the performance characteristics of existing databases. Oracle Expert provides many advantages. Oracle Expert:

Oracle Expert also serves as:

Oracle Change Management Pack

The Oracle Change Management Pack consists of six applications:

These applications allow you to make complex changes to schema objects (like dropping a column from a table), track changes to schemas and databases over time, make copies of schemas or objects, and compare and synchronize schemas and databases.

Additional Information:  

See Oracle Enterprise Manager Getting Started with Oracle Change Management Pack for specific information on using this tool. 


Copyright © 1998 Oracle Corporation. 
All Rights Reserved.