4
Monitoring a Database

This chapter describes how to monitor Oracle9i for Windows.

This chapter contains these topics:

• Database Monitoring Overview

• Using Oracle for Windows NT Performance Monitor

• Using the Event Viewer

• Using Trace and Alert Files

• Viewing Threads Using the Oracle Administration Assistant for Windows NT

Database Monitoring Overview

Table 4-1 describes tools that enable you to monitor your Oracle9i database:

Table 4-1 Database Monitoring Tools

This Tool...	Enables You To...
Oracle for Windows NT 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 Database Management Packs	Monitor and tune using tools with real-time graphical performance information. See Also: Your Oracle Enterprise Manager documentation set for more information
Oracle Administration Assistant for Windows NT	View information on or terminate Oracle threads.

Using Oracle for Windows NT Performance Monitor

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.

Registry Information

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:

• Hostname

• Username

• Password

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.

Accessing Oracle for Windows NT Performance Monitor

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:

Figure 4-1 Oracle Performance Monitor Window

Text description of the illustration ch3a.gif

The Oracle for Windows NT Performance Monitor has four views you can choose from the View menu. Table 4-2 describes these views:

Table 4-2 Oracle Performance Monitor Views

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.

Monitoring Oracle9i 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 Oracle for Windows NT Performance Monitor.

To add objects to a view:

1. Choose Add To (Chart, Alert, Log, Report) from the Edit menu.

   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.

   
   
   Text description of the illustration perfmon.gif
2. Select the objects you want to monitor, then choose Add.

   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 Oracle9i 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 Oracle for Windows NT Performance Monitor, start the database, and restart Oracle for Windows NT 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.

3. Choose Done when you are finished.

   The selections you have chosen to monitor are displayed.

Understanding Oracle Performance Objects

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.

Note: You can only monitor one instance at a time using Oracle for Windows NT Performance Monitor on a given computer.

Table 4-3 Oracle9i Objects and Counters

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 SHARED_POOL_SIZE. (See Oracle9i Database Performance Guide and Reference for more detailed information on tuning memory allocation in the Oracle9i database.) 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	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 datafiles and redo log files Separating tables and indexes Striping table data These values are time-derived.
Oracle9i 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 (Least Recently Used) list.
	LRU scans/sec	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	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."
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 V$SYSSTAT.
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 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.
Oracle9i 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.

Oracle for Windows NT Performance Monitor Troubleshooting Information

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

• The database is not running

• An invalid host string or password has been entered in the registry

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:

1. Check the ORACLE_BASE\ORACLE_HOME\dbs\operf.log file for error messages.

2. Resolve the problem as follows:
   • If the log file indicates an invalid host string or password, check the registry for correct values for Hostname, Password, and Username. See HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet of the Oracle9i Database Getting Started for Windows for further information about these values.

   • If the database is not started, exit Oracle for Windows NT Performance Monitor and restart the database.

3. Restart Oracle for Windows NT Performance Monitor.

Using the Event Viewer

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.

Accessing the Event Viewer

To access the Event Viewer:

1. Choose Start > Programs > Administrative Tools > Event Viewer.

   The Event Viewer window appears.

2. Choose Application from the Log menu.

Figure 4-2 displays the Application view window.

Figure 4-2 Application View window

Text description of the illustration ch33.gif

Reading the Event Viewer

Table 4-4 describes the icons beside each event and the type of event.

Table 4-4 Event Viewer Icons

Icon	Event Type	Description
Red (stop sign)	Error	Indicates an error. Always check these icons.
Blue (informational)	Information	Indicates a noncritical system event. You can ignore these icons unless you want to track a specific event.
Yellow (exclamation point)	Warning	Indicates a special event, such as the termination of an instance or the shutdown of services. Investigate these icons, but they are usually noncritical.

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:

Table 4-5 Oracle.orcl Event IDs

Event ID	Description
IDs other than 34	Specifies general database activities, such as an instance being started or stopped.
34	Specifies an audit trail event. These events are recorded if the AUDIT_TRAIL parameter is set to db (true) or os in the init.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 Display More Information

To use the Event Viewer:

1. Look at the icons.

2. Double-click an icon to analyze (especially red icons).

   The Event Detail dialog box appears with more information about the selected event:

   
   
   Text description of the illustration ch34.gif

   See Also: Microsoft Windows NT documentation for more information on using the Windows NT Event Viewer

Managing the Event Viewer

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:

1. Choose Log Settings from the Log menu.

   The Event Log Settings dialog box appears.

2. Adjust the setting in the Maximum Log Size field to an appropriate level.

3. Choose OK.

   You are returned to the Event Viewer.

   Caution: 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.

Using Trace and Alert Files

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:

• sidDBWR.TRC

• sidSMON.TRC

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 sidALRT.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 sidALRT.LOG file is generated in ORACLE_BASE\admin\db_name\bdump. Alert files should be deleted or archived periodically.

Viewing Threads Using the Oracle Administration Assistant for Windows NT

To view information on Oracle threads using the Oracle Administration Assistant for Windows NT:

1. Choose Start > Programs > Oracle - HOME_NAME > Configuration and Migration Tools > Oracle Administration Assistant for Windows.

2. Right-click the sid, where sid is a specific instance name, such as orcl.

3. Choose Process Information.

4. View information on appropriate threads. If you want to terminate a thread, select the thread you want to terminate.

5. Choose Kill Thread.

   
   
   Text description of the illustration procinfo.gif