6 Monitoring a Database on Windows

Registry Information

When you install Oracle Counters for Windows Performance Monitor, values are automatically set in the registry as described in Chapter 15, "Configuration Parameters and the Registry".

Oracle Counters for Windows 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 to change these values. Oracle recommends setting the security level on each of these registry values.

To use Oracle Counters for Windows 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 installations of Oracle Database by changing Hostname registry value so it points to another computer specified in file tnsnames.ora.

Accessing Oracle Counters for Windows Performance Monitor

To access Oracle Counters for Windows Performance Monitor:

From the Start menu, select Programs, then select Oracle - HOME_NAME, then select Configuration and Migration Tools and then select Oracle Counters for Windows Performance Monitor.

Oracle Counters for Windows 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 operating system 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 Counters for Windows 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.)

   The Add to Chart dialog appears.

2. In the Computer list, select the computer to monitor.

3. In the Object list, select the object or objects to monitor, then click Add.

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

4. In the Counter list, select one or more counters for each object you have selected. Note that the contents of the Counter list change depending upon your selection in the Object list. For details on how a counter works, highlight the counter and click Explain.

5. In the Instance list, select an instance for this counter.

6. In the Color list, choose a color for the display of the selected counter.

7. In the Scale list, choose the scale at which to display the counter.

8. In the Width list, specify the width of the line on the graph.

9. In the Style list, 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 Database Performance Objects

All Oracle Database system resources that can be monitored through Oracle Counters for Windows Performance Monitor begin with 'Oracle Database'. This section lists Oracle Database objects and describes their associated counters. These measures are defined in:

ORACLE_HOME\dbs\PERFver.ora

Oracle Database 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.

Oracle Database 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.

Oracle Database 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, 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. This value is not time-derived.

Oracle Database 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.

Oracle Database Datafiles

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

Oracle Database 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.

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

Oracle Database Dynamic Space Management

The counter is recursive calls/sec. Dynamic extension causes Oracle Database to execute SQL statements in addition to those SQL statements issued by user processes. These SQL statements are called recursive calls.

If Oracle Database makes excessive recursive calls while an application is, then it may be necessary to determine the cause. Examine the recursive calls statistic through dynamic performance table V$SYSSTAT.

Oracle Database 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.

Oracle Database 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 Counters for Windows Performance Monitor Troubleshooting Information

If no data or Oracle Database objects appear in the Objects list of the Add to (Chart, Alert, Log, Report) dialog, then:

1. Ensure that Oracle Database instance is up. If it is not, then exit Oracle Counters for Windows Performance Monitor, and start the database.

2. If the database instance is up but no data or Oracle Database objects appear, then check the error file for Oracle Counters for Windows Performance Monitor:

   ORACLE_HOME\dbs\OPERFver.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\Services" for further information about these values.

3. Restart Oracle Counters for Windows Performance Monitor.

Reading Event Viewer

Oracle Database for Windows events display with a source of Oracle.SID.

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 6-2, for example, shows details about EventID 1011. In the Description field you will find a text description of the event. In the Data field you can choose Bytes to see the information in hexadecimal format or Words to see DWORDS for the same data.

Figure 6-2 Event Detail Dialog

Description of "Figure 6-2 Event Detail Dialog"

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 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 supported on Windows to write XML format audit files when AUDIT_TRAIL is set to XML or XML,EXTENDED format and thus must be added to the initialization parameter file.