Oracle Enterprise Manager Getting Started with the Oracle Diagnostics Pack
Release 9.0.1

Part Number A88748-02


Book List



Master Index


Go to previous page Go to next page


The Oracle Diagnostics Pack extends Oracle Enterprise Manager to enable the monitoring, diagnosing, and capacity planning of the multitiered Oracle server environment. The Diagnostics Pack takes advantage of the following Enterprise Manager system management features:

The Diagnostics Pack provides the following components:

This chapter provides a brief overview of each of these components and gives a brief example of how to best use these components to solve database performance problems.

What's New in Version 9i

As part of Oracle Diagnostics Pack release 9i, Oracle offers the following list of new features:

Oracle Advanced Event Tests

In addition to the basic UpDown event tests provided for all targets administered in the Enterprise Manager console, the Diagnostics Pack provides a library of advanced event tests to automatically detect excessive resource use, performance degradation, and other problem situations.

See the Oracle Enterprise Manager Event Test Reference Manual for a complete list of these advanced event tests.

The key to a smooth running system is to resolve problems before they turn into crises. These advanced event tests allow you to focus on the causes of a problem before the symptoms become serious. In addition, with the paging and E-mail support provided by Enterprise Manager, you can be notified of any problems with the system wherever you happen to be.

Oracle Performance Manager

Performance Manager displays performance data in real-time graphical views that can be automatically refreshed at user-defined intervals. Multiple charts and tables can be displayed simultaneously, affording you a multifaceted view of system performance. For example, an Oracle server administrator can monitor resources consumed by the database, the underlying operating system, and other processes running on the node. Some of the predefined overview charts are:

Users can further drill down on these charts to see greater detail, and in some cases, drill down to problematic sessions and terminate them. Many other subsystem specific charts are available, giving you a rich set of data to analyze your system from multiple viewpoints.

See Chapter 3, "Introduction to Oracle Performance Manager" for more information about Oracle Performance Manager.

Oracle Capacity Planner

The same data collection mechanism used by Performance Manager to sample real-time data is also used to collect historical performance and resource consumption data. The Diagnostics Pack uses Capacity Planner to analyze performance data to help you diagnose problems using historical information and project your future capacity needs.

In Performance Manager, historical data can be gathered for the targets you monitor. The collection can be customized, allowing you to select any subset of statistics to collect and the interval at which these statistics should be sampled. The data collection mechanism provides roll up capabilities, where data is automatically aggregated at the end of each hour, day, week, or month. This data collection mechanism also allows you to set up data expiration policies so you can fully control the amount of storage needed for the historical data.

Once the data is gathered, you can use Capacity Planner to chart the historical data for the purposes of identifying trends and predicting future hardware requirements for the system. You can also extrapolate to a particular point in time or work back from a particular target value and find when a specific level of usage is expected to occur. These charts can be customized, allowing you to get the data you need and to view data from multiple sources (for example, CPU and disk) on a single, unified chart.

See Chapter 4, "Introduction to Oracle Capacity Planner" for more information about Capacity Planner.

Oracle Trace Data Viewer

Trace is a general-purpose tracing mechanism that collects data for any software product enabled with Trace Application Programming Interface (API) calls, such as the Oracle server. You can use Trace to collect a wide variety of data, such as performance statistics, diagnostic data, system resource usage, and business transaction details.

The major components of Trace are the Trace Manager and Trace Data Viewer. Trace Manager is an application used to create, schedule and administer Trace collections for products containing Trace API calls. Trace Manager automatically discovers Trace preconfigured products that are installed on all nodes that are known to the Oracle Management Server. By default Trace discovers any Oracle Server release 7.3.3 and higher. For more information about Oracle Trace, see the Oracle9i Database Performance Guide and Reference

Data Viewer allows you to view formatted data collected by Trace. Data Viewer handles the complex task of extracting data and aggregating key server performance metrics on a large Trace collection. Once you select a Trace collection, you can have Data Viewer compute SQL or Wait statistics or both.

See Chapter 7, "Using Oracle Trace Data Viewer" for more information about using Data Viewer.

Oracle TopSessions Charts

TopSessions is a chart that monitors 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, 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.

For more information about using the Top Sessions chart, see Chapter 6, "Using Oracle TopSessions".

Typical Scenario for Using the Diagnostics Pack

Monitoring, problem diagnosis, and correction can be broken down into three stages:

The following sections provide an example of how you can use the Diagnostics Pack to solve performance problems.

System Monitoring and Problem Detection

Problems can come to your attention in a number of ways:

Ideally an administrator would like to be alerted to a problem before his or her users are aware of the problem. The Advanced Event Tests included in the Diagnostics Pack allow you to proactively identify potential problems before they become serious and adversely affect users.

Diagnosing the Cause of the Problem

You can diagnose the cause of the problem by:

Examining the Current State of the System

Using Performance Manager, you can take a quick look at the system resources by accessing the Overview chart. This chart provides the following information:

Investigating Resource Usage

Using Performance Manager you can identify the processes using the most resources. The Process Info Chart found under the Nodes branch of the navigator tree, lists the Percent Memory Used. The Memory Statistics Chart found under the Databases branch of the navigator tree, lists memory statistics, for example, Shared Pool Size, Buffer Cache Size, and SGA Size.

Identifying Problem Sessions

Using Oracle TopSessions you can identify the top n sessions using the most CPU, memory, and I/O. You can also drill down into sessions statistics, for example, V$: sesstat, open_cursor, locks, and sqlarea.

You can also sort data based on a selected statistics filter. For example, you can sort the top 10 sessions for the Redo activity by redo blocks written, redo size, and redo entry volume.

Finally, you can view open cursors, explain plans, and sessions holding locks that other sessions are waiting for.

Identifying High Impact SQL

Using SQL Analyze, an application available in the Oracle Tuning Pack, you can select and sort the top n cached SQL statements based on the SQL volume metrics and SQL efficiency metrics. The SQL volume metrics include: number of executions, disk reads, and sorts. The SQL efficiency metrics include: buffer gets per executions, buffer gets per rows processed, and parse calls per executions.

Also using SQL Analyze, you can drag and drop SQL statements to the SQL Analyze workbench for tuning.

Analyzing Past Information

If a collection was set using Capacity Planner, you can determine what led up to the problem. For example, you can collect data to determine space usage and answer the following questions:

By using the trend analysis feature of Capacity Planner, you can determine when the disk will be full.

Correcting the Problem

Once you have diagnosed the problem, there are various actions you can take to resolve the problem. Some examples include:

Microsoft SQL Server Support

The Oracle Diagnostics Pack can monitor the performance of hosts, Oracle servers, Microsoft SQL Server, and other targets. In Performance Manager you can view real-time charts displaying performance and resource use of the Microsoft SQL Servers. In Capacity Planner you can collect and view historical data about your Microsoft SQL Servers. The Advanced Events contain an Up/Down test for Microsoft SQL Server. These capabilities are included as part of the Oracle Diagnostics Pack; no additional licenses are required.

This SQL Server monitoring support is provided as a plug-in to the Intelligent Agent on NT. Once you have installed this plug-in to the Intelligent Agent, you can discover SQL Servers as part of the normal Enterprise Manager discovery process. You will then be able to select SQL Servers from the navigator and view their performance. The metrics shown are contained in the NT performance registry as exposed by SQL Server.

To monitor SQL Server:

Go to previous page Go to next page
Copyright © 1996-2001, Oracle Corporation.

All Rights Reserved.


Book List



Master Index