6 Automatic Performance Diagnostics

6.1 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 "Overview of the 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.

6.2 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

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.

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