PLSQL_性能优化工具系列10_Automatic Database Diagnostic Monitor - ADDM

2014-09-06 Created By BaoXinjian

一、摘要

---

Oracle10g中推出了新的优化诊断工具：数据库自动诊断监视工具（Automatic Database Diagnostic Monitor :ADDM），配合STA（SQL Tuning Advisor）和AWR (AWR -- Automatic Workload Repository)使用，可以快速定位问题和数据库瓶颈。

1. 9i和10g之后版本的变化

a. 在9i及之前，DBA只能使用sql_trace、statspack、10046和10053事件等性能分析工具，定位性能问题，然后根据自己的经验进行优化。

b. 在10g之后，ADDM自动收集相关的统计数据到AWR中，而STA则根据这些数据，给出优化建议，甚至是优化的SQL语句。

2. 收集级别

缺省情况下，ADDM自动启用，也可以通过STATISTICS_LEVEL参数来控制是否启用ADDM

这个参数有3个级别：BASIC、TYPICAL、ALL

a. 默认是TYPICAL，如果将该参数设置为BASIC，关闭统计信息的自动收集功能，

b. 一般情况下TYPICAL已可满足99%的需求

c. 除非遇见非常严重的性能问题才会用到ALL,

d. ORACLE 还提供了V$STATISTICS_LEVEL视图用来记录各个参数下ORACLE可以收集的统计信息的类别。

3. 如何查看收集级别

--可以通过以下命令查看数据库目前的统计信息收集级别 
SQL> SHOW PARAMETER STATISTICS_LEVEL
NAME_COL_PLUS_SHOW_PARAM  TYPE       ALUE_COL_PLUS_SHOW_PARAM
------------------------- ---------- -------------------------
statistics_level          string     TYPICAL

4. 收集信息的类型

--在STATISTICS_LEVEL参数为TYPICAL的情况下，可以通过V$STATISTICS_LEVEL视图查看都有哪些统计信息被收集
SQL>SELECT statistics_name,session_status,system_status,activation_level,session_settable
      FROM  v$statistics_level ORDER BY statistics_name; 

STATISTICS_NAME                        SESSION_ SYSTEM_S ACTIVAT SES
-------------------------------------- -------- -------- ------- ---
Active Session History                 ENABLED  ENABLED  TYPICAL NO
Adaptive Thresholds Enabled            ENABLED  ENABLED  TYPICAL NO
Automated Maintenance Tasks            ENABLED  ENABLED  TYPICAL NO
Bind Data Capture                      ENABLED  ENABLED  TYPICAL NO
Buffer Cache Advice                    ENABLED  ENABLED  TYPICAL NO
Global Cache Statistics                ENABLED  ENABLED  TYPICAL NO
Longops Statistics                     ENABLED  ENABLED  TYPICAL NO
MTTR Advice                            ENABLED  ENABLED  TYPICAL NO
Modification Monitoring                ENABLED  ENABLED  TYPICAL NO
PGA Advice                             ENABLED  ENABLED  TYPICAL NO
Plan Execution Sampling                ENABLED  ENABLED  TYPICAL YES
Plan Execution Statistics              DISABLED DISABLED ALL     YES
SQL Monitoring                         ENABLED  ENABLED  TYPICAL YES

5. ADDM基本原理

ADDM（automatic database diannose management 自动数据库诊断管理）的基本原理是减少DB time的数据库指标，DB时间是数据库花费在处理用户请求总的时间（单位为毫秒）。

如果某个问题导致产生不当的或过多的DB时间，那么ADDM将会自动 把该问题标为关注的内容。

Oracle使用新的后台进程MMON来管理ADDM，AWR每产生一次快照，MMON进程就通知ADDM把AWR最近两次快照之间的差值进行分析。

因此，在默认情况下，每当有AWR的快照产生，ADDM将自动运行。

注意：ADDM不会将处理用户响应时间作为调优的目标，你应该使用"TRACE"技术来监控它.

6. 通过减小"DB TIME", 使用同样多的系统资源,数据库服务器可以处理更多的用户请求,也就是提高了吞吐量。通过ADDM报告的问题是按照DB time排序的。

ADDM所诊断到的问题包括这些:

1. CPU的瓶颈

2. 内存结构上的瓶颈 (SGA,PGA是否已经合理地设置 ?)

3. I/O 是否存在性能瓶颈? (I/O子系统是否工作正常 ?)

4. 高负载的SQL语句 (是否存在消耗过量系统资源的SQL ?)

5. 高负荷的PLSQL执行和编译,同时是否存在高负荷的JAVA使用?

6. RAC相关的问题: 什么是全局缓冲的热块和对象? 是否存在潜在的内联(interconnect)问题 ?  

7. 应用程序的使用是否已经达到了理想化:是否存在下面这些问题?连接池的管理,  过量的解析,  应用程序层的锁竞争.

8. 数据库配置的问题:日志文件的大小是否合理?归档是否有问题?是否频繁的达到检查点?参数是否合理?

9. 并发的问题: 是否存在"缓冲区忙"的问题?

10. "热"对象以及不同程序领域内的顶级SQL.

7. 相关的视图

DBA_ADVISOR_TASKS

DBA_ADVISOR_LOG

DBA_ADVISOR_RECOMMENDATIONS

DBA_ADVISOR_FINDINGS

 

 二、案例

---

案例： 收集两个snap_id之间的ADDM诊断信息

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

SQL> show parameters statistics_level;
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
statistics_level                     string      TYPICAL

Step2. 找到需要分析时间段的快照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

Step3. 找到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 <return> to continue, otherwise enter an alternative.
Enter value for report_name: /u02/awr/awr16-18 -- 报告输出

Step4. 其他

a. 修改AWR收集间隔时间和保存时间

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

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

--查看当前快照收集时间间隔和保留时间
select t.snap_interval,t.retention from dba_hist_wr_control t

 

Thanks and Regards