初探ADDM的使用

ADDM能发现定位的问题包括：

·操作系统内存页入页出问题
·由于Oracle负载和非Oracle负载导致的CPU瓶颈问题
·导致不同资源负载的Top SQL语句和对象——CPU消耗、IO带宽占用、潜在IO问题、RAC内部通讯繁忙
·按照PLSQL和JAVA执行时间排的Top SQL语句.
·过多地连接 (login/logoff).
·过多硬解析问题——由于shared pool过小、书写问题、绑定大小不适应、解析失败原因引起的。
·过多软解析问题
·索引查询过多导致资源争用.
·由于用户锁导致的过多的等待时间 (通过包dbms_lock加的锁)
·由于DML锁导致的过多等待时间(例如锁住表了)
·由于管道输出导致的过多等待时间(如通过包dbms_pipe.put进行管道输出)
·由于并发更新同一个记录导致的过多等待时间(行级锁等待)
·由于ITL不够导致的过多等待时间（大量的事务操作同一个数据块）
·系统中过多的commit和rollback(logfile sync事件).
·由于磁盘带宽太小和其他潜在问题（如由于logfile太小导致过多的checkpoint，MTTR设置问题，过多的undo操作等等）导致的IO性能问题
·对于DBWR进程写数据块，磁盘IO吞吐量不足
·由于归档进程无法跟上redo日至产生的速度，导致系统变慢
·redo数据文件太小导致的问题
·由于扩展磁盘分配导致的争用
·由于移动一个对象的高水位导致的争用问题
·内存太小问题——SGA Target, PGA, Buffer Cache, Shared Pool
·在一个实例或者一个机群环境中存在频繁读写争用的热块
·在一个实例或者一个机群环境中存在频繁读写争用的热对象
·RAC环境中内部通讯问题
·LMS进程无法跟上导致锁请求阻塞
·在RAC环境中由于阻塞和争用导致的实例倾斜
·RMAN导致的IO和CPU问题
·Streams和AQ问题
·资源管理等待事件

有一点要记住：AWR收集的数据时放到内存中（share pool），通过一个新的后台进程MMON定期写到磁盘中。所以10g的share pool要求比以前版本更大，一般推荐比以前大15-20%。另外，还要求系统参数STATISTICS_LEVEL设置为TYPICAL（推荐）或ALL;

ALTER SESSION SET STATISTICS_LEVEL= TYPICAL;

第一步：创建测试用的表

SQL> CREATE TABLE bigtab AS SELECT rownum as "id", a.* FROM dba_objects a;
 
Table created.

SQL> create table smalltab as select rownum as "id", a.* FROM dba_tables a;

Table created.

SQL> DECLARE
2       n NUMBER;
3    BEGIN
4       FOR n IN 1..100
5       LOOP
6           INSERT INTO bigtab SELECT rownum as "id", a.* FROM dba_objects a;
7           COMMIT;
8       END LOOP;
9   END;
/
 
PL/SQL procedure successfully completed.

第二步：采集一次工作量快照

SQL> begin
  2   dbms_workload_repository.create_snapshot('TYPICAL');
  3  end;
  4  /
 
PL/SQL procedure successfully completed.

第三步：进行一些高负荷操作

DECLARE
    v_var number;
BEGIN
    FOR n IN 1..6
    LOOP
        select count(*) into v_var from bigtab b, smalltab a;
    END LOOP;
END;
 /
PL/SQL procedure successfully completed.
 
第四步：再次采集一次工作量快照

要注意的是：两次快照之间的间隔时间必须足够（一般推荐30分钟左右），否则得到的ADDM报告中就会提示：THERE WAS NOT ENOUGH DATABASE TIME FOR ADDM ANALYSIS.

SQL> begin
  2   dbms_workload_repository.create_snapshot('TYPICAL');
  3  end;
  4  /
 
PL/SQL procedure successfully completed.

第五步：创建一个优化诊断任务并执行

先获取到两次快照的ID:

SQL> select snap_id from
  2  (SELECT * FROM dba_hist_snapshot
  3  ORDER BY snap_id desc)
  4  where rownum <=2;
 
 SNAP_ID
--------
      99
      98
 
然后创建优化任务，并执行。
DECLARE
    task_name VARCHAR2(30) := 'DEMO_ADDM01';
    task_desc VARCHAR2(30) := 'ADDM Feature Test';
    task_id NUMBER;
BEGIN
    dbms_advisor.create_task('ADDM', task_id, task_name, task_desc, null);
    dbms_advisor.set_task_parameter(task_name, 'START_SNAPSHOT', 65);
    dbms_advisor.set_task_parameter(task_name, 'END_SNAPSHOT', 66);
    dbms_advisor.set_task_parameter(task_name, 'INSTANCE', 1);
    dbms_advisor.set_task_parameter(task_name, 'DB_ID', 1712);
    dbms_advisor.execute_task(task_name);
END;

第六步：查看优化建议结果

通知函数dbms_advisor.get_task_report可以得到优化建议结果。
SQL> SET LONG 1000000 PAGESIZE 0 LONGCHUNKSIZE 1000
SQL> COLUMN get_clob FORMAT a80
SQL> SELECT dbms_advisor.get_task_report('DEMO_ADDM01', 'TEXT', 'ALL') FROM DUAL;

说明：
其中第五步到第六步可以直接执行$ORACLE_HOME/rdbms/admin/addmrpt.sql来得到，这个脚本的执行过程和statspack脚本执行过程类似：
此外，如果是RAC环境下，可以执行$ORACLE_HOME/rdbms/admin/addmrpti.sql，这脚本的执行，会多出要求输入DB ID和instance ID的要求。

以下就是测试的ADDM报告，供参考:

         DETAILED ADDM REPORT FOR TASK '任务_557' WITH ID 557
          --------------------------------------------------

              Analysis Period: 29-1月 -2008 from 14:51:50 to 17:07:09
         Database ID/Instance: 3051507877/1
      Database/Instance Names: IRMDB/irmdb
                    Host Name: IBM-L3YMBNP
             Database Version: 10.2.0.3.0
               Snapshot Range: from 476 to 479
                Database Time: 8175 seconds
        Average Database Load: 1 active sessions

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

FINDING 1: 100% impact (38109 seconds)
--------------------------------------
I/O 子系统的吞吐量比预期吞吐量小得多。

   RECOMMENDATION 1: Host Configuration, 100% benefit (38109 seconds)
      ACTION: 考虑增加 I/O 子系统的吞吐量。Oracle 建议的解决方案是使用 SAME
         方法将所有数据文件条带化。可能还需要增加磁盘数量以获得更好的性能。或者,
 考虑使用 Oracle 的自动存储管理解决方案。
      RATIONALE: 分析期间, 数据文件的平均 I/O 吞吐量, 对于读取为每秒 196 K, 对于
写入为每秒 2.2
         K。单个块读取的平均响应时间为 34 毫秒。

   RECOMMENDATION 2: Host Configuration, 100% benefit (38076 seconds)
      ACTION: 文件 D:\ORACLE\PRODUCT\10.2.0\ORADATA\IRMDB\TEMP01.DBF
         的性能比其它文件低得多。如果不能使用 SAME 方法将所有的文件条带化, 可考
虑将此文件条带化至多个磁盘。
         RELEVANT OBJECT: database file
         "D:\ORACLE\PRODUCT\10.2.0\ORADATA\IRMDB\TEMP01.DBF"
      RATIONALE: 对于此文件, 单个块读取的平均响应时间为 27 毫秒。

   SYMPTOMS THAT LED TO THE FINDING:
      SYMPTOM: 等待类别 "用户 I/O" 消耗了大量数据库时间。 (25% impact [2043 seco
nds])

FINDING 2: 100% impact (8175 seconds)
-------------------------------------
在主机操作系统中检测到大量虚拟内存写入/写出。

   RECOMMENDATION 1: Host Configuration, 100% benefit (8175 seconds)
      ACTION: 主机操作系统出现大量内存写入/写出, 但未检测到根本原因。请研究不属
于 (在消耗了大量虚拟内存的主机上运行的)
         此实例的进程。还可以考虑在主机中添加更多物理内存。

FINDING 3: 100% impact (8175 seconds)
-------------------------------------
发现 SQL 语句消耗了大量数据库时间。

   RECOMMENDATION 1: SQL Tuning, 99% benefit (8102 seconds)
      ACTION: 优化 SQL_ID 为 "58s659jjhwz8r" 的 PL/SQL 块。请参阅 Oracle 的 "PL/
SQL
         User\'s Guide and Reference" 中的 "Tuning PL/SQL Applications" 一章。
         RELEVANT OBJECT: SQL statement with SQL_ID 58s659jjhwz8r
         declare
         v_var number;
         begin
         for n in 1..6
         loop
         select count(*) into v_var from bigtab b,smalltab a;
         end loop;
         end;

   RECOMMENDATION 2: SQL Tuning, 92% benefit (7525 seconds)
      ACTION: 对 SQL_ID 为 "5v0mjsunmm5s4" 的 SQL 语句运行 SQL Tuning Advisor。

         RELEVANT OBJECT: SQL statement with SQL_ID 5v0mjsunmm5s4 and
         PLAN_HASH 3281046854
         SELECT COUNT(*) FROM BIGTAB B,SMALLTAB A
      RATIONALE: SQL_ID 为 "5v0mjsunmm5s4" 的 SQL 语句执行了 4 次, 每次执行平均
用时 2025 秒。

FINDING 4: 68% impact (5529 seconds)
------------------------------------
实例在 CPU 上花费的时间占据了数据库时间中的大部分。

   RECOMMENDATION 1: SQL Tuning, 92% benefit (7525 seconds)
      ACTION: 对 SQL_ID 为 "5v0mjsunmm5s4" 的 SQL 语句运行 SQL Tuning Advisor。

         RELEVANT OBJECT: SQL statement with SQL_ID 5v0mjsunmm5s4 and
         PLAN_HASH 3281046854
         SELECT COUNT(*) FROM BIGTAB B,SMALLTAB A
      RATIONALE: SQL_ID 为 "5v0mjsunmm5s4" 的 SQL 语句执行了 4 次, 每次执行平均
用时 2025 秒。
      RATIONALE: 每次执行使用 CPU 的平均时间为 1382 秒。

FINDING 5: 24% impact (1997 seconds)
------------------------------------
发现个别数据库段造成了大量的用户 I/O 等待。

   RECOMMENDATION 1: Segment Tuning, 24% benefit (1997 seconds)
      ACTION: 调查涉及 TABLE "SYS.BIGTAB" (对象 ID 为 55305) 的 I/O 的应用程序逻
辑。
         RELEVANT OBJECT: database object with id 55305
      RATIONALE: 对象的 I/O 使用统计信息为: 5 完整对象扫描, 197509 物理读取, 0
物理写入和 0 直接读取。
      RATIONALE: SQL_ID 为 "5v0mjsunmm5s4" 的 SQL 语句在等待热对象的用户 I/O 上
消耗了大量时间。
         RELEVANT OBJECT: SQL statement with SQL_ID 5v0mjsunmm5s4
         SELECT COUNT(*) FROM BIGTAB B,SMALLTAB A

   SYMPTOMS THAT LED TO THE FINDING:
      SYMPTOM: 等待类别 "用户 I/O" 消耗了大量数据库时间。 (25% impact [2043 seco
nds])

FINDING 6: 24% impact (1997 seconds)
------------------------------------
发现个别 SQL 语句造成了大量的用户 I/O 等待。

   RECOMMENDATION 1: SQL Tuning, 92% benefit (7525 seconds)
      ACTION: 对 SQL_ID 为 "5v0mjsunmm5s4" 的 SQL 语句运行 SQL Tuning Advisor。

         RELEVANT OBJECT: SQL statement with SQL_ID 5v0mjsunmm5s4 and
         PLAN_HASH 3281046854
         SELECT COUNT(*) FROM BIGTAB B,SMALLTAB A
      RATIONALE: SQL_ID 为 "5v0mjsunmm5s4" 的 SQL 语句执行了 4 次, 每次执行平均
用时 2025 秒。
      RATIONALE: 每次执行在用户 I/O 等待事件上花费的平均时间为 499 秒。

   SYMPTOMS THAT LED TO THE FINDING:
      SYMPTOM: 等待类别 "用户 I/O" 消耗了大量数据库时间。 (25% impact [2043 seco
nds])

FINDING 7: 23% impact (1919 seconds)
------------------------------------
PGA 大小不合适导致了对临时表空间的附加 I/O, 从而消耗了大量数据库时间。

   NO RECOMMENDATIONS AVAILABLE

   ADDITIONAL INFORMATION:
      分析期间, 参数 "pga_aggregate_target" 的值为 "143 M"。

   SYMPTOMS THAT LED TO THE FINDING:
      SYMPTOM: 等待类别 "用户 I/O" 消耗了大量数据库时间。 (25% impact [2043 seco
nds])

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

          ADDITIONAL INFORMATION
          ----------------------

等待类别 "应用程序" 并未消耗大量数据库时间。
等待类别 "提交" 并未消耗大量数据库时间。
等待类别 "并发" 并未消耗大量数据库时间。
等待类别 "配置" 并未消耗大量数据库时间。
等待类别 "网络" 并未消耗大量数据库时间。
会话连接和断开连接的调用并未消耗大量数据库时间。
对 SQL 语句的硬语法分析并未消耗大量数据库时间。

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.