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 Event Viewer
  
  
• Trace Files
  
  
• Alert Files
  
  
• Viewing Threads

Database Monitoring Overview

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

Table 4-1 Database Monitoring Tools

Tool	Functionality
Oracle for Windows NT Performance Monitor	Monitor database objects, such as CPU usage, buffer cache, or a background process.
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 any Oracle thread.

See Also: Oracle9i Database Performance Guide and Reference for general tuning information

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 Windows NT Performance Monitor, except it has been preloaded with Oracle9i database performance elements.

On each computer, you can view behavior of objects, such as 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.

A set of counters is associated with each object. A counter is a unit of measurement used by Performance Monitor to display activity. The type of activity the counter measures is dependent upon 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.

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 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" in Oracle9i Database Getting Started for Windows for instructions on how to use 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 Hostname registry value so it points to another computer specified in file tnsnames.ora.

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 Performance Monitor displaying Chart View:

Figure 4-1 Oracle Performance Monitor Window

Text description of the illustration newperf.gif

Oracle for Windows NT Performance Monitor has four views you can choose from the View menu:

• Chart view displays database activity as it occurs.
  
  
• 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.

  See Also: Your Windows NT documentation for complete information about the four views

Modifying Views

For each view you can decide objects to monitor, what counters to use for each object, and graphical attributes of the view. 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. (This example uses Add to Chart, but other dialogs are similar.)

   

   Text description of the illustration perfmon.gif

   The Add to Chart dialog box appears.

2. In the Computer list box, select the computer to monitor.
   
   
3. In the Object list box, select the object or objects to monitor, then choose Add.

   If no data or Oracle9i objects appear, then either Oracle9i database is not running or an invalid host string or password has been entered. If the database is not started, then exit Oracle for Windows NT Performance Monitor, start the database, and restart Oracle for Windows NT Performance Monitor.

4. In the Counter list box, select one or more counters for each object you have selected. Note that the contents of the Counter box change depending upon your selection in the Object box. For details on how a counter works, highlight the counter and choose Explain.
   
   
5. In the Instance box, select an instance for this counter.
   
   
6. In the Color box, choose a color for the display of the selected counter.
   
   
7. In the Scale box, choose the scale at which to display the counter.
   
   
8. In the Width box, specify the width of the line on the graph.
   
   
9. In the Style box, choose a style for your graph line.
   
   
10. Click 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. This section lists Oracle9i objects and describes their associated counters. These measures are defined in

ORACLE_BASE\ORACLE_HOME\dbs\perf.ora

For additional information on these objects, see Oracle9i Database Performance Guide and Reference.

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

Oracle9i Buffer Cache

The counter is 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

The counter is redo log space requests. The value of this counter must be near zero. If this value increments consistently, then 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

The counter is 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 over this threshold while your application is running, then increase the amount of memory available to the data dictionary cache.

To increase the memory available to the cache, increase the value of initialization parameter SHARED_POOL_SIZE. (See Oracle9i Database Performance Guide and Reference for more detailed information on tuning memory allocation in Oracle9i database.) This value is not time-derived.

Oracle9i Library Cache

The counter is reloads/pins %. This is 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

The counters are phyrds/sec and phywrts/sec. These values are time-derived. Disk contention occurs when multiple processes try to access the same disk simultaneously. Depending on results from monitoring disk activity, corrective actions could include:

• Distributing I/O
  
  
• Separating datafiles and redo log files
  
  
• Separating tables and indexes
  
  
• Striping table data

Oracle9i DBWR stats1

The two counters available, buffers scanned/sec and LRU scans/sec, are helpful in tuning Buffer Cache. Buffers scanned/sec is the number of buffers DBWR scanned in each second. The buffers scanned are on the LRU (Least Recently Used) list. LRU scans/sec is the number of times DBWR scanned the (Least Recently Used) buffer list in each second.

Oracle9i DBWR stats2

The two counters available, timeouts/sec and checkpoints/sec, are helpful in determining how much work DBWR has been requested to perform. Timeouts/sec is the number of times DBWR timed-out in each second. DBWR is on a three second timeout interval. If DBWR has not been posted within a three second interval, then it times out.

Checkpoints/sec is the number of checkpoint messages processed by database writer in each second. Whenever a checkpoint occurs, DBWR must be messaged (posted) to "write dirty buffers to disk".

Oracle9i Dynamic Space Management

The counter is 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, then it may be necessary to determine the cause. Examine the recursive calls statistic through dynamic performance table V$SYSSTAT.

Oracle9i Free List

The counter is free list waits/requests %. Contention for free lists is reflected by contention for free data blocks in 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, then consider adding more free lists to reduce contention.

Oracle9i Sorts

The available counters are sorts in memory/sec and sorts on disk/sec. The default sort area size is adequate to hold all 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 increase 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, Alert, Log, Report) dialog box, then:

1. Ensure that Oracle9i database is running. If it is not running, then exit Oracle for Windows NT Performance Monitor, and start the database.
   
   
2. If the database is running but no data or Oracle9i objects appear, then check the error file for Oracle for Windows NT Performance Monitor:
   
   

   ORACLE_BASE\ORACLE_HOME\dbs\operf.log
   
   

   If the log file indicates an invalid host string or password, then check the registry for correct values for Hostname, Password, and Username. See "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet" in Oracle9i Database Getting Started for Windows for further information about these values.

3. Restart Oracle for Windows NT Performance Monitor.

Using Event Viewer

Oracle9i for Windows problems and other significant occurrences are recorded as events in an application event log. View and manage these recorded events in Event Viewer.

To access 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, Table 4-2 shows what is recorded in each column, and Table 4-3 interprets icons that appear at the left edge of the viewer.

Figure 4-2 Application View Window

Text description of the illustration ev_vwr.gif

Table 4-2 Application View Definitions

Column Name	Definition
Date	Date event took place
Time	Time event took place
Source	Application that recorded event
Category	Classification of event
Event	Unique number assigned to event
Computer	Computer name on which event occurred

Table 4-3  Event Viewer Icons

Icon	Event Type	Suggested Action
Red Stop Sign	Error	Always check these icons.
Lower-case "i" in Blue Circle	Information	Noncritical system events. Check these icons only to track a specific event.
Exclamation Point in Yellow Circle	Warning	Special events, such as instance termination or services shutdown. Investigate these icons, but they are usually noncritical.

Reading Event Viewer

Oracle9i for Windows NT events display with a source of Oracle.orcl.

Event number 34 specifies an audit trail event. These events are recorded if parameter AUDIT_TRAIL is set to db (true) or os in the initialization parameter file. Option os enables system wide auditing and causes audited records to be written to Event Viewer. Option db enables system wide auditing and causes audited records to be written to the database audit trail (table SYS.AUD$). Some records, however, are written to Event Viewer.

Event numbers other than 34 specify general database activities, such as an instance being started or stopped.

When you double-click an icon in Event Viewer, the Event Detail dialog appears with more information about the selected event. Figure 4-3, for example, shows details about EventID 1011. In the Description text box you will find a text description of the event. In the Data text box you can choose Bytes to see the information in hexadecimal format or Words to see DWORDS for the same data.

Figure 4-3 Event Detail Dialog

Text description of the illustration ev_dtl.gif

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

Managing Event Viewer

Setting AUDIT_TRAIL to db or os causes more records to be written to Event Viewer. This can fill up the Event Viewer log file. Follow these procedures 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. Click OK.

   You are returned to Event Viewer.

   Caution: Audit information cannot be spooled to a file. Parameter AUDIT_FILE_DEST is not supported in Windows and should not be added to the initialization parameter file.

Trace 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 parameter BACKGROUND_DUMP_DEST is set in the initialization parameter file. If BACKGROUND_DUMP_DEST is set, then trace files are stored in the directory specified. If the parameter is not set, then trace files are stored in directory ORACLE_BASE\ADMIN\db_name\bdump.

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 is the name of the instance.

Trace files are also created for user threads if parameter USER_DUMP_DEST is set in the initialization parameter file. Trace files for user threads have the form oraxxxxx.trc, where xxxxx is a 5-digit number indicating the Windows thread ID.

Alert Files

Alert files contain important information about error messages and exceptions that occur during database operations. Each Oracle9i for Windows 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 parameter BACKGROUND_DUMP_DEST in the initialization parameter file. If parameter BACKGROUND_DUMP_DEST is not set, then file SIDALRT.LOG is generated in ORACLE_BASE\admin\db_name\bdump. Alert files should be deleted or archived periodically.

Viewing Threads

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

1. Choose Start > Programs > Oracle - HOME_NAME > Configuration and Migration Tools > Administration Assistant for Windows NT.
   
   
2. Right-click the SID, where SID is a specific instance name, such as orcl.
   
   
3. Choose Process Information.

   The Process Information dialog appears, listing name, type, user, thread ID, and CPU usage for each Oracle thread.

4. To terminate a thread, select it and choose Kill Thread.

   

   Text description of the illustration procinfo.gif