Automatic Database Diagnostic Monitor (ADDM)

最新推荐文章于 2024-09-14 19:55:29 发布

cuichao112233

最新推荐文章于 2024-09-14 19:55:29 发布

阅读量167

点赞数

文章标签：数据库

ADDM is used to diagnose database performance issues. Its analysis can be performed across any two AWR snapshots.

SQL> @$ORACLE_HOME/rdbms/admin/addmrpt.sql

Current Instance

~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance

----------- ------------ -------- ------------

2568139559 CTGBU2 1 ctgbu2

Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id Inst Num DB Name Instance Host

------------ -------- ------------ ------------ ------------

1858485560 1 CTGBP ctgbp JPSIT01

1858485560 1 CTGBP ctgbp ctgbpdb01

1858485560 1 CTGBP ctgbp ctgbpdb02

* 2568139559 1 CTGBU2 ctgbu2 JPSIT01

Using 2568139559 for database Id

Using 1 for instance number

Specify the number of days of snapshots to choose from

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Entering the number of days (n) will result in the most recent

(n) days of snapshots being listed. Pressing without

specifying a number lists all completed snapshots.

Listing the last 3 days of Completed Snapshots

Snap

Instance DB Name Snap Id Snap Started Level

------------ ------------ --------- ------------------ -----

ctgbu2 CTGBU2 6611 14 Jun 2009 00:00 1

6612 14 Jun 2009 01:00 1

6613 14 Jun 2009 02:00 1

6614 14 Jun 2009 03:00 1

6615 14 Jun 2009 04:00 1

6616 14 Jun 2009 05:00 1

6617 14 Jun 2009 06:00 1

6618 14 Jun 2009 07:00 1

6619 14 Jun 2009 08:00 1

6620 14 Jun 2009 09:00 1

6621 14 Jun 2009 10:00 1

6622 14 Jun 2009 11:00 1

6623 14 Jun 2009 12:00 1

6624 14 Jun 2009 13:00 1

6625 14 Jun 2009 14:00 1

6626 14 Jun 2009 15:00 1

6627 14 Jun 2009 16:00 1

6628 14 Jun 2009 17:00 1

6629 14 Jun 2009 18:00 1

6630 14 Jun 2009 19:00 1

6631 14 Jun 2009 20:01 1

6632 14 Jun 2009 21:00 1

6633 14 Jun 2009 22:00 1

6634 14 Jun 2009 23:00 1

6635 15 Jun 2009 00:00 1

6636 15 Jun 2009 01:00 1

6637 15 Jun 2009 02:00 1

6638 15 Jun 2009 03:00 1

6639 15 Jun 2009 04:00 1

6640 15 Jun 2009 05:00 1

6641 15 Jun 2009 06:00 1

6642 15 Jun 2009 07:00 1

6643 15 Jun 2009 08:00 1

6644 15 Jun 2009 09:00 1

6645 15 Jun 2009 10:00 1

6646 15 Jun 2009 11:00 1

6647 15 Jun 2009 12:00 1

6648 15 Jun 2009 13:00 1

6649 15 Jun 2009 14:00 1

6650 15 Jun 2009 15:00 1

6651 15 Jun 2009 16:00 1

6652 15 Jun 2009 17:00 1

6653 15 Jun 2009 18:00 1

6654 15 Jun 2009 19:00 1

6655 15 Jun 2009 20:00 1

6656 15 Jun 2009 21:00 1

6657 15 Jun 2009 22:00 1

6658 15 Jun 2009 23:00 1

6659 16 Jun 2009 00:00 1

6660 16 Jun 2009 01:00 1

6661 16 Jun 2009 02:00 1

6662 16 Jun 2009 03:00 1

6663 16 Jun 2009 04:01 1

6664 16 Jun 2009 05:01 1

6665 16 Jun 2009 06:01 1

6666 16 Jun 2009 07:01 1

Snap

Instance DB Name Snap Id Snap Started Level

------------ ------------ --------- ------------------ -----

ctgbu2 CTGBU2 6667 16 Jun 2009 08:00 1

6668 16 Jun 2009 09:00 1

6669 16 Jun 2009 10:01 1

6670 16 Jun 2009 11:00 1

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 6664

Begin Snapshot Id specified: 6664

Enter value for end_snap: 6666

End Snapshot Id specified: 6666

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is addmrpt_1_6664_6666.txt. To use this name,

press to continue, otherwise enter an alternative.

Enter value for report_name: /tmp/addmtest.log

Using the report name /tmp/addmtest.log

Running the ADDM analysis on the specified pair of snapshots ...

Generating the ADDM report for this analysis ...

DETAILED ADDM REPORT FOR TASK 'TASK_31477' WITH ID 31477

--------------------------------------------------------

Analysis Period: 16-JUN-2009 from 05:01:03 to 07:01:09

Database ID/Instance: 2568139559/1

Database/Instance Names: CTGBU2/ctgbu2

Host Name: JPSIT01

Database Version: 10.1.0.4.0

Snapshot Range: from 6664 to 6666

Database Time: 8710 seconds

Average Database Load: 1.2 active sessions

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

FINDING 1: 14% impact (1261 seconds)

------------------------------------

Individual SQL statements responsible for significant user I/O wait were

found.

RECOMMENDATION 1: SQL Tuning, 4.1% benefit (356 seconds)

ACTION: Tune the PL/SQL block with SQL_ID "89j4h9uu70grb". Refer to the

"Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide

and Reference"

RELEVANT OBJECT: SQL statement with SQL_ID 89j4h9uu70grb

DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;

broken BOOLEAN := FALSE; BEGIN /* Database Analysis report */

quest_ix_scheduler.run_task(147,JOB,NEXT_DATE); :mydate := next_date;

IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

RECOMMENDATION 2: SQL Tuning, 3.5% benefit (302 seconds)

ACTION: Tune the PL/SQL block with SQL_ID "dxh8b2fpqfnu5". Refer to the

"Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide

and Reference"

RELEVANT OBJECT: SQL statement with SQL_ID dxh8b2fpqfnu5

BEGIN :rule_value:=quest_ix_cbo_rule_pak.stale_analyzer_stats(:run_id

); END;

RECOMMENDATION 3: SQL Tuning, 3.5% benefit (302 seconds)

ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID

"07k0t67czx7g6".

RELEVANT OBJECT: SQL statement with SQL_ID 07k0t67czx7g6 and

PLAN_HASH 2736083658

SELECT OWNER , TABLE_NAME , BLOCKS , ANALYZED_BLOCKS , BLOCK_VARIANCE

FROM QUEST_IX_STALE_STATS ORDER BY 5 DESC

RECOMMENDATION 4: SQL Tuning, 3.4% benefit (300 seconds)

ACTION: Tune the PL/SQL block with SQL_ID "bqx3f42d29g34". Refer to the

"Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide

and Reference"

RELEVANT OBJECT: SQL statement with SQL_ID bqx3f42d29g34

BEGIN DBMS_SPACE.SPACE_USAGE(segment_owner=>:segment_owner,segment_na

me=>:segment_name,segment_type=>:segment_type,unformatted_blocks=>:un

f,unformatted_bytes=>:unfb,fs1_blocks=>:fs1,fs1_bytes=>:fs1b,fs2_bloc

ks=>:fs2,fs2_bytes=>:fs2b,fs3_blocks=>:fs3,fs3_bytes=>:fs3b,fs4_block

s=>:fs4,fs4_bytes=>:fs4b,full_blocks=>:full,full_bytes=>:fullb); END;

SYMPTOMS THAT LED TO THE FINDING:

Wait class "User I/O" was consuming significant database time. (4.3%

impact [378 seconds])

FINDING 2: 14% impact (1236 seconds)

------------------------------------

PL/SQL execution consumed significant database time.

RECOMMENDATION 1: SQL Tuning, 3.6% benefit (316 seconds)

ACTION: Tune the PL/SQL block with SQL_ID "89j4h9uu70grb". Refer to the

"Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide

and Reference"

RELEVANT OBJECT: SQL statement with SQL_ID 89j4h9uu70grb

DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;

broken BOOLEAN := FALSE; BEGIN /* Database Analysis report */

quest_ix_scheduler.run_task(147,JOB,NEXT_DATE); :mydate := next_date;

IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

RECOMMENDATION 2: SQL Tuning, 3.5% benefit (307 seconds)

ACTION: Tune the PL/SQL block with SQL_ID "dxh8b2fpqfnu5". Refer to the

"Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide

and Reference"

RELEVANT OBJECT: SQL statement with SQL_ID dxh8b2fpqfnu5

BEGIN :rule_value:=quest_ix_cbo_rule_pak.stale_analyzer_stats(:run_id

); END;

RECOMMENDATION 3: SQL Tuning, 3.5% benefit (307 seconds)

ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID

"07k0t67czx7g6".

RELEVANT OBJECT: SQL statement with SQL_ID 07k0t67czx7g6 and

PLAN_HASH 2736083658

SELECT OWNER , TABLE_NAME , BLOCKS , ANALYZED_BLOCKS , BLOCK_VARIANCE

FROM QUEST_IX_STALE_STATS ORDER BY 5 DESC

RECOMMENDATION 4: SQL Tuning, 3.5% benefit (305 seconds)

ACTION: Tune the PL/SQL block with SQL_ID "bqx3f42d29g34". Refer to the

"Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide

and Reference"

RELEVANT OBJECT: SQL statement with SQL_ID bqx3f42d29g34

BEGIN DBMS_SPACE.SPACE_USAGE(segment_owner=>:segment_owner,segment_na

me=>:segment_name,segment_type=>:segment_type,unformatted_blocks=>:un

f,unformatted_bytes=>:unfb,fs1_blocks=>:fs1,fs1_bytes=>:fs1b,fs2_bloc

ks=>:fs2,fs2_bytes=>:fs2b,fs3_blocks=>:fs3,fs3_bytes=>:fs3b,fs4_block

s=>:fs4,fs4_bytes=>:fs4b,full_blocks=>:full,full_bytes=>:fullb); END;

FINDING 3: 4.5% impact (391 seconds)

------------------------------------

Host CPU was a bottleneck and the instance was consuming 89% of the host CPU.

All wait times will be inflated by wait for CPU.

RECOMMENDATION 1: Host Configuration, 4.5% benefit (391 seconds)

ACTION: Consider adding more CPUs to the host or increasing the number

of instances serving the database.

ADDITIONAL INFORMATION: Host CPU consumption was 76%. The instance spent

significant time on CPU. However, there were no predominant SQL

statements responsible for the CPU load.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

ADDITIONAL INFORMATION

----------------------

Wait class "Administrative" was not consuming significant database time.

Wait class "Application" was not consuming significant database time.

Wait class "Cluster" was not consuming significant database time.

Wait class "Commit" was not consuming significant database time.

Wait class "Concurrency" was not consuming significant database time.

Wait class "Configuration" was not consuming significant database time.

Wait class "Network" was not consuming significant database time.

Wait class "Scheduler" was not consuming significant database time.

Wait class "Other" was not consuming significant database time.

The analysis of I/O performance is based on the default assumption that the

average read time for one database block is 10000 micro-seconds.

An explanation of the terminology used in this report is available when you run the report with the 'ALL' level of detail.

2^nd, Running ADDM Using DBMS_ADVISOR APIs

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 5am and 7am as input

VARIABLE task_name VARCHAR2(30);

BEGIN

  :task_name := run_addm( TO_DATE('05:00:01 (06/16)', 'HH24:MI:SS (MM/DD)'),

                          TO_DATE('07:00:01 (06/16)', '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');

BEGIN

DBMS_ADVISOR.create_task (

advisor_name      => 'ADDM',

task_name         => '5838_6027_AWR_SNAPSHOT',

task_desc         => 'Advisor for snapshots 5838 to 6027');

DBMS_ADVISOR.set_task_parameter (

task_name         => '5838_6027_AWR_SNAPSHOT',

parameter => 'START_SNAPSHOT',

value     => 5838);

DBMS_ADVISOR.set_task_parameter (

task_name         => '5838_6027_AWR_SNAPSHOT',

parameter => 'END_SNAPSHOT',

value     => 6027);

DBMS_ADVISOR.execute_task(task_name => '5838_6027_AWR_SNAPSHOT');

END;

SQL> SET LONG 100000

SQL> SET PAGESIZE 50000

SQL> SELECT DBMS_ADVISOR.get_task_report('5838_6027_AWR_SNAPSHOT') AS report FROM dual;

Views with ADDM Information

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.