Diagnosing Performance Problems

At times database performance problems arise that require your diagnosis and correction. Sometimes problems are brought to your attention by users who complain about slow performance. Other times you might notice performance spikes in the Host CPU chart on the home page.

In all cases, these problems are flagged by the Automatic Database Diagnostics Monitor (ADDM), which does a top-down system analysis every hour by default and reports its findings on the Oracle Enterprise Manager Home page.

Viewing Performance Analysis

ADDM runs automatically every hour to coincide with the snapshots taken by Automatic Workload Repository (AWR). Its output consists of a description of each problem it has identified, and a recommended action.

Findings are displayed in two places on the home page:

Under the Performance Analysis section on the Database Home page, as shown in Figure 10-6.

You can drill down by clicking the finding. The Performance Findings Details page appears, describing the findings and recommended actions.
The Diagnostic Summary next to Performance Findings shows the number of findings, if any. Click this link to go to the ADDM page.

Figure 10-6 Performance Analysis

Description of "Figure 10-6 Performance Analysis"

Responding to Performance Findings

To respond to a performance finding, click the finding and follow the recommended actions, if any. A recommendation can include running an advisor.

For example, Figure 10-6 shows a performance finding of SQL statements consuming significant database time were found, with an impact of 100 percent and recommendation of SQL Tuning.

Clicking the finding link takes you to the Performance Finding Details page. Here the recommended action is to run the SQL Advisor, which you can do by clicking Run Advisor Now. The advisor runs and gives a recommendation in the form of precise actions for tuning the SQL statements for better performance.

Modifying Default ADDM Behavior

ADDM behavior and analysis is based on the Automatic Workload Repository (AWR), which collects system performance statistics and stores the data in the database. After default installation, the AWR captures data every hour in the form of a snapshot and purges data over seven days old. You can modify both the snapshot frequency and the data retention period as desired.

You can view and alter these settings on the Workload Repository page:

The retention period for snapshots. This is initially set to 7 days. AWR automatically purges any snapshot older than the retention period, with the exception preserved snapshots that are retained for ever. A preserved snapshot set is typically created to define a reference period for performance analysis.
The interval for snapshots. The default and recommended value is one hour.

To navigate to this page, from the Database Administration page, under Statistics Management, select Automatic Workload Repository.

To change either of these settings, click Edit on the Workload Repository page. The Edit Settings page appears. Enter a new Snapshot retention period or new System Snapshot Interval. Click OK.

Running ADDM Manually

By default Oracle runs ADDM every hour. Performance findings, if any, from the last snapshot are listed on the Oracle Enterprise Manager Home page. This is described in "Performance Self-Diagnostics: Automatic Database Diagnostics Monitor" .

You can also run ADDM manually. Reasons for doing so include running it as a recommended action associated with an alert, running it in the middle of a snapshot period, or running it across multiple snapshots.

Note:

If you need more frequent ADDM reporting, you can also modify the default snapshot interval. To do so, see "Modifying Default ADDM Behavior".

From the Home page, under Related links you can navigate to the ADDM page by clicking Advisor Central, then ADDM. The Run ADDM page appears.

Figure 10-7 is a screen shot of Run ADDM page.

Increased session activity is displayed as peaks in the graph.

To analyze a period across multiple snapshots:

Select Run ADDM to analyses past instance performance.
Select Period Start Time. Choose a starting snapshot under the graph.
By default, the end time is the last snapshot. To specify a different end time, select Period End Time. Choose an ending snapshot under the graph.
Click OK to start the analysis. The Automatic Database Diagnostic Monitor page appears detailing its findings and recommendations.

Figure 10-7 Run ADDM Page

This is a screen shot of the ADDM monitoring page.

Description of "Figure 10-7 Run ADDM Page"