Using the Oracle Performance Analyzer

Oracle Performance Analyzer creates an ADDM task that analyzes database activity between two points in time (represented by AWR snapshots) and displays the results. Sufficient database activity is required to obtain any tuning recommendations and often this requires many minutes of run time. There are different ways to invoke Oracle Performance Analyzer, depending on the length of time you plan to analyze.

This section covers the following topics:

Analyzing Performance Over Short Periods of Time

Oracle Performance Analyzer includes a timer that automatically creates AWR snapshots and an ADDM task and displays the results. This is useful for shorter periods of time where Visual Studio can remain open during the entire analysis. This is also the simplest way to analyze performance and is a good starting point in learning about Oracle performance tuning features.

To use the Oracle Performance Analyzer timer, perform the following steps:

  1. Select a SYSDBA database connection in Server Explorer that represents the database to be tuned. Right-click, and from the menu, select the Oracle Performance Analyzer to launch the designer.
  2. Start the application that accesses the database to be tuned. This can be any application running in Visual Studio, any executable running outside of Visual Studio, any application running in a web server, and so on.

    Note:

    In most cases, you should perform this step (start the application) before Step #3 (running the Oracle Performance Analyzer) to allow the application time to connect and do other start-up activities. These activities are generally not of interest when doing performance tuning. However, if your application executes SQL that runs immediately after the application begins, you may wish to run Oracle Performance Analyzer to make sure that the SQL is tuned. If so, perform Step #3 before Step #2.

  3. Enter the amount of time for the analyzer to run, in hours and minutes, and click Start. The analyzer requires at least 5 minutes of database time to generate any results. Database time is a indicator of the total database workload. It represents the total time spent in database calls. Generally, database time is a small fraction of the actual Performance Analyzer run-time.

    A status message appears, showing the time before the analysis completes.

  4. When the analysis is complete, select the root of the Performance Analyzer tree, which populates the details side of the Performance Analyzer tab.
  5. Click on each finding in the tree to view its details. Findings can have one or more recommendations. Click on recommendation nodes to view them. These recommendations may contain actions that you can take to resolve issues. Click on action nodes to view them.

    If there is insufficient database activity to successfully analyze performance, a message appears in the Additional Information section indicating this. This message also tells you the actual database time that was used. Repeat the test with greater database activity and longer run-time, or both.

  6. Click Tune SQL to display recommendations, if the Tune SQL control appears.

    The Tune SQL control only appears when findings suggest that you should run SQL tuning advisor.

  7. Read the recommendations in the Tune SQL tab.

    Recommendations of type STATISTICS or SQL PROFILE activate the Implement Recommendation and Preview SQL controls.

  8. Click Implement Recommendation to perform the recommendations or click Preview SQL to view the SQL that implements the recommendation.

Analyzing Performance Over Longer Periods of Time

If the period of time being analyzed is so long that Visual Studio may need to be closed, or for more flexibility, you can create AWR Snapshots and ADDM Tasks manually. Manually created AWR snapshots can also be configured to include more statistics than the default.

To analyze performance by manually creating AWR snapshots and ADDM tasks, perform the following:

  1. Start the application that accesses the database to be tuned. This can be any application running in Visual Studio, any executable running outside of Visual Studio, any application running in a web server, and so on.

    Note:

    In most cases, you should perform this step (start the application) to allow the application time to connect and do other start up activities. These activities are generally not of interest when doing performance tuning. However, if your application executes SQL that runs immediately after the application begins, you may wish to create a snapshot to make sure that the SQL is tuned. If so, perform Step #2 before Step #1.

  2. Select a SYSDBA database connection in Server Explorer that represents the database to be tuned.
  3. Right-click on the AWR Snapshots node and select New AWR Snapshot from the menu.
  4. In the New AWR Snapshot Dialog, select Typical or All for snapshot level depending on the depth of analysis you wish to achieve. Then press OK. At this point, you may close Visual Studio if desired.
  5. After the time period being analyzed has elapsed, perform steps 2-5 again to create a second new snapshot. Note: The analyzer requires at least 5 minutes of database time to generate any results. Database time is a indicator of the total database workload. It represents the total time spent in database calls. Generally, database time is a small fraction of the actual Performance Analyzer run-time.
  6. Right-click on the ADDM Tasks node and select New ADDM Task from the menu.
  7. In the New ADDM Task Dialog, from the lists, choose the snapshot you created as the Begin time and choose the second snapshot you created as the End time. Then Press OK.

    Oracle Performance Analyzer launches, displaying the results of the analysis.

  8. Select the root of the Performance Analyzer tree, which populates the details side of the Performance Analyzer tab.
  9. Click on each finding in the tree to view its details. Findings can have one or more recommendations, which may contain actions that you can take to resolve issues. Click on the recommendation nodes to view them, and click on any action nodes to view them.

    If there is insufficient database activity to successfully analyze performance, a message appears in the Additional Information section indicating this. This message also tells you the actual database time that was used. Wait for a longer period of time and/or increase database activity. Then return to step 2 to create another snapshot and another ADDM Task.

  10. Click Tune SQL to display recommendations, if the Tune SQL control appears.

    The Tune SQL control only appears when findings suggest that you should run SQL Tuning Advisor.

  11. Read the recommendations in the Tune SQL tab.

    Recommendations of type STATISTICS or SQL PROFILE activate the Implement Recommendation and Preview SQL controls.

  12. Click Implement Recommendation to perform the recommendations or click Preview SQL to view the SQL that implements the recommendation.

Viewing Past Performance Analyzer Results

Oracle database stores all performance tuning results in the database as ADDM Tasks. These are available in the ADDM Tasks Server Explorer collection.

To view the results of past Performance Analyzer results, do the following:

  1. In Server Explorer, select a SYSDBA database connection that represents the database that was tuned.
  2. Expand the ADDM Tasks node to reveal individual ADDM Tasks.
  3. Double-click the ADDM Task of interest, to launch Oracle Performance Analyzer and display the tuning results.

See Also:

Starting the Oracle Performance Analyzer