6 Automatic Performance Diagnostics

6.2.1 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.

6.2.2 An ADDM Example

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

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.

6.2.3 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);
   

6.2.4 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 using Oracle Enterprise Manager:

• On the Database Home page, ADDM findings for the last analysis period are displayed under Diagnostic Summary. To display a more detailed page containing recommendations for a particular finding, click the link associated with the finding.

• On the Database Home page, click the Advisor Central link under Related Links. On the Advisor Central page, you can search for previous ADDM tasks or click the ADDM link under Advisors to run a new ADDM task.

• On the Database Performance page, click Run ADDM Now under Average Active Sessions to create a new AWR snapshot and run ADDM on this and the previous snapshot.

• You can run ADDM tasks on selected snapshots or a set of preserved snapshots (baseline) from the Snapshots page.

  • On the Administration page, click the Automatic Workload Repository link under Statistics Management.

  • On Automatic Workload Repository page, click the link next to Snapshots or Preserved Snapshot Sets.

    • On the Snapshots page, select Run ADDM 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, select Run ADDM 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

6.2.5 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.

@$ORACLE_HOME/rdbms/admin/addmrpt.sql

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;
/

-- 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' );

6.2.6 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