ADDM诊断工具的使用

环境:

OS:Red Hat Linux As 5

DB:10.2.0.4

 

1.确保参数statistics_level是TYPICAL,默认情况下该参数是TYPICAL

SQL> show parameters statistics_level;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
statistics_level                     string      TYPICAL

 

2.找到需要分析时间段的快照ID

column snap_id format 99;
column instance_number format 99;
column  startup_time format a15;
column  begin_interval_time format a15;
column  end_interval_time format a15;

SELECT
snap_id,
t.instance_number,
to_char(t.startup_time,'YYYYMMDD HH24:MI') startup_time,
to_char(t.begin_interval_time,'YYYYMMDD HH24:MI') begin_interval_time,
to_char(t.end_interval_time,'YYYYMMDD HH24:MI') end_interval_time
FROM dba_hist_snapshot t
where t.startup_time>= to_date('20120602 08:20:00','YYYYMMDD HH24:MI:SS')
  and t.startup_time<= to_date('20120602 11:00:00','YYYYMMDD HH24:MI:SS')
order by t.snap_id;

 

SNAP_ID INSTANCE_NUMBER STARTUP_TIME    BEGIN_INTERVAL_ END_INTERVAL_TI
------- --------------- --------------- --------------- ---------------
     13               1 20120602 08:23  20120602 08:23  20120602 08:35
     14               1 20120602 10:43  20120602 10:43  20120602 10:54
     15               1 20120602 10:58  20120602 10:58  20120602 12:00

4.找到addmrpt.sql并执行

[root@hxl /]# find . -name addmrpt.sql
./u01/app/oracle/product/10.2.0/db_1/rdbms/admin/addmrpt.sql

 

SQL>@/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/addmrpt.sql;

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 16  -- 开始的快照
Begin Snapshot Id specified: 16

Enter value for end_snap: 18
End   Snapshot Id specified: 18 -- 结束的快照

 

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is addmrpt_1_16_18.txt.  To use this name,
press to continue, otherwise enter an alternative.

Enter value for report_name: /u02/awr/awr16-18 -- 报告输出

 

在RAC环境下,执行addmrpt.sql,要求输入DB ID和instance ID.

 

说明:

begin
 dbms_workload_repository.modify_snapshot_settings(interval=>120, retention=>8*24*60);
end;
interval和retention单位都是分钟,如上设置收集间隔是2个小时,保留时间是8天,若要停止自动收集则将interval设置为0.

 

查看当前快照收集时间间隔和保留时间

select t.snap_interval,t.retention from dba_hist_wr_control t

 

--The End--