我写的SQL调优专栏:https://blog.csdn.net/u014427391/article/category/8679315
对于局部的，比如某个页面列表sql，我们可以使用Oracle的执行计划进行sql调优，但是对于整个系统来说，你可以知道哪些sql比较耗时?当然可以通过查Oracle的共享池得到，不过Oracle系统本身就提供了几种性能分析报告，比如AWR、ASH、ADDM、AWRDD等等报告，本博客介绍一下ADDM性能分析报告
ADDM全称是Automatic Database Diagnostic Monitor，是Oracle一个实现性能自我诊断的最佳利器。它依赖于AWR，也就是说ADDM要诊断，必要要有诊断的依据。在Oracle中，这个诊断依据就是Oracle AWR，因为Oracle AWR会定期的收集整个数据库在运行期间的性能统计数据。
一、ADDM报告生成
继之前AWR、ASH方面的博客之后，https://smilenicky.blog.csdn.net/article/details/89414432，https://smilenicky.blog.csdn.net/article/details/89419185，我再写一篇ADDM方面的博客:
1.1 工具选择
对于Oracle数据库可以使用sqlplus或者plsql developer客户端软件sqlplus 使用可以使用sqlplus工具登录进入数据库
sqlplus / as sysdba
查看用户
show parameter db_name
用登录之后才可以使用plsql developer使用plsql developer也可以使用，登录之后，选择文件(File)->新建(New)->命令窗口(Command Window)
在这里插入图片描述1.2 自动创建快照
开始压测后执行
exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
可以通过dba_hist_wr_control查看当前的配置情况，当前awr为每1小时做一次数据快照，保留时间为8天。
select * from dba_hist_wr_control;
在这里插入图片描述
修改配置，每隔30分钟收集一次，保存1天
execute dbms_workload_repository.modify_snapshot_settings(interval=>30,retention=>14000);
关闭自动收集
SQL>exec dbms_workload_repository.modify_snapshot_settings (interval=>0,retention=>24*60);
1.3 手工创建快照
除了自动创建快照，也可以手工创建快照
select dbms_workload_repository.create_snapshot() from dual;
1.4 ADDM报告生成
对于sqlplus客户端的可以使用
@?/rdbms/admin/addmrpt.sql
对于plsql客户端，我用绝对路径去执行，@?的命令找不到文件这个要根据自己的Oracle安装路径去修改，例如:
@D:/oracle/product/11.1.0/db_1/RDBMS/ADMIN/addmrpt.sql
(1)快照开始id
Enter value for begin_snap:418
要根据日志打印的快照id范围来填，所以我可以填写:418
(2)快照结束id
Enter value for end_snap:420
要根据日志打印的快照id范围来填，所以我可以填写:420
(3)ADDM报告名称
Enter value for report_name
填写ADDM报告的名称，我可以填写addm_20190421.html，然后在打印的日志里有文件保存的路径:，比如:D:\oracle\product\11.2.0\dbhome_1\RDBMS\ADMIN\addm.html
SQL> @D:/oracle/product/11.2.0/dbhome_1/RDBMS/ADMIN/addmrpt.sql Current Instance ~~~~~~~~~~~~~~~~ DBID DB_NAME INST_ INST_NAME ---------- ------------ ----- ------------ 1525762377 ORCL 1 orcl Cannot SET TRIMSPOOL Cannot SET UNDERLINE Instances in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DBBID INSTT DBB_NAME INSTT_NAME HOST ------------ ----- ------------ ------------ ------------ * 1525762377 1 ORCL orcl PC-201508171 906 Using 1525762377 for database Id Using 1 for instance number PL/SQL procedure successfully completed dbid --------- 1525762377 inst_num --------- 1 PL/SQL procedure successfully completed inst_num --------- 1 dbid --------- 1525762377 max_snap_time --------- 18/04/2019 Specify the number of days of snapshots to choose from ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Entering the number of days (n) will result in the most recent (n) days of snapshots being listed. Pressing without specifying a number lists all completed snapshots. Listing the last 3 days of Completed Snapshots INST_NAME DB_NAME SNAP_ID SNAPDAT LV ------------ ------------ -------- ------------------ – orcl ORCL 417 16 Apr 2019 00:00 1 orcl ORCL 418 16 Apr 2019 19:48 1 orcl ORCL 419 16 Apr 2019 21:00 1 orcl ORCL 420 16 Apr 2019 22:00 1 orcl ORCL 421 16 Apr 2019 23:00 1 orcl ORCL 422 17 Apr 2019 21:20 1 orcl ORCL 423 17 Apr 2019 22:00 1 orcl ORCL 424 17 Apr 2019 23:00 1 orcl ORCL 425 18 Apr 2019 00:00 1 orcl ORCL 426 18 Apr 2019 21:26 1 orcl ORCL 427 18 Apr 2019 22:00 1 11 rows selected dbid --------- 1525762377 inst_num --------- 1 max_snap_time --------- 18/04/2019 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Begin Snapshot Id specified: 418 End Snapshot Id specified: 420 PL/SQL procedure successfully completed bid --------- 418 eid --------- 420 PL/SQL procedure successfully completed inst_num --------- 1 dbid --------- 1525762377 bid --------- 418 eid --------- 420 Cannot SET TRIMSPOOL Cannot SET UNDERLINE Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is addmrpt_1_418_420.txt. To use this name, press to continue, otherwise enter an alternative. Using the report name addm.html Running the ADDM analysis on the specified pair of snapshots … bid --------- 418 eid --------- 420 inst_num --------- 1 dbid --------- 1525762377 task_name --------- TASK_953 Generating the ADDM report for this analysis … Started spooling to D:\oracle\product\11.2.0\dbhome_1\RDBMS\ADMIN\addm.html ADDM Report for Task ‘TASK_953’ ------------------------------- Analysis Period --------------- AWR snapshot range from 418 to 420. Time period starts at 16-APR-19 07.48.09 PM Time period ends at 16-APR-19 10.00.20 PM Analysis Target --------------- Database ‘ORCL’ with DB ID 1525762377. Database version 11.2.0.1.0. ADDM performed an analysis of instance orcl, numbered 1 and hosted at PC-201508171906. Activity During the Analysis Period ----------------------------------- Total database time was 57 seconds. The average number of active sessions was .01. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ There are no findings to report. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Additional Information ---------------------- Miscellaneous Information ------------------------- There was no significant database activity to run the ADDM. The database’s maintenance windows were active during 99% of the analysis period. task_name --------- TASK_953 Stopped spooling to D:\oracle\product\11.2.0\dbhome_1\RDBMS\ADMIN\addm.html End of Report Report written to addm.html.
二、ADDM报告性能分析
ADDM性能报告是从数据库的整体配置和局部SQL方面给出建议，阅读时候按FINDING1，FINDING2，…，的顺序阅读就好
