Oracle 11g：ADDM报告解析

ADDM是10g推出的特性，全称是Automatic Database Diagnostic Monitor，是Oracle一个实现性能自我诊断的最佳利器，它是通过诊断和分析AWR得到的数据来推断数据库可能存在的问题，给出的建议是以减少DB time为依据的。数据库每产生一个快照，MMON进程就会触发ADDM把最近两次快照的数据差值进行分析。

ADDM按照DB Time，即数据库时间模型统计自上而下进行分析，将最消耗资源的问题(用占据整个DB Time的百分比排序)列出在首部，并给出建议办法以及理由。所有的诊断结果都按此方式列出。通过优化后减少DB Time，在相同资源的前提下，使得数据库能够支持的更多用户请求，从而增加吞吐量。

ADDM分析的主要范围：

• CPU瓶颈：Oracle数据库还是其他应用程序导致CPU开销过高？
• 内存瓶颈：Oracle数据库的内存结构，如SGA、PGA、和缓冲区高速缓存，足够大吗？
• I/O问题：I/O子系统执行超预期？
• 高负载SQL语句：是否有任何SQL语句正在消耗过多的系统资源？
• 高负荷的PL/SQL的执行和编译，和高负荷的java使用？
• Oracle RAC问题：全局缓存热块和对象是什么；有任何互连延迟的问题？
• 应用程序最优使用Oracle数据库：如糟糕的连接管理，过度解析析，或应用程序级锁争的问题吗？
• 数据库配置问题：是否有不正确的日志文件大小，归档问题，过多的检查点，或未经优化的参数设置？
• 并发问题：是否存在缓冲区忙问题？
• 热对象和顶级SQL的各种问题领域

在$ORACLE_HOME/rdbms/admin下

-rw-r--r-- 1 oracle oinstall 4748 1月   5 2005 addmrpti.sql -- 用于RAC

-rw-r--r-- 1 oracle oinstall 3168 10月 15 2003 addmrpt.sql -- 用于单实例

下面是一个ADDM报告的解析：

          

ADDM Report for Task 'TASK_49546'
---------------------------------

Analysis Period =》该部分指明了分析的时间范围。由于AWR是给定时间里的平均值，因此生成ADDM报告时也要选取业务比较忙的阶段，更有助于诊断特定故障。
---------------
AWR snapshot range from 16023 to 16024.
Time period starts at 27-JUL-20 09.59.50 AM
Time period ends at 27-JUL-20 11.00.33 AM

Analysis Target =》该部分指明了数据库的版本，实例等信息
---------------
Database 'SIFW' with DB ID 519682427.
Database version 11.2.0.4.0.
ADDM performed an analysis of instance sifw1, numbered 1 and hosted at
zsfwdbadm01.szhrss.gov.cn.

Activity During the Analysis Period =》该部分为分析期间的总的数据库耗用时间及每个会话的平均时间
该例中自然时间是从09.59.50到11.00.33，共3643秒，DB time是32984s，平均每秒有9.05个活动会话。
已知该环境共有48个逻辑CPU，性能还不错。
-----------------------------------
Total database time was 32984 seconds.
The average number of active sessions was 9.05.

Summary of Findings =》该部分是诊断结果的摘要部分，列出重要的诊断结果及百分比，建议条数
例如第一行Top SQL Statements，受影响活动会话数6.3个，占整个DB time的69.6%，有5条建议
第二行Undersized SGA，一条建议

-------------------
Description         Active Sessions      Recommendations
Percent of Activity
------------------  -------------------  ---------------
1  Top SQL Statements  6.3 | 69.6           5
2  Undersized SGA      .1 | 1.07            1


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


Findings and Recommendations
--这部分是调优的关键。由多个不同的Finding组成，且每个Finding均包含以下内容：
--1、在Finding标题中列出相应的Findings名称，如TopSQL，或者相关等待事件如Free Buffer Waits
--2、描述受影响的活动会话数，以及占用总活动的百分比
--3、给出优化建议，采取的行动，以及理论依据
----------------------------

Finding 1: Top SQL Statements
Impact is 6.36 active sessions, 69.6% of total activity.
--------------------------------------------------------
SQL statements consuming significant database time were found. These
statements offer a good opportunity for performance improvement.

Recommendation 1: SQL Tuning
Estimated benefit is 2.93 active sessions, 32.39% of total activity.
--------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SELECT statement with SQL_ID
"6pfvxmb6b12s5".
Related Object
SQL statement with SQL_ID 6pfvxmb6b12s5.
select a.id_ tid from dwact.act_ru_task a,        bpzone.ti_addition
tia   where a.id_=tia.tid         and exists ( select * from
bpzone.ei_addition b                where  b.eid=a.execution_id_
and b.rootpiid=:1                           and
(b.ancestoreid like '%.'||:2 ||'.%' or :3  like  '%.'||b.eid||'.%')
)        and nvl(A.ASSIGNEE_, :4  ) = :5            AND
nvl(tia.exclusiveuser,'-') <> :6             AND EXISTS ( SELECT *
FROM dwact.ACT_RU_IDENTITYLINK C
WHERE A.ID_ = C.TASK_ID_
AND ((c.type_='candidate' AND c.user_id_=:7  )
OR   EXISTS ( SELECT D.GROUP_ID_
FROM dwact.ACT_ID_MEMBERSHIP D
WHERE D.USER_ID_ = :8
AND D.GROUP_ID_ = C.GROUP_ID_)))
Rationale
The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
This part of database time may be improved by the SQL Tuning Advisor.
Rationale
Database time for this SQL was divided as follows: 100% for SQL
execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL statement with SQL_ID "6pfvxmb6b12s5" was executed 3337 times and
had an average elapsed time of 3.2 seconds.
--该部分是针对insert SQL语句(SQL_ID为6pfvxmb6b12s5)给出的一些调整建议
--包含完整的SQL语句，执行的次数，以及执行的平均时间
--指出时间都花在哪里了（100% for SQL execution）由此可以看出通过优化SQL语句来提升性能

Recommendation 2: SQL Tuning
Estimated benefit is 1.77 active sessions, 19.51% of total activity.
--------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the UPDATE statement with SQL_ID
"33m0a3u1xndua".
Related Object
SQL statement with SQL_ID 33m0a3u1xndua.
update hsoms.hso_work_master set tsflag=:1   where tsid=:2
Rationale
The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
This part of database time may be improved by the SQL Tuning Advisor.
Rationale
Database time for this SQL was divided as follows: 100% for SQL
execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL statement with SQL_ID "33m0a3u1xndua" was executed 3061 times and
had an average elapsed time of 2.1 seconds.

Recommendation 3: SQL Tuning
Estimated benefit is .81 active sessions, 8.97% of total activity.
------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SELECT statement with SQL_ID
"3btxzvtmjf403".
Related Object
SQL statement with SQL_ID 3btxzvtmjf403.
select 1                      from si3s.orgn_cmpl b             where
b.bcxmbh = 'eshwhzdwbz'        and b.bcxmz = '1'                  and
b.dwid = :1
Rationale
The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
This part of database time may be improved by the SQL Tuning Advisor.
Rationale
Database time for this SQL was divided as follows: 100% for SQL
execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL statement with SQL_ID "3btxzvtmjf403" was executed 29200 times and
had an average elapsed time of 0.094 seconds.

Recommendation 4: SQL Tuning
Estimated benefit is .49 active sessions, 5.46% of total activity.
------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the DELETE statement with SQL_ID
"amj0qm2ghgz78".
Related Object
SQL statement with SQL_ID amj0qm2ghgz78.
delete from hsoms.code_openid where openid = :1
Rationale
The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
This part of database time may be improved by the SQL Tuning Advisor.
Rationale
Database time for this SQL was divided as follows: 100% for SQL
execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL statement with SQL_ID "amj0qm2ghgz78" was executed 6931 times and
had an average elapsed time of 0.25 seconds.

Recommendation 5: SQL Tuning
Estimated benefit is .3 active sessions, 3.27% of total activity.
-----------------------------------------------------------------
Action
Run SQL Tuning Advisor on the UPDATE statement with SQL_ID
"50a1h324vxvxm".
Related Object
SQL statement with SQL_ID 50a1h324vxvxm.
update hsoms.hsp_work_master set tsflag=:1   where tsid=:2
Rationale
The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
This part of database time may be improved by the SQL Tuning Advisor.
Rationale
Database time for this SQL was divided as follows: 100% for SQL
execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL statement with SQL_ID "50a1h324vxvxm" was executed 237 times and had
an average elapsed time of 4.8 seconds.


Finding 2: Undersized SGA
Impact is .1 active sessions, 1.07% of total activity.
------------------------------------------------------
The SGA was inadequately sized, causing additional I/O or hard parses.
The value of parameter "sga_target" was "217088 M" during the analysis period.

Recommendation 1: Database Configuration
Estimated benefit is .02 active sessions, .21% of total activity.
-----------------------------------------------------------------
Action
Increase the size of the SGA by setting the parameter "sga_target" to
230656 M.

Symptoms That Led to the Finding:
---------------------------------
Wait class "User I/O" was consuming significant database time.
Impact is .2 active sessions, 2.18% of total activity.

=》该部分指出SGA设置过小，并给出建议的SGA size.
这部分可以结合v$sga_target_advice来看

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

Additional Information =》该部分是一些额外的信息，用于说明哪些类别没有消耗大量的数据库时间。
----------------------

Miscellaneous Information
-------------------------
Wait class "Application" was not consuming significant database time.
Wait class "Cluster" 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.
The network latency of the cluster interconnect was within acceptable limits
of 1 milliseconds.
Session connect and disconnect calls were not consuming significant database
time.
Hard parsing of SQL statements was not consuming significant database time.