Chinaunix首页 | 论坛 | 博客
  • 博客访问: 17771
  • 博文数量: 6
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 70
  • 用 户 组: 普通用户
  • 注册时间: 2020-07-06 10:35
文章分类

全部博文(6)

文章存档

2020年(6)

我的朋友

分类: Oracle

2020-07-29 11:58:42

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报告的解析:
          

点击(此处)折叠或打开

  1. ADDM Report for Task 'TASK_49546'
  2. ---------------------------------
  3. Analysis Period =》该部分指明了分析的时间范围。由于AWR是给定时间里的平均值,因此生成ADDM报告时也要选取业务比较忙的阶段,更有助于诊断特定故障。
  4. ---------------
  5. AWR snapshot range from 16023 to 16024.
  6. Time period starts at 27-JUL-20 09.59.50 AM
  7. Time period ends at 27-JUL-20 11.00.33 AM
  8. Analysis Target =》该部分指明了数据库的版本,实例等信息
  9. ---------------
  10. Database 'SIFW' with DB ID 519682427.
  11. Database version 11.2.0.4.0.
  12. ADDM performed an analysis of instance sifw1, numbered 1 and hosted at
  13. zsfwdbadm01.szhrss.gov.cn.
  14. Activity During the Analysis Period =》该部分为分析期间的总的数据库耗用时间及每个会话的平均时间
  15. 该例中自然时间是从09.59.50到11.00.33,共3643秒,DB time是32984s,平均每秒有9.05个活动会话。
  16. 已知该环境共有48个逻辑CPU,性能还不错。
  17. -----------------------------------
  18. Total database time was 32984 seconds.
  19. The average number of active sessions was 9.05.
  20. Summary of Findings =》该部分是诊断结果的摘要部分,列出重要的诊断结果及百分比,建议条数
  21. 例如第一行Top SQL Statements,受影响活动会话数6.3个,占整个DB time的69.6%,有5条建议
  22. 第二行Undersized SGA,一条建议
  23. -------------------
  24. Description Active Sessions Recommendations
  25. Percent of Activity
  26. ------------------ ------------------- ---------------
  27. 1 Top SQL Statements 6.3 | 69.6 5
  28. 2 Undersized SGA .1 | 1.07 1
  29. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  30. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  31. Findings and Recommendations
  32. --这部分是调优的关键。由多个不同的Finding组成,且每个Finding均包含以下内容:
  33. --1、在Finding标题中列出相应的Findings名称,如TopSQL,或者相关等待事件如Free Buffer Waits
  34. --2、描述受影响的活动会话数,以及占用总活动的百分比
  35. --3、给出优化建议,采取的行动,以及理论依据
  36. ----------------------------
  37. Finding 1: Top SQL Statements
  38. Impact is 6.36 active sessions, 69.6% of total activity.
  39. --------------------------------------------------------
  40. SQL statements consuming significant database time were found. These
  41. statements offer a good opportunity for performance improvement.
  42. Recommendation 1: SQL Tuning
  43. Estimated benefit is 2.93 active sessions, 32.39% of total activity.
  44. --------------------------------------------------------------------
  45. Action
  46. Run SQL Tuning Advisor on the SELECT statement with SQL_ID
  47. "6pfvxmb6b12s5".
  48. Related Object
  49. SQL statement with SQL_ID 6pfvxmb6b12s5.
  50. select a.id_ tid from dwact.act_ru_task a, bpzone.ti_addition
  51. tia where a.id_=tia.tid and exists ( select * from
  52. bpzone.ei_addition b where b.eid=a.execution_id_
  53. and b.rootpiid=:1 and
  54. (b.ancestoreid like '%.'||:2 ||'.%' or :3 like '%.'||b.eid||'.%')
  55. ) and nvl(A.ASSIGNEE_, :4 ) = :5 AND
  56. nvl(tia.exclusiveuser,'-') <> :6 AND EXISTS ( SELECT *
  57. FROM dwact.ACT_RU_IDENTITYLINK C
  58. WHERE A.ID_ = C.TASK_ID_
  59. AND ((c.type_='candidate' AND c.user_id_=:7 )
  60. OR EXISTS ( SELECT D.GROUP_ID_
  61. FROM dwact.ACT_ID_MEMBERSHIP D
  62. WHERE D.USER_ID_ = :8
  63. AND D.GROUP_ID_ = C.GROUP_ID_)))
  64. Rationale
  65. The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
  66. This part of database time may be improved by the SQL Tuning Advisor.
  67. Rationale
  68. Database time for this SQL was divided as follows: 100% for SQL
  69. execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
  70. execution.
  71. Rationale
  72. SQL statement with SQL_ID "6pfvxmb6b12s5" was executed 3337 times and
  73. had an average elapsed time of 3.2 seconds.
  74. --该部分是针对insert SQL语句(SQL_ID为6pfvxmb6b12s5)给出的一些调整建议
  75. --包含完整的SQL语句,执行的次数,以及执行的平均时间
  76. --指出时间都花在哪里了(100% for SQL execution)由此可以看出通过优化SQL语句来提升性能
  77. Recommendation 2: SQL Tuning
  78. Estimated benefit is 1.77 active sessions, 19.51% of total activity.
  79. --------------------------------------------------------------------
  80. Action
  81. Run SQL Tuning Advisor on the UPDATE statement with SQL_ID
  82. "33m0a3u1xndua".
  83. Related Object
  84. SQL statement with SQL_ID 33m0a3u1xndua.
  85. update hsoms.hso_work_master set tsflag=:1 where tsid=:2
  86. Rationale
  87. The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
  88. This part of database time may be improved by the SQL Tuning Advisor.
  89. Rationale
  90. Database time for this SQL was divided as follows: 100% for SQL
  91. execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
  92. execution.
  93. Rationale
  94. SQL statement with SQL_ID "33m0a3u1xndua" was executed 3061 times and
  95. had an average elapsed time of 2.1 seconds.
  96. Recommendation 3: SQL Tuning
  97. Estimated benefit is .81 active sessions, 8.97% of total activity.
  98. ------------------------------------------------------------------
  99. Action
  100. Run SQL Tuning Advisor on the SELECT statement with SQL_ID
  101. "3btxzvtmjf403".
  102. Related Object
  103. SQL statement with SQL_ID 3btxzvtmjf403.
  104. select 1 from si3s.orgn_cmpl b where
  105. b.bcxmbh = 'eshwhzdwbz' and b.bcxmz = '1' and
  106. b.dwid = :1
  107. Rationale
  108. The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
  109. This part of database time may be improved by the SQL Tuning Advisor.
  110. Rationale
  111. Database time for this SQL was divided as follows: 100% for SQL
  112. execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
  113. execution.
  114. Rationale
  115. SQL statement with SQL_ID "3btxzvtmjf403" was executed 29200 times and
  116. had an average elapsed time of 0.094 seconds.
  117. Recommendation 4: SQL Tuning
  118. Estimated benefit is .49 active sessions, 5.46% of total activity.
  119. ------------------------------------------------------------------
  120. Action
  121. Run SQL Tuning Advisor on the DELETE statement with SQL_ID
  122. "amj0qm2ghgz78".
  123. Related Object
  124. SQL statement with SQL_ID amj0qm2ghgz78.
  125. delete from hsoms.code_openid where openid = :1
  126. Rationale
  127. The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
  128. This part of database time may be improved by the SQL Tuning Advisor.
  129. Rationale
  130. Database time for this SQL was divided as follows: 100% for SQL
  131. execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
  132. execution.
  133. Rationale
  134. SQL statement with SQL_ID "amj0qm2ghgz78" was executed 6931 times and
  135. had an average elapsed time of 0.25 seconds.
  136. Recommendation 5: SQL Tuning
  137. Estimated benefit is .3 active sessions, 3.27% of total activity.
  138. -----------------------------------------------------------------
  139. Action
  140. Run SQL Tuning Advisor on the UPDATE statement with SQL_ID
  141. "50a1h324vxvxm".
  142. Related Object
  143. SQL statement with SQL_ID 50a1h324vxvxm.
  144. update hsoms.hsp_work_master set tsflag=:1 where tsid=:2
  145. Rationale
  146. The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
  147. This part of database time may be improved by the SQL Tuning Advisor.
  148. Rationale
  149. Database time for this SQL was divided as follows: 100% for SQL
  150. execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
  151. execution.
  152. Rationale
  153. SQL statement with SQL_ID "50a1h324vxvxm" was executed 237 times and had
  154. an average elapsed time of 4.8 seconds.
  155. Finding 2: Undersized SGA
  156. Impact is .1 active sessions, 1.07% of total activity.
  157. ------------------------------------------------------
  158. The SGA was inadequately sized, causing additional I/O or hard parses.
  159. The value of parameter "sga_target" was "217088 M" during the analysis period.
  160. Recommendation 1: Database Configuration
  161. Estimated benefit is .02 active sessions, .21% of total activity.
  162. -----------------------------------------------------------------
  163. Action
  164. Increase the size of the SGA by setting the parameter "sga_target" to
  165. 230656 M.
  166. Symptoms That Led to the Finding:
  167. ---------------------------------
  168. Wait class "User I/O" was consuming significant database time.
  169. Impact is .2 active sessions, 2.18% of total activity.
  170. =》该部分指出SGA设置过小,并给出建议的SGA size.
  171. 这部分可以结合v$sga_target_advice来看
  172. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  173. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  174. Additional Information =》该部分是一些额外的信息,用于说明哪些类别没有消耗大量的数据库时间。
  175. ----------------------
  176. Miscellaneous Information
  177. -------------------------
  178. Wait class "Application" was not consuming significant database time.
  179. Wait class "Cluster" was not consuming significant database time.
  180. Wait class "Commit" was not consuming significant database time.
  181. Wait class "Concurrency" was not consuming significant database time.
  182. Wait class "Configuration" was not consuming significant database time.
  183. CPU was not a bottleneck for the instance.
  184. Wait class "Network" was not consuming significant database time.
  185. The network latency of the cluster interconnect was within acceptable limits
  186. of 1 milliseconds.
  187. Session connect and disconnect calls were not consuming significant database
  188. time.
  189. Hard parsing of SQL statements was not consuming significant database time.


阅读(1652) | 评论(0) | 转发(0) |
0

上一篇:Oracle 内存设置 SGA PGA

下一篇:没有了

给主人留下些什么吧!~~