专栏首页Nicky's blogOrace ADDM报告和性能分析

Orace ADDM报告和性能分析

我写的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 <return> 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 <return> 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,…,的顺序阅读就好

下面的图来自《收获,不止SQL优化》一书:

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Elasticsearch系列之极速入门与实践教程

    NASA has once again delayed the launch of its new powerful space observatory, th...

    SmileNicky
  • 异步调用导致的不同步问题

    本文链接:https://blog.csdn.net/u014427391/article/details/100044661

    SmileNicky
  • Oracle体系结构学习笔记

    版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/u014427391/article/details/...

    SmileNicky
  • What is base..ctor(); in C#?

    landv
  • MCC:用PNML格式展开彩色培养网的工具(CS)

    在本文中,我们介绍了MCC,MCC是为一项非常具体的任务设计的工具:将PNML语法中给出的高级Petri网模型转换为等效的Place/Transition网。 ...

    时代在召唤
  • Qt学习之路_14(简易音乐播放器)

    http://www.cnblogs.com/tornadomeet/archive/2012/09/23/2699077.html

    bear_fish
  • Qt学习之路_14(简易音乐播放器)

      这一节实现一个简易的音乐播放器,其音乐播放的核心功能是采用Qt支持的Phonon框架,该框架在前一篇博文Qt学习之路_13(简易俄罗斯方块) 中已经使用过...

    bear_fish
  • 外键要建立索引的原理和实验

    项目中,我们要求凡是有主子关系的表都要使用外键约束,来保证主子表之间关系的正确,不推荐由应用自己控制这种关系。

    bisal
  • 进程间通信 -- 各种通信方式的一方天地

    今天突然被 ==“不同场景下该如何选择进程间通信方式?”==给噎着了,这我还真没认真想过,以前只知道说它们都是什么?为什么?怎么用?还真没想过什么时候用谁?这个...

    看、未来
  • 走进 Java Volatile 关键字

    Java Volatile 关键字是一种轻量级的数据一致性保障机制,之所以说是轻量级的是因为 volatile 不具备原子性,它对数据一致性的保障体现在对修改过...

    不会飞的小鸟

扫码关注云+社区

领取腾讯云代金券