Automatic Diagnostic Tool ADDM

The Automatic diagnosis tool ADDM is short for the Automatic Database Diagnostic Monitor. It is literally an Automatic diagnosis tool. This tool can discover potential performance problems and bottlenecks, and provides suggestions and solutions to solve the problem. To use this tool, you must set the STATISTICS_LEVEL parameter to TYPICAL (default) or ALL. You can use ADDM to obtain the following data: CPU load, memory usage, I/O usage, resource-consuming SQL/PLSQL, and so on, application problems, database configuration problems, connectivity problems, Object Problems, and RAC problems. Now, let's talk about how to use this tool. 0. The addmrpt. SQL script is stored in rdbms/admin/addmrpt. SQL. It is easy to use and can be directly called.

[oracle@gtlions ~]$ sqlplus "/as sysdba"SQL*Plus: Release 10.2.0.5.0 - Production on Wed Mar 7 01:19:09 2012Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionssys@GT10G> show parameter satistics_level;sys@GT10G> show parameter statistics_level;NAME                     TYPE     VALUE------------------------------------ -------- ------------------------------statistics_level             string   TYPICALsys@GT10G> @?/rdbms/admin/addmrpt.sql

 

............ In the list of recent snapshots, select the start and end snapshot numbers of the report you want to generate. By default, snapshots of the past three days are listed. If you want to list snapshots of a longer period of time, you need to modify the script on your own. Note that there cannot be instance interruptions between the selected start and end snapshots. The generated report file name is saved in the current path by default, 1. if you cannot log on to the server to perform operations using the DBMS_ADVISOR toolkit, you can use this package to perform operations in sqlplus: set the task, start value of the snapshot, end value of the snapshot, run the task, and obtain the report. You can refer to an official function to execute the above tasks:

CREATE OR REPLACE FUNCTION run_addm(start_time IN DATE, end_time IN DATE )  RETURN VARCHAR2IS  begin_snap          NUMBER;  end_snap            NUMBER;  tid                 NUMBER;          -- Task ID  tname               VARCHAR2(30);    -- Task Name  tdesc               VARCHAR2(256);   -- Task DescriptionBEGIN  -- 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 reportSET PAGESIZE 0 LONG 1000000 LONGCHUNKSIZE 1000;COLUMN get_clob FORMAT a80;-- execute run_addm() with 7pm and 9pm as inputVARIABLE task_name VARCHAR2(30);BEGIN  :task_name := run_addm( TO_DATE('20120311 12:00:00', 'YYYYMMDD HH24:MI:SS'),                          TO_DATE('20120312 12:00:00', 'YYYYMMDD HH24:MI:SS') );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' );

 

We can see that several procedures and functions are called to create a task, set task parameters, and execute the task. Finally, we can get the report through the task view. It can also be processed separately:

BEGIN  -- Create an ADDM task.  DBMS_ADVISOR.create_task (    advisor_name      => 'ADDM',    task_name         => 'MYADDM',    task_desc         => 'MYADDM');  -- Set the start and end snapshots.  DBMS_ADVISOR.set_task_parameter (    task_name => 'MYADDM',    parameter => 'START_SNAPSHOT',    value     => 101);  DBMS_ADVISOR.set_task_parameter (    task_name => 'MYADDM',    parameter => 'END_SNAPSHOT',    value     => 130);  -- Execute the task.  DBMS_ADVISOR.execute_task(task_name => 'MYADDM');END;/-- Display the report.SET LONG 100000SET PAGESIZE 50000SELECT DBMS_ADVISOR.get_task_report('MYADDM') AS reportFROM   dual;

 

2. Enterprise Manager. 3. Related views

DBA_HIST_SNAPSHOT -- stores the snapshot information DBA_ADVISOR_TASKS -- stores the Basic information about existing tasks of the addm task. DBA_ADVISOR_LOG -- addm execution log DBA_ADVISOR_FINDINGS --- addm diagnostic result overview different modules are stored in sequence --- addm diagnostic result recommended different modules are stored-The End ----- example create table t_test_subtemplate (rec_type varchar2 (10 ), capture_time varchar2 (30), uuid varchar2 (30) partition by range (capture_time) subpartition by list (uuid) subpartition template (subpartition sp00 values ('1 '), subpartition sp01 values ('2') (partition part_201101 values less than (20130130), partition part_201102 values less than (20130230 )); alter table t_test_subtemplate add partition part_201104 values less than (20130330 );