1
Overview

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:

• Discovery and graphical representation of targets, such as databases or nodes, to be monitored
• Starting tools from the console in the context of a specific target
• Automated collection of performance and resource usage data
• Central monitoring and administration of remote systems using intelligent agents

The Diagnostics Pack provides the following components:

• Oracle Advanced Event Tests

  A set of predefined event tests built on the Oracle Event Management System offering "lights out" performance monitoring and problem solving for distributed systems.

• Oracle Performance Manager

  A real-time performance monitor, providing graphical views of an array of performance metrics for the targets you are monitoring. Performance Manager provides the ability to drill down to detailed data of performance diagnostics data to identify problems such as lock contention and excessive file I/O.

• Oracle Capacity Planner

  A tool for collecting, storing, and analyzing historical performance data for the targets you are monitoring. Capacity Planner allows you to use historical information to diagnose problems today and to anticipate resource needs to avoid problems in the future.

• Oracle Trace Data Viewer

  A comprehensive tracing mechanism for the Oracle Server. Oracle Trace captures SQL, transaction, and application-based workload and performance data.

• Oracle TopSessions charts

  A diagnostics tool that pinpoints problematic database sessions and displays detailed performance and resource consumption data.

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:

• A single performance monitoring solution that combines automated agent-based monitoring with real-time graphical charts and historical trend analysis, providing logical step-by-step methodology for discovering and investigating performance problems. This is accomplished by integrating the Advanced Events with the Performance Manager charts and Capacity Planner's historical data.
  • Events and real-time performance charts share common performance thresholds that are enabled from either Performance Manager or the Enterprise Manager console.
  • Event notifications can be investigated by launching real-time charts, historical data and diagnostic advice directly from the event viewer.
  • Real-time diagnostic charts incorporate event notification through chart threshold state indicators.
• Significantly expanded coverage of Agent-based database monitoring. Through the integration of the Agent event system with the data collection mechanism used by Performance Manager and Capacity Planner, the Advanced Events support lights out monitoring of many of the Oracle database metrics currently only available through real time charts or historical data analysis.
• An enhanced real-time diagnostics interface that builds on the new performance data organization and drilldowns introduced in the previous version of the Diagnostics Pack.
  • New organization of the Database Health overview diagnostics charts for easier performance assessment.
  • Integration of host operating system diagnostics data in the Database Health overview, providing a single monitoring point for databases and operating systems.
  • Chart threshold state indicators that provide immediate visual notification of performance status.
  • Drilldowns to diagnostics advice.
• Automated generation and Web publication of Performance Manager charts and Capacity Planner analysis reports. This allows administrators to set up status and performance reports that are periodically updated for access from a web server by other database administrators and system managers. This supports all targets covered by Performance Manager and Capacity Planner.
• New top activity diagnostics charts for finding and troubleshooting high impact sessions and SQL.
  • New filtering capabilities providing complete flexibility in searching for high impact activity, such as high cost SQL by object or type.
  • New top details provide graphical performance data to help diagnose a problem session or SQL statement
• Browser-based implementation of some Performance Manager functionality.
• Direct Connect Performance Manager that allows database monitoring with the Agent or OMS.

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:

• Free space available on selected disks
• Memory usage on selected nodes
• Cache utilization
• Throughput

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:

• Detecting a problem
• Diagnosing the cause of the problem
• Correcting the problem and preventing the problem from occurring again

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:

• You are alerted by your users that there is a problem, for example, the system is very slow
• You notice something yourself
• You get an alarm, page, or E-mail that the system is not working correctly or that a potential problem may exist

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
• Investigating resource usage
• Identifying problem sessions
• Identifying high impact SQL
• Analyzing past (historical) information

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:

• Processor, user, and privileged times across all processors
• Bytes available on each disk
• Current processor queue length
• Available and committed memory
• Counts of various objects on the system
• System I/O operations

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:

• What led up to the space problem? Was the space consumed gradually or all at once?
• Does the space problem still exist?
• Is the space problem getting any better?

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:

• Starting fixit jobs in the Enterprise Manager console

  Define an event to automatically run a fixit job when the event is triggered.

• Identifying and correcting resource-intensive sessions

  Either kill or block a session if the session is consuming too many resources.

• Identifying problematic SQL statements

  Use Performance Manager to drill down to a SQL chart and launch SQL Analyze to tune the problematic SQL statement.

• Analyzing space usage

  Add a datafile in a tablespace storage folder. You can also change the MAXEXTENTS of a table.

• Improving memory usage

  Analyze the buffer size DB_BLOCK_BUFFER parameter. You can also change the initialization parameters using the Instance Manager application.

• Tuning the database by using the Oracle Tuning Pack
• Reorganizing tablespaces using the Oracle Tuning Pack

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:

• Install the Diag Pack SQL Server component on the target (it must be selected at install time)
• Be sure you have 8.0.6 or higher NT Agent/DG running on the SQL*Server node
• Drop and rediscover, or refresh the node
• SQL Server targets will show up in the Console navigator and in Capacity Planner and Performance Manager