Oracle Enterprise Manager Performance Monitoring User's Guide

Performance Monitoring Applications

Performance Monitoring Overview
Starting Performance Monitoring Applications
A Typical Performance Monitoring Scenario

This chapter introduces the optional performance monitoring applications of the Oracle Enterprise Manager. It covers the following topics:

Performance Monitoring Overview
Starting Performance Monitoring Applications
A Typical Performance Monitoring Scenario

Attention:

For information on the specific Oracle7 server releases supported by Oracle Enterprise Manager and Oracle performance monitoring applications, see the compatibility matrix in the Oracle Enterprise Manager Readme.

Performance Monitoring Overview

Administrators of complex, dynamic database environments must confront daily the large volume of data their systems generate. Analyzing and responding to this data can be very time consuming. Often, suboptimal solutions stem from the pressure for immediate results.

The performance monitoring applications described in this guide graphically depict database performance and storage statistics in real-time to aid in managing these environments. This optional group of tools is fully integrated with Oracle Enterprise Manager.

Oracle Performance Manager

Oracle Performance Manager is a flexible, extensible, and easy-to-use 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, throughput, tablespaces, redo logs, buffers, caches and I/O, for example. Oracle Performance Manager also allows you to record statistics and play them back at a later time.

You can display these statistics in a number of formats, including tables, line charts, area charts, horizontal and vertical bar charts, cube charts, and pie charts. Moreover, all chart formats can be displayed in either two or three dimensions. You can drill down for more detail on some predefined charts. You can display multiple charts in a single chart window, and choose which ones you want to display together. You can also customize many other aspects of a chart-----including its axes, scale, and legend, for example. Moreover, in 3D charts you can modify the X and Y angle rotation, depth, and shadowing of objects.

Because monitoring requirements vary significantly among environments and over time, Oracle Performance Manager also lets you design new charts to meet current and evolving needs. In fact, creating a chart is as easy as filling in a property sheet. You can link charts to enable drill down for more detail, and invoke Oracle TopSessions from a chart to find out how individual sessions are contributing to database performance.

Oracle Lock Manager

Oracle Lock Manager is a tool for monitoring locks in a database, particularly blocking and waiting sessions.

Oracle TopSessions

Oracle TopSessions is a tool for monitoring 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 choosing. For any given session, you can then 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 useful monitoring capabilities, Oracle TopSessions provides a methodology for identifying and correcting certain database performance problems. For example, when sudden file I/O load is detected, you can first identify the sessions contributing most to 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.

Oracle Tablespace Manager

Oracle Tablespace Manager is a tool for monitoring and managing database storage. You can display an overview of tablespace usage information, either for all of the tablespaces in a database, or for the datafile(s) within a tablespace. To find out more about a given tablespace or datafile, you can drill down to graphically display how storage has been allocated for its segments. Clicking on a segment in the segment list highlights the extents in the tablespace or datafile storing data for that segment. Clicking on an extent in the storage allocation graphic highlights the segment containing it. Should data storage for a table be fragmented, you can defragment it. You can also use the coalescing feature of Oracle Tablespace Manager to join adjacent free blocks.

Starting Performance Monitoring Applications

Oracle Performance Manager, Oracle Lock Manager, Oracle TopSessions, and Oracle Tablespace Manager can be started from the console or in standalone mode.:

Starting from the Console

To start these applications from the Oracle Enterprise Manager Console, first select a database instance in the Oracle Enterprise Manager navigator tree with which a connection will be established. Then, you can proceed in one of two ways:

From the Performance Pack launch palette, click on the appropriate application icon.
From the Tools menu, select Performance Pack, and then the application name.

Starting in Standalone Mode

You can also launch a performance monitoring application as a separate executable in standalone mode, just as you would launch any other Windows application.

The standard Login Information dialog box for Oracle Enterprise Manager applications is displayed. This dialog box contains the following:

User Name

Enter your Oracle username for the database to which you are connecting.

Password

Enter your Oracle password for the database to which you are connecting.

Service

Enter the SQL*Net service name for the database to which you are connecting.

This service name becomes part of the title for each window associated with that connection. If you do not specify a service name, the title of each window associated with the connection begins with the word "Untitled." This scheme distinguishes windows associated with different connections.

Connect As

Click on this combo box to select the permission level of your login. Options include: NORMAL (default), SYSOPER, and SYSDBA.

Use As Preference

Check this box to save the username, password and service you have entered for automatic display the next time the Login Information dialog box is invoked.

OK

Click on this button to initiate a connection and display the main window of the application.

Cancel

Click on this button to exit the dialog box without connecting.

After either Oracle Lock Manager, Oracle TopSessions or Oracle Tablespace Manager have successfully connected to a database instance, the main window for that application appears. For information on the Oracle Lock Manager main window, see Using Oracle Lock Manager on page 8-1. For information on the Oracle TopSessions main window, see Using Oracle TopSessions on page 9-1. For information on the Oracle Tablespace Manager main window, see Using Oracle Tablespace Manager on page 10-1.

Starting Oracle Performance Manager in standalone mode, however, requires an extra step. See Starting Oracle Performance Manager on page 2-2 for more information.

A Typical Performance Monitoring Scenario

The following scenario illustrates how you can use Oracle Enterprise Manager performance monitoring applications to monitor database performance and solve performance problems.

Suppose, for example, that you are the DBA for an accounting firm with two Oracle7 databases running in remote locations. Several hundred user sessions typically connect to each database.

While calls from users often alert you to performance problems, using Oracle Enterprise Manager and these optional performance monitoring tools, you can now isolate and resolve problems proactively. Each morning, you establish Oracle Enterprise Manager connections to the two remote databases. Using these connections, you then start Oracle Performance Manager to monitor real-time database performance.

As you have found the Oracle Performance Manager predefined Overview chart particularly useful, you usually display this chart for each database in two background windows on your management console.

One morning, a remote user calls to complain of slow response time when entering general ledger data at one of the remote database sites. A quick glance at the corresponding Overview chart confirms the existence of a problem: you find a number of large spikes in physical writes per second on the File I/O Rate section of the Overview chart. Drilling down to the File I/O Rate Details chart, you are able to identify the particular datafile experiencing the increase in File I/O Rate activity.

Given that dozens of user sessions access the general ledger at any one time, it would be useful to identify the session(s) causing the file access problems. To do so, you establish an Oracle TopSessions connection to the database and display the top 10 sessions, based on file I/O activity. Selecting the session with greatest file I/O activity, you drill down for more detail, and then review the SQL statement currently executing for that session, as well as the explain plan for that statement. A quick review of the explain plan shows that a sequential search routine employs indexing improperly.

While fixing the SQL code is the long-term solution, you decide to use the Oracle Enterprise Manager Instance Manager application to disconnect the problem session. For information on using Instance Manager, see the Oracle Enterprise Manager Administrator's Guide.

Copyright © 1996 Oracle Corporation.
All Rights Reserved.