Skip Headers
Oracle® Database 2 Day + Performance Tuning Guide
11g Release 1 (11.1)

Part Number B28275-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

6 Manual Database Performance Monitoring

You can run the Automatic Database Diagnostic Monitor (ADDM) manually to monitor current and historical database performance. Typically, you use the automatic diagnostic feature of ADDM to identify performance problems with the database. As described in Chapter 3, "Automatic Database Performance Monitoring", ADDM runs once every hour by default. It is possible to configure ADDM to run more or less frequently. However, in some cases you may want to run ADDM manually.

One reason for running ADDM manually is to analyze a time period longer than one ADDM analysis period. For example, you may want to analyze database performance in a full workday by analyzing 8 consecutive hours of activity. One technique is to analyze each of the individual ADDM analyses within this 8-hour period. However, this approach may become complicated if performance problems exist for only part of the 8-hour period, because they may appear in only some of the individual ADDM analyses. Alternatively, you can run ADDM manually with a pair of Automatic Workload Repository (AWR) snapshots that encompass the 8-hour period. In this case, ADDM will identify the most critical performance problems in the entire time period.

This chapter contains the following sections:

Manually Running ADDM to Analyze Current Database Performance

By default, ADDM runs every hour to analyze snapshots taken by AWR during this period. In some cases you may notice performance degradation that did not exist in the previous ADDM analysis period, or a sudden spike in database activity on the Performance page, as described in Chapter 4, "Monitoring Real-Time Database Performance". If the next ADDM analysis is not scheduled to run for 30 minutes, then you may want to run ADDM manually to identify and resolve the performance problem.

When you run ADDM manually, a manual AWR snapshot is created automatically. This manual run may affect the ADDM run cycle. For example, if you scheduled ADDM to run hourly at the start of each hour and the last ADDM run was at 8:00 p.m., running ADDM manually at 8:30 p.m. will cause the next scheduled ADDM run to start at 9:30 p.m., not 9:00 p.m. All subsequent ADDM runs will continue on the new run cycle, occurring hourly at the half-hour instead of the start of each hour.

To analyze current database performance by manually running ADDM:

  1. On the Database Home page, under Related Links, click Advisor Central.

    The Advisor Central page appears.

  2. Under Advisors, click ADDM.

    The Run ADDM page appears.

    Description of run_addm_now.gif follows
    Description of the illustration run_addm_now.gif

    In this example, CPU usage spiked in the last 10 minutes.

  3. Select Run ADDM to analyze current instance performance and click OK.

    The Confirmation page appears.

  4. Click Yes.

    The Processing: Run ADDM Now page appears while the database takes a new AWR snapshot.

    An ADDM run occurs for the time period between the new and the previous snapshot. After ADDM completes the analysis, the Automatic Database Diagnostic Monitor (ADDM) page appears with the results.

    Description of run_addm_now_results.gif follows
    Description of the illustration run_addm_now_results.gif

  5. Click View Report.

    The View Report page appears.

  6. Optionally, click Save to File to save the results of the ADDM task in a report for later access.

Manually Running ADDM to Analyze Historical Database Performance

You can run ADDM manually to analyze historical database performance by selecting a pair or range of AWR snapshots as the analysis period. This technique is useful when you have identified a time period in the past when database performance was poor.

In the Performance page, you can monitor historical performance by selecting Historical from the View Data list. In Historical view, you can monitor database performance in the past, up to the duration defined by the AWR retention period. If you notice performance degradation, then you can drill down to appropriate pages from the Performance page to identify historical performance problems with the database, as described in Chapter 4, "Monitoring Real-Time Database Performance". If you identify a problem, then you can run ADDM manually to analyze a particular time period.

To analyze historical database performance by manually running ADDM:

  1. On the Database Home page, under Related Links, click Advisor Central.

    The Advisor Central page appears.

  2. Under Advisors, click ADDM.

    The Run ADDM page appears.

  3. Select Run ADDM to analyze past instance performance.

  4. Specify a time period for analysis by selecting a pair of AWR snapshots. Complete the following steps:

    1. Select Period Start Time.

    2. Below the chart for the starting snapshot, click the snapshot you want to use for the start time.

      A play icon (displayed as an arrow) appears over the snapshot icon.

      In this example, database activity peaked from 10 p.m. to 11 p.m., so the snapshot taken at 10:00 p.m. is selected for the start time.

    3. Select Period End Time.

    4. Below the chart for the ending snapshot, click the snapshot you want to use for the end time.

      A stop icon (displayed as a square) appears over the snapshot icon.

      In this example, the snapshot taken at 10:00 p.m. is selected.

    Description of run_addm_past.gif follows
    Description of the illustration run_addm_past.gif

  5. Click OK.

    After ADDM completes the analysis, the Automatic Database Diagnostic Monitor (ADDM) page appears with the results of the ADDM run.

    Figure 6-1 Analyzing Historical Database Performance

    Description of Figure 6-1 follows
    Description of "Figure 6-1 Analyzing Historical Database Performance"

  6. Click View Report.

    The View Report page appears.

  7. Optionally, click Save to File.

Accessing Previous ADDM Results

If you ran ADDM manually to analyze current or historical database performance, the results are displayed on the Automatic Database Diagnostic Monitor (ADDM) page after the ADDM run has completed.

You can access the ADDM results at a later time, or access the ADDM results from previous executions.

To access the ADDM results:

  1. On the Database Home page, under Related Links, click Advisor Central.

    The Advisor Central page appears.

  2. Complete the following steps:

    1. Under Advisor Tasks, select ADDM from the Advisory Type list.

    2. Select the appropriate search criteria.

      For example, you can select All in the Advisor Runs list to view all ADDM tasks.

    3. Click Go.

    Description of advisor_tasks_addm.gif follows
    Description of the illustration advisor_tasks_addm.gif

    The ADDM tasks are displayed under Results.

  3. To view an ADDM result, select the desired ADDM task and click View Result.

    The results from the selected ADDM task are shown in the Automatic Database Diagnostic Monitor (ADDM) page.