Release 2.1
A76915-01
 Library |
 Product |
 Contents |
 Index |
| Oracle(R) Enterprise Manager Getting Started with the Oracle Diagnostics Pack Release 2.1 A76915-01 |
|
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:
A set of predefined event tests built on the Oracle Event Management System offering "lights out" performance monitoring and problem solving for distributed systems.
A real-time performance monitor, providing graphical views of an array of performance metrics for the services 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.
A tool for collecting, storing, and analyzing historical performance data for the services 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.
A comprehensive tracing mechanism for the Oracle Server. Oracle Trace captures SQL, transaction, and application-based workload and performance data.
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.
In addition to the basic UpDown event tests provided for all services 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 Chapter 6, "Oracle Advanced Event Tests" 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.
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.
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 services 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.
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 a graphical 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.
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.
Refer to the Oracle Trace User's Guide for additional information about Trace.
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, 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.
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.
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.
You can diagnose the cause of the problem by:
Using Performance Manager, you can take a quick look at the system resources by accessing the Overview chart. This chart provides the following information:
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.
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.
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.
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.
Once you have diagnosed the problem, there are various actions you can take to resolve the problem. Some examples include:
Define an event to automatically run a fixit job when the event is triggered.
Either kill or block a session if the session is consuming too many resources.
Use Performance Manager to drill down to a SQL chart and launch SQL Analyze to tune the problematic SQL statement.
Add a datafile in a tablespace storage folder. You can also change the MAXEXTENTS of a table.
Analyze the buffer size DB_BLOCK_BUFFER parameter. You can also change the initialization parameters using the Instance Manager application.
