1  说明

从Oracle Database 12c开始，Real-time ADDM会主动检测某个时间点的数据库性能问题。为了做到这一点，实时ADDM每3秒钟自动运行一次，并使用内存数据来诊断数据库中的任何性能峰值。

触发条件：

1.  每三秒，由MMON进程获取没有被加锁或latch的性能数据。

2.  满足下表里任何一个问题，都会自动运行ADDM。

3.  使用MMON的隶属进程生成报告，并存储在AWR中。

Issue	Conditionhttp://www.cndba.cn/Expect-le/article/2157
High load	Average active sessions are greater than 3 times the number of CPU coreshttp://www.cndba.cn/Expect-le/article/2157
I/O boundhttp://www.cndba.cn/Expect-le/article/2157	I/O impact on active sessions based on single block read performance
CPU bound	Active sessions are greater than 10% of total load and CPU utilization is greater than 50%
Over-allocated memory	Memory allocations are over 95% of physical memory http://www.cndba.cn/Expect-le/article/2157
Interconnect bound	Based on single block interconnect transfer time
Session limit	Session limit is close to 100%
Process limit	Process limit is close to 100%
Hung sessionhttp://www.cndba.cn/Expect-le/article/2157	Hung sessions are greater than 10% of total sessionshttp://www.cndba.cn/Expect-le/article/2157
Deadlock detected	Any deadlock is detected

2  实验

$ORACLE_HOME/rdbms/admin中的rtaddmrpt.sql和rtaddmrpti.sql两个脚本用来生成Real-time ADDM报告。

rtaddmrpti.sql区别就是可以指定dbid和报告开始时间

2.1   手动生成ADDM报告

SQL> @$ORACLE_HOME/rdbms/admin/rtaddmrpt.sql
Current Database
~~~~~~~~~~~~~~~~
     DB Id
----------
1463743464
 
Instances in this Report reposistory
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     Db Id Inst Num
---------- --------
1463743464	  1
Default to current database
Using database id:
146374346
 
Enter begin time for report:
--    Valid input formats:
--	To specify absolute begin time:
--	  [MM/DD[/YY]] HH24:MI[:SS]
--	  Examples: 02/23/03 14:30:15
--	    02/23 14:30:15
--	    14:30:15
--	    14:30
--	To specify relative begin time: (start with '-' sign)
--	   -[HH24:]MI
--	   Examples: -1:15 (SYSDATE - 1 Hr 15 Mins)
--	     -25   (SYSDATE - 25 Mins)
 
Default to -60 mins
Report begin time specified:
 
Enter duration in minutes starting from begin time:
Defaults to SYSDATE - begin_time
Press Enter to analyze till current time
Report duration specified:
 
Using 04/08/2017 13:18:02 as report begin time
Using 04/08/2017 14:18:02 as report end time
 
Report ids in this workload repository.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
     DB Id REPORT_ID TIME	  trigger_cause     impact
---------- --------- -------------------- ------------------------- ----------
1463743464	 101 04/08/2017 13:22:48  High Load 0 --可以看到触发条件是High Load
1463743464	 102 04/08/2017 14:18:00  High Load	    .01
 
Select a report id from the list. If the report id list is empty,
please select a different begin time and end time.
Enter value for report_id: 102
Report id specified : 102
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is rtaddmrpt_0804_1418.html. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name:
 
Using the report name	   rtaddmrpt_0804_1418.html
略....
<!--FXTMODEL--></script></body></html>
Report written to rtaddmrpt_0804_1418.html

报告结果：

2.2   比较不同时间段的ADDM报告

2.2.1  查看Snapshots

http://www.cndba.cn/Expect-le/article/2157

SET LINESIZE 120
COLUMN begin_interval_time FORMAT A30
COLUMN end_interval_time FORMAT A30
COLUMN startup_time FORMAT A30
SELECT snap_id, begin_interval_time, end_interval_time, startup_time
FROM   dba_hist_snapshot
WHERE  begin_interval_time > TRUNC(SYSTIMESTAMP)
ORDER BY snap_id;

 

2.2.2  数据库级别

compare_database函数生成一个HTML或XML报告，比较同一数据库或两个不同数据库的两个时间段。当比较两个数据库时，所有AWR数据必须存储当前数据库中。如果将BASE_DBID和COMP_DBID参数省略或设置为NULL，则默认是当前数据库。REPORT_TYPE参数决定报告是HTML还是XML格式。

http://www.cndba.cn/Expect-le/article/2157http://www.cndba.cn/Expect-le/article/2157

SET LONG 1000000 LONGCHUNKSIZE 1000000
SET LINESIZE 1000 PAGESIZE 0
SET TRIM ON TRIMSPOOL ON
SET ECHO OFF FEEDBACK OFF
SPOOL /tmp/addm_compare_db_report.html
SELECT DBMS_ADDM.compare_databases (
         base_begin_snap_id => 35,
         base_end_snap_id   => 36,
         comp_begin_snap_id => 39,
         comp_end_snap_id   => 40,
         report_type        => 'HTML') AS report  --或者XML
FROM   dual;
SPOOL OFF

结果：

可以很容易对比出同一个数据库不同时间段的性能情况，或者不同数据库同一时间段的性能情况。

 

2.2.3  实例级别的比较

这个对于RAC尤其有用，很容易比较两个节点的性能情况。

SET LONG 1000000 LONGCHUNKSIZE 1000000
SET LINESIZE 1000 PAGESIZE 0
SET TRIM ON TRIMSPOOL ON
SET ECHO OFF FEEDBACK OFF

SPOOL /tmp/addm_compare_inst_report.html
SELECT DBMS_ADDM.compare_instances (
         base_instance_id   => 1,
         base_begin_snap_id => 35,
         base_end_snap_id   => 36,
         comp_instance_id   => 1,
         comp_begin_snap_id => 37,
         comp_end_snap_id   => 38,
         report_type        => 'HTML') AS report
FROM   dual;
SPOOL OFF

版权声明：本文为博主原创文章，未经博主允许不得转载。

Spot ADDM