6
Automatic Performance Diagnostics

This chapter describes Oracle automatic features for performance diagnosing and tuning.

This chapter contains the following topics:

• Introduction to Database Diagnostic Monitoring
• Automatic Database Diagnostic Monitor

  See Also: Oracle Database 2 Day DBA for information on monitoring, diagnosing, and tuning the database, including Oracle Enterprise Manager Interfaces for using the Automatic Database Diagnostic Monitor

Introduction to Database Diagnostic Monitoring

When problems occur with a system, it is important to perform accurate and timely diagnosis of the problem before making any changes to a system. Often a database administrator (DBA) simply looks at the symptoms and immediately starts changing the system to fix those symptoms. However, long-time experience has shown that an initial accurate diagnosis of the actual problem significantly increases the probability of success in resolving the problem.

For Oracle systems, the statistical data needed for accurate diagnosis of a problem is saved in the Automatic Workload Repository (AWR). The Automatic Database Diagnostic Monitor (ADDM) analyzes the AWR data on a regular basis, then locates the root causes of performance problems, provides recommendations for correcting any problems, and identifies non-problem areas of the system. Because AWR is a repository of historical performance data, ADDM can be used to analyze performance issues after the event, often saving time and resources reproducing a problem. See "Automatic Workload Repository".

An ADDM analysis is performed every time an AWR snapshot is taken and the results are saved in the database. You can view the results of the analysis using Oracle Enterprise Manager or by viewing a report in a SQL*Plus session.

In most cases, ADDM output should be the first place that a DBA looks when notified of a performance problem. ADDM provides the following benefits:

• Automatic performance diagnostic report every hour by default
• Problem diagnosis based on decades of tuning expertise
• Time-based quantification of problem impacts and recommendation benefits
• Identification of root cause, not symptoms
• Recommendations for treating the root causes of problems
• Identification of non-problem areas of the system
• Minimal overhead to the system during the diagnostic process

It is important to realize that tuning is an iterative process and fixing one problem can cause the bottleneck to shift to another part of the system. Even with the benefit of ADDM analysis, it can take multiple tuning cycles to reach acceptable system performance. ADDM benefits apply beyond production systems; on development and test systems ADDM can provide an early warning of performance issues.

Automatic Database Diagnostic Monitor

The Automatic Database Diagnostic Monitor (ADDM) provides a holistic tuning solution. ADDM analysis can be performed over any time period defined by a pair of AWR snapshots taken on a particular instance. Analysis is performed top down, first identifying symptoms and then refining them to reach the root causes of performance problems.

The goal of the analysis is to reduce a single throughput metric called DB time. DB time is the cumulative time spent by the database server in processing user requests. It includes wait time and CPU time of all non-idle user sessions. DB time is displayed in the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.

Note that ADDM does not target the tuning of individual user response times. Use tracing techniques to tune for individual user response times. See "End to End Application Tracing".

By reducing DB time, the database server is able to support more user requests using the same resources, which increases throughput. The problems reported by the ADDM are sorted by the amount of DB time they are responsible for. System areas that are not responsible for a significant portion of DB time are reported as non-problem areas.

The types of problems that ADDM considers include the following:

• CPU bottlenecks - Is the system CPU bound by Oracle or some other application?
• Undersized Memory Structures - Are the Oracle memory structures, such as the SGA, PGA, and buffer cache, adequately sized?
• I/O capacity issues - Is the I/O subsystem performing as expected?
• High load SQL statements - Are there any SQL statements which are consuming excessive system resources?
• High load PL/SQL execution and compilation, as well as high load Java usage
• RAC specific issues - What are the global cache hot blocks and objects; are there any interconnect latency issues?
• Sub-optimal use of Oracle by the application - Are there problems with poor connection management, excessive parsing, or application level lock contention?
• Database configuration issues - Is there evidence of incorrect sizing of log files, archiving issues, excessive checkpoints, or sub-optimal parameter settings?
• Concurrency issues - Are there buffer busy problems?
• Hot objects and top SQL for various problem areas

ADDM also documents the non-problem areas of the system. For example, wait event classes that are not significantly impacting the performance of the system are identified and removed from the tuning consideration at an early stage, saving time and effort that would be spent on items that do not impact overall system performance.

In addition to problem diagnostics, ADDM recommends possible solutions. When appropriate, ADDM recommends multiple solutions for the DBA to choose from. ADDM considers a variety of changes to a system while generating its recommendations. Recommendations include:

• Hardware changes - Adding CPUs or changing the I/O subsystem configuration
• Database configuration - Changing initialization parameter settings
• Schema changes - Hash partitioning a table or index, or using automatic segment-space management (ASSM)
• Application changes - Using the cache option for sequences or using bind variables
• Using other advisors - Running the SQL Tuning Advisor on high load SQL or running the Segment Advisor on hot objects

ADDM Analysis Results

ADDM analysis results are represented as a set of FINDINGs. See Example 6-1 for an example of ADDM analysis results. Each ADDM finding can belong to one of three types:

• Problem: Findings that describe the root cause of a database performance issue.
• Symptom: Findings that contain information that often lead to one or more problem findings.
• Information: Findings that are used for reporting non-problem areas of the system.

Each problem finding is quantified by an impact that is an estimate of the portion of DB time caused by the finding's performance issue. A problem finding can be associated with a list of RECOMMENDATIONs for reducing the impact of the performance problem. Each recommendation has a benefit which is an estimate of the portion of DB time that can be saved if the recommendation is implemented. A list of recommendations can contain various alternatives for solving the same problem; you not have to apply all the recommendations to solve a specific problem.

Recommendations are composed of ACTIONs and RATIONALEs. You need to apply all the actions of a recommendation in order to gain the estimated benefit. The rationales are used for explaining why the set of actions were recommended and to provide additional information to implement the suggested recommendation.

An ADDM Example

Consider the following section of an ADDM report in Example 6-1.

Example 6-1 Example ADDM Report

FINDING 1: 31% impact (7798 seconds) 
------------------------------------ 
SQL statements were not shared due to the usage of literals. This resulted in 
additional hard parses which were consuming significant database time.

RECOMMENDATION 1: Application Analysis, 31% benefit (7798 seconds)
  ACTION: Investigate application logic for possible use of bind variables
    instead of literals. Alternatively, you may set the parameter 
    "cursor_sharing" to "force".
  RATIONALE: SQL statements with PLAN_HASH_VALUE 3106087033 were found to be
    using literals. Look in V$SQL for examples of such SQL statements.

In this example, the finding points to a particular root cause, the usage of literals in SQL statements, which is estimated to have an impact of about 31% of total DB time in the analysis period.

The finding has a recommendation associated with it, composed of one action and one rationale. The action specifies a solution to the problem found and is estimated to have a maximum benefit of up to 31% DB time in the analysis period. Note that the benefit is given as a portion of the total DB time and not as a portion of the finding's impact. The rationale provides additional information on tracking potential SQL statements that were using literals and causing this performance issue. Using the specified plan hash value of SQL statements that could be a problem, a DBA could quickly examine a few sample statements.

When a specific problem has multiple causes, the ADDM may report multiple problem and symptom findings. In this case, the impacts of these multiple findings can contain the same portion of DB time. Because the performance issues of findings can overlap, summing all the impacts of the reported findings can yield a number higher than 100% of DB time. For example, if a system performs many read I/Os the ADDM might report a SQL statement responsible for 50% of DB time due to I/O activity as one finding, and an undersized buffer cache responsible for 75% of DB time as another finding.

When multiple recommendations are associated with a problem finding, the recommendations may contain alternatives for solving the problem. In this case, the sum of the recommendations' benefits may be higher than the finding's impact.

When appropriate, an ADDM action many haves multiple solutions for the DBA to choose from. In the example, the most effective solution is to use bind variables. However, it is often difficult to modify the application. Changing the value of the CURSOR_SHARING initialization parameter is much easier to implement and can provide significant improvement.

Setting Up ADDM

Automatic database diagnostic monitoring is enabled by default and is controlled by the STATISTICS_LEVEL initialization parameter. The STATISTICS_LEVEL parameter should be set to the TYPICAL or ALL to enable the automatic database diagnostic monitoring. The default setting is TYPICAL. Setting STATISTICS_LEVEL to BASIC disables many Oracle features, including ADDM, and is strongly discouraged.

ADDM analysis of I/O performance partially depends on a single argument, DBIO_EXPECTED, that describes the expected performance of the I/O subsystem. The value of DBIO_EXPECTED is the average time it takes to read a single database block in microseconds. Oracle uses the default value of 10 milliseconds, which is an appropriate value for most modern hard drives. If your hardware is significantly different, such as very old hardware or very fast RAM disks, consider using a different value.

To determine the correct setting for DBIO_EXPECTED parameter, perform the following steps:

1. Measure the average read time of a single database block read for your hardware. Note that this measurement is for random I/O, which includes seek time if you use standard hard drives. Typical values for hard drives are between 5000 and 20000 microseconds.
2. Set the value one time for all subsequent ADDM executions. For example, if the measured value if 8000 microseconds, you should execute the following command as SYS user:

   EXECUTE DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER(
                        'ADDM', 'DBIO_EXPECTED', 8000);
   

Accessing ADDM with Oracle Enterprise Manager

The primary interface for diagnostic monitoring is the Oracle Enterprise Manager Database Control. To access Automatic Database Diagnostic Monitor through Oracle Enterprise Manager Database Control:

• On the Database Home page, ADDM findings for the last analysis period are displayed under Performance Analysis. You can click the link associated with each finding to display a more detailed page containing recommendations for the findings.
• You can click the Advisor Central link under Related Links at the bottom of the Oracle Enterprise Manager Database pages. On the Advisor Central page, you can search for previous ADDM tasks or click the ADDM link to create a new task.
• On the Database Performance page, click a clipboard icon just below the Sessions: Waiting and Working graph to display ADDM analysis.
• You can run ADDM tasks on selected snapshots or a set of preserved snapshots (baseline) from the Workload Repository Snapshots page.
  • On the Administration page, click the Automatic Workload Repository link under Workload.
  • On Automatic Workload Repository page, click the link next to Snapshots or Preserved Snapshot Sets.
    • On the Snapshots page, you can select Create ADDM Task from the pull-down Actions menu. Next select the beginning and ending snapshots corresponding to the time period that you want to analyze.
    • On the Preserved Snapshot Sets page, you can select Create ADDM Task from the pull-down Actions menu. Next select the preserved snapshot set corresponding to the time period that you want to analyze.

      See Also: Oracle Enterprise Manager Concepts and Oracle Enterprise Manager online help for information about monitoring and diagnostic tools available with Oracle Enterprise Manager

Diagnosing Database Performance Issues with ADDM

To diagnose database performance issues, ADDM analysis can be performed across any two AWR snapshots as long as the following requirements are met:

• Both the snapshots did not encounter any errors during creation and both have not yet been purged.
• There were no shutdown and startup actions between the two snapshots.

Consider a scenario in which users complain that the database was performing poorly between 7 P.M. and 9 P.M. of the previous night. The first step in diagnosing the database performance during that time period is invoking an ADDM analysis over that specific time frame.

While the simplest way to run an ADDM analysis over a specific time period is with the Oracle Enterprise Manager GUI, ADDM can also be run manually using the $ORACLE_HOME/rdbms/admin/addmrpt.sql script and DBMS_ADVISOR package APIs. The SQL script and APIs can be run by any user who has been granted the ADVISOR privilege.

Running ADDM Using addmrpt.sql

To invoke ADDM analysis for the scenario previously described, you can simply run the addmrpt.sql script at the SQL prompt:

@$ORACLE_HOME/rdbms/admin/addmrpt.sql

When running the addmrpt.sql report to analyze the specific time period in the example scenario, you need to:

1. Identify the last snapshot that was taken before or at 7 P.M. and the first snapshot that was taken after or at 9 P.M. of the previous night from the list of recent snapshots that the report initially displays. The output is similar to the following:

   Listing the last 3 days of Completed Snapshots
   ...
                                                           Snap
   Instance     DB Name        Snap Id    Snap Started    Level
   ------------ ------------ --------- ------------------ -----
   main         MAIN               136 20 Oct 2003 18:30      1
                                   137 20 Oct 2003 19:00      1
                                   138 20 Oct 2003 19:30      1
                                   139 20 Oct 2003 20:00      1
                                   140 20 Oct 2003 20:30      1
                                   141 20 Oct 2003 21:00      1
                                   142 20 Oct 2003 21:30      1
   
   

2. Provide the snapshot Id closest to 7 P.M. when prompted for the beginning snapshot and the 9 P.M. snapshot Id when prompted for the ending snapshot.

   Enter value for begin_snap: 137
   Begin Snapshot Id specified: 137
   
   Enter value for end_snap: 141
   End   Snapshot Id specified: 141
   
   

3. Enter a report name or accept the default name when prompted to specify the report name.

   Enter value for report_name: 
   Using the report name addmrpt_1_137_145.txt
   Running the ADDM analysis on the specified pair of snapshots ...
   Generating the ADDM report for this analysis ...
   
   

After the report name is specified, ADDM analysis over the specific time frame is performed. At the end of the analysis, the SQL script displays the textual ADDM report of the analysis. You can review the report to find the top performance issues affecting the database and possible ways to solve those issues.

Instructions for running the report addmrpt.sql in a non-interactive mode can be found at the beginning of the $ORACLE_HOME/rdbms/admin/addmrpt.sql file.

Running ADDM using DBMS_ADVISOR APIs

To perform specific ADDM analysis, you can use the DBMS_ADVISOR APIs to write your own PL/SQL program. Using the DBMS_ADVISOR procedures, you can create and execute any of the advisor tasks, such as an ADDM task. An advisor task is an executable data area in the workload repository that manages all users tuning efforts.

A typical usage of the DBMS_ADVISOR package involves:

• Creating an advisor task of a particular type, such as ADDM, using DBMS_ADVISOR.CREATE_TASK
• Setting the required parameters to run a specific type of task, such as START_SNAPSHOT and END_SNAPSHOT parameters, using DBMS_ADVISOR.SET_TASK_PARAMETER
• Executing the task using DBMS_ADVISOR.EXECUTE_TASK
• Viewing the results using DBMS_ADVISOR.GET_TASK_REPORT

In terms of the scenario previously discussed, you can write a PL/SQL function that can automatically identify the snapshots that were taken closest to a given time period and then run ADDM. The PL/SQL function is similar to the following:

Example 6-2 Function for ADDM Analysis on a Pair of Snapshots

CREATE OR REPLACE FUNCTION run_addm(start_time IN DATE, end_time IN DATE )
  RETURN VARCHAR2
IS
  begin_snap          NUMBER;
  end_snap            NUMBER;
  tid                 NUMBER;          -- Task ID
  tname               VARCHAR2(30);    -- Task Name
  tdesc               VARCHAR2(256);   -- Task Description
BEGIN
  -- Find the snapshot IDs corresponding to the given input parameters.
  SELECT max(snap_id)INTO begin_snap
    FROM DBA_HIST_SNAPSHOT
   WHERE trunc(end_interval_time, 'MI') <= start_time;
  SELECT min(snap_id) INTO end_snap
    FROM DBA_HIST_SNAPSHOT
   WHERE  end_interval_time >= end_time;
  --
  -- set Task Name (tname) to NULL and let create_task return a
  -- unique name for the task.
  tname := '';
  tdesc := 'run_addm( ' || begin_snap || ', ' || end_snap || ' )';
  --
  -- Create a task, set task parameters and execute it
  DBMS_ADVISOR.CREATE_TASK( 'ADDM', tid, tname, tdesc );
  DBMS_ADVISOR.SET_TASK_PARAMETER( tname, 'START_SNAPSHOT', begin_snap );
  DBMS_ADVISOR.SET_TASK_PARAMETER( tname, 'END_SNAPSHOT' , end_snap );
  DBMS_ADVISOR.EXECUTE_TASK( tname );
  RETURN tname;
END;
/

The PL/SQL function run_addm in Example 6-2 finds the snapshots that were taken closest to a specified time frame and executes an ADDM analysis over that time period. The function also returns the name of the ADDM task that performed the analysis.

To run ADDM between 7 P.M. and 9 P.M. using the PL/SQL function run_addm and produce the text report of the analysis, you can execute SQL statements similar to the following:

Example 6-3 Reporting ADDM Analysis on a Pair of Specific Snapshots

-- set SQL*Plus variables and column formats for the report
SET PAGESIZE 0 LONG 1000000 LONGCHUNKSIZE 1000;
COLUMN get_clob FORMAT a80;
-- execute run_addm() with 7pm and 9pm as input
VARIABLE task_name VARCHAR2(30);
BEGIN
  :task_name := run_addm( TO_DATE('19:00:00 (10/20)', 'HH24:MI:SS (MM/DD)'),
                          TO_DATE('21:00:00 (10/20)', 'HH24:MI:SS (MM/DD)') );
END;
/
-- execute GET_TASK_REPORT to get the textual ADDM report.
SELECT DBMS_ADVISOR.GET_TASK_REPORT(:task_name)
  FROM DBA_ADVISOR_TASKS t
 WHERE t.task_name = :task_name
  AND t.owner = SYS_CONTEXT( 'userenv', 'session_user' );

Note that the SQL*Plus system variable LONG has to be set to a value that is large enough to show the entire ADDM report because the DBMS_ADVISOR.GET_TASK_REPORT function returns a CLOB.

Views with ADDM Information

Typically, you would view output and information from the automatic database diagnostic monitor through Oracle Enterprise Manager or ADDM reports. However, you can display ADDM information through the DBA_ADVISOR views. This group of views includes:

• DBA_ADVISOR_TASKS

  This view provides basic information about existing tasks, such as the task Id, task name, and when created.

• DBA_ADVISOR_LOG

  This view contains the current task information, such as status, progress, error messages, and execution times.

• DBA_ADVISOR_RECOMMENDATIONS

  This view displays the results of completed diagnostic tasks with recommendations for the problems identified in each run. The recommendations should be looked at in the order of the RANK column, as this relays the magnitude of the problem for the recommendation. The BENEFIT column gives the benefit to the system you can expect after the recommendation is carried out.

• DBA_ADVISOR_FINDINGS

  This view displays all the findings and symptoms that the diagnostic monitor encountered along with the specific recommendation.

  See Also: Oracle Database Reference for information on static data dictionary views