第六章

第六章  automatic performance diagnostics（ADDM）
一、ADDM概述

1.addm分析
ADDM报告中：
对awr报告进行常规分析
诊断主要的性能问题
提供解决问题的方法
确认系统的那些方面没有问题

优势：
每小时产生
诊断准确
以时间为依据判断问题并推荐解决方法
定位到问题根源，而不会症状
针对根源给出解决方法
定位没有问题的环节
诊断代价小

addm分析是根据awr快照和数据库中的一系列实例进行的。
如果是RAC环境，可以有三种分析方式：
数据库
实例
partial  即分析所有实例的某一部分

ADDM分析的最终目的是减少db time。
可以在v$sess_time_model和v$sys_time_model中查看db time相关情况

ADDM可以识别的问题包括：
CPU瓶颈 ： CPU是否限制了数据库和其他应用？
内存结构不合理： 数据库内存结构中，SGA、PGA、buffer cache分配是否足够？
I/O容量问题：I/O是否达到期望的性能？
高负载的SQL：是否有占用过量系统资源的sql？
高负载的pl/sql执行或编译、高负载的java组件
RAC中的问题，如内部交互性能太低
oracle数据库的不合理调用：如连接管理问题、绑定变量过度使用、应用层的锁争用问题
并行问题
热点问题

2.ADDM在RAC中的应用
在RAC环境下，ADDM可以检测到问题的具体位置。如某个实例的CPU负载很高，影响了整个数据库，ADDM将查出是哪个实例的问题。

addm finding找到的问题分为：
problem finding包含数据库性能问题的根本原因
symptom finding包含了一系列的problem findings的信息
Information findings用来报告数据库性能相关的信息，但不包括性能问题
warning findings是可能影响addm完整性和准确性的信息

ADDM默认是开启的，由参数control_management_pack_access和statistics_level决定
control_management_pack_access应该设置为diagnostic或diagnostic_tuning 默认是后者；设置为none禁用addm

statistic_level应该设置为typical或all。

addm对i/o性能的分析是独立的，基于dbio_expected。dbio_expected是读取一个数据库块平均花费的毫秒数，默认是10毫秒

二、ADDM设置
1.为数据库运行addm
BEGIN
DBMS_ADDM.ANALYZE_DB (
task_name IN OUT VARCHAR2,
begin_snapshot IN NUMBER,
end_snapshot IN NUMBER,
db_id IN NUMBER := NULL);
END;
/

2.为实例运行addm
BEGIN
DBMS_ADDM.ANALYZE_INST (
task_name IN OUT VARCHAR2,
begin_snapshot IN NUMBER,
end_snapshot IN NUMBER,
instance_number IN NUMBER := NULL,
db_id IN NUMBER := NULL);
END;
/

3.partial模式运行addm
begin
dbms_Addm.analyze_partial（
task_name,
instance_number,
begin_snapshot,
end_snapshot,
dbid
）;
end;
/

4.查看addm报告
dbms_addm.get_report(task_name,return);
如：
SET LONG 1000000 PAGESIZE 0;
SELECT DBMS_ADDM.GET_REPORT(:tname) FROM DUAL;

个人补充：生成addm报告也可以通过addmrpt.sql脚本

---------------------
实验：
自己的尝试失败了：
SQL> begin
  2  dbms_addm.analyze_db(task_name => 'ADDM_TEST',begin_snapshot => '149',end_snapshot => '170');
  3  end;
  4  /
dbms_addm.analyze_db(task_name => 'ADDM_TEST',begin_snapshot => '149',end_snapshot => '170');
                     *
ERROR at line 2:
ORA-06550: line 2, column 22:
PLS-00363: expression '' cannot be used as an assignment target
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored

按照书中的方法成功：
VAR tname VARCHAR2(30);
BEGIN
:tname := 'ADDM for 7PM to 9PM';
DBMS_ADDM.ANALYZE_DB(:tname, 150, 170);
END;
/

SQL>

PL/SQL procedure successfully completed
tname
---------
ADDM for 7PM to 9PM

SQL>
SQL> SET LONG 1000000 PAGESIZE 0;
SQL> SELECT DBMS_ADDM.GET_REPORT(:tname) FROM DUAL;

DBMS_ADDM.GET_REPORT(:TNAME)
--------------------------------------------------------------------------------
          ？？ 'ADDM for 7PM to 9PM' ？ ADDM ？？
          ----------------------------------
…………

自己的错误主要是由于变量的使用，task_name需要先声明，再调用，而不是直接给出。
下面的尝试是成功的：
SQL> var tname varchar2(30);
SQL> begin
  2  :tname:='my ADDM report';
  3  dbms_addm.analyze_Db(:tname,151,170);
  4  end;
  5  /

PL/SQL procedure successfully completed.

SQL> set long 100000 pagesize 0;
SQL> select dbms_addm.get_report(:tname) from dual;
          ADDM Report for Task 'my ADDM report'
          -------------------------------------

Analysis Period
---------------
AWR snapshot range from 151 to 170.
Time period starts at 05-DEC-12 03.00.22 PM
Time period ends at 06-DEC-12 05.00.50 AM

Analysis Target
---------------
Database 'TESTDB' with DB ID 2581100181.
Database version 11.2.0.1.0.
Analysis was requested for all instances, but ADDM analyzed instance testdb,
numbered 1 and hosted at localhost.localdomain.
See the "Additional Information" section for more information on the requested
instances.

Activity During the Analysis Period
-----------------------------------
Total database time was 975 seconds.
The average number of active sessions was .02.
ADDM analyzed 1 of the requested 1 instances.

Summary of Findings
-------------------
   Description                     Active Sessions      Recommendations
                                   Percent of Activity
   ------------------------------  -------------------  ---------------
1  Hard Parse                      0 | 18.6             0
2  "User I/O" wait Class           0 | 12.06            0
3  Hard Parse Due to Parse Errors  0 | 7.6              1
4  Soft Parse                      0 | 3.27             2

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

          Findings and Recommendations
          ----------------------------

Finding 1: Hard Parse
Impact is 0 active sessions, 18.6% of total activity.
-----------------------------------------------------
Hard parsing of SQL statements was consuming significant database time.
Hard parses due to cursor environment mismatch were not consuming significant
database time.
Hard parses due to literal usage and cursor invalidation were not consuming
significant database time.
The Oracle instance memory (SGA and PGA) was adequately sized.

   No recommendations are available.

Finding 2: "User I/O" wait Class
Impact is 0 active sessions, 12.06% of total activity.
------------------------------------------------------
Wait class "User I/O" was consuming significant database time.
The throughput of the I/O subsystem was not significantly lower than expected.
The Oracle instance memory (SGA and PGA) was adequately sized.

   No recommendations are available.

Finding 3: Hard Parse Due to Parse Errors
Impact is 0 active sessions, 7.6% of total activity.
----------------------------------------------------
Hard parsing SQL statements that encountered parse errors was consuming
significant database time.

   Recommendation 1: Application Analysis
   Estimated benefit is 0 active sessions, 7.6% of total activity.
   ---------------------------------------------------------------
   Action
      Investigate application logic to eliminate parse errors.

   Symptoms That Led to the Finding:
   ---------------------------------
      Hard parsing of SQL statements was consuming significant database time.
      Impact is 0 active sessions, 18.6% of total activity.

Finding 4: Soft Parse
Impact is 0 active sessions, 3.27% of total activity.
-----------------------------------------------------
Soft parsing of SQL statements was consuming significant database time.

   Recommendation 1: Application Analysis
   Estimated benefit is 0 active sessions, 3.27% of total activity.
   ----------------------------------------------------------------
   Action
      Investigate application logic to keep open the frequently used cursors.
      Note that cursors are closed by both cursor close calls and session
      disconnects.

   Recommendation 2: Database Configuration
   Estimated benefit is 0 active sessions, 3.27% of total activity.
   ----------------------------------------------------------------
   Action
      Consider increasing the session cursor cache size by increasing the
      value of parameter "session_cached_cursors".
   Rationale
      The value of parameter "session_cached_cursors" was "50" during the
      analysis period.

 

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

          Additional Information
          ----------------------

Miscellaneous Information
-------------------------
Wait class "Application" 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.
CPU was not a bottleneck for the instance.
Wait class "Network" was not consuming significant database time.
Session connect and disconnect calls were not consuming significant database
time.

The database's maintenance windows were active during 78% of the analysis
period.

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26451536/viewspace-750991/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26451536/viewspace-750991/