oracle addm报错,Oracle 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 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，...，的顺序阅读就好


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


原文：https://www.cnblogs.com/mzq123/p/10745096.html