ADDM

本文章为网络笔记，看了warehouse老师的视频受益匪浅，更是感觉自己技术太过初级，特写了本笔记，方便以后反复学习！
如有任何不妥，请发邮件至102448567@qq.com删除文章！
关于warehouse：
http://blog.itpub.net/19602/viewspace-1059211/
11gR2视频第四版 8_02_addm
也是利用AWR收集的快照信息，AWR要人为的分析，ADDM直接说明问题在哪，更智能
生成

$ORACLE_HOME/rdbms/admin/addmrpt.sql

这个报告只有text格式，没有html格式

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name	 Inst Num Instance
----------- ------------ -------- ------------
  xxxxxxxxx ORCLxx		2 orclxx2


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name	   Instance	Host
------------ -------- ------------ ------------ ------------
* xxxxxxxxx	    2 ORCLxx	   orclxx2	racxx02
  xxxxxxxxx	    1 ORCLxx	   orclxx1	racxx01

Using  xxxxxxxxx for database Id
Using	       2 for instance number


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

							Snap
Instance     DB Name	    Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
orclxx2      ORCLxx	      16703 14 Mar 2021 00:00	   1
			     		  16722 14 Mar 2021 09:30	   1
			      	      16723 14 Mar 2021 10:00	   1
					      16724 14 Mar 2021 10:30	   1
					      16725 14 Mar 2021 11:00	   1
			    		  16726 14 Mar 2021 11:30	   1
					      16727 14 Mar 2021 12:00	   1

输入开始快照和结束快照，报告名字默认

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 16723
Begin Snapshot Id specified: 16723

Enter value for end_snap: 16725
End   Snapshot Id specified: 16725



Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is addmrpt_2_16723_16725.txt.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name: 

报告输入完成

End of Report
Report written to addmrpt_2_16723_16725.txt

报告如下：
生成报告的时候Oracle生成了一个任务，任务也是Oracle中的一个对象，结合调度机制可以让Oracle每天都生成一个ADDM报告

          ADDM Report for Task 'TASK_136712'
          ----------------------------------

Analysis Period  ##分析期间
---------------
AWR snapshot range from 16723 to 16725.	##快照的范围
Time period starts at 14-MAR-21 10.00.20 AM	##开始时间点
Time period ends at 14-MAR-21 11.00.25 AM	##结束时间点

Analysis Target	##分析目标
---------------
Database 'XXXXX' with DB ID xxxxxxxxx.
Database version 11.2.0.4.0.
ADDM performed an analysis of instance orclxx2, numbered 2 and hosted at
racxx02.

Activity During the Analysis Period	##在分析期间内数据库的活动情况
-----------------------------------
Total database time was 69399 seconds.	##全部的DB TIME时间
The average number of active sessions was 19.25.	##平均活动session的数量是19.25个
##我们这个addm报告的时间区间大概是3605s，DB TIME=3605*19.25=69396

Summary of Findings	##发现的问题的汇总
-------------------
   Description                               Active Sessions      Recommendations
                                             Percent of Activity	##建议
   ##描述									##活动session占用活动的
   												百分比
 ##一共发现了5个问题
   ----------------------------------------  -------------------  --------------
-
1  Top SQL Statements                        11.16 | 58           5
2  Top Segments by "User I/O" and "Cluster"  5.6 | 29.08          2
3  Global Cache Messaging                    4.87 | 25.29         1
4  Buffer Busy - Hot Objects                 4.18 | 21.7          0
5  Undersized SGA                            1.86 | 9.67          1


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


          Findings and Recommendations	##发现的问题的描述以及建议
          ----------------------------

Finding 1: Top SQL Statements
Impact ##（影响） is 11.17 active sessions, 58% of total activity.
-------------------------------------------------------
SQL statements consuming significant database time were found. These
statements offer a good opportunity for performance improvement.

   Recommendation 1: SQL Tuning
   Estimated benefit is 6.15 active sessions, 31.92% of total activity.
   --------------------------------------------------------------------
   Action
      Run SQL Tuning Advisor on the SELECT statement with SQL_ID
      "5w01g46fu8d27".
      Related Object
         SQL statement with SQL_ID 5w01g46fu8d27.
         select sum(HJJE) as HJJE,sum(YLSL) as YLSL from (select c.brxm as
         BRXM, b.fymc as FYMC, a.yldj as YLDJ, a.ylsl * nvl(a.TS,1) as YLSL,
         a.hjje as HJJE, e.personname as YSXM, d.sfrq as SFRQ,c.KDRQ as KDRQ
         from ms_yj02 a, gy_ylsf b, ms_yj01 c, ms_mzxx d, sys_personnel e
         where a.ylxh = b.fyxh and a.yjxh = c.yjxh and c.mzxh = d.mzxh and
         c.ysdm = e.personid and a.JGID = 'xxxxxxxxxxxx' and b.FYMC like
         '%多普勒%' and c.ZFPB = 0  and d.SFRQ <=  to_date( '2021-01-31
         23:59:59',  ' yyyy-mm-dd HH24:mi:ss')and d.SFRQ >=to_date('2021-01-01
         00:00:00',  ' yyyy-mm-dd HH24:mi:ss') order by d.sfrq desc )
   Rationale
      The SQL spent 99% of its database time on CPU, I/O and Cluster waits.
      This part of database time may be improved by the SQL Tuning Advisor.
   Rationale
      Database time for this SQL was divided as follows: 100% for SQL
      execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
      execution.
   Rationale
      SQL statement with SQL_ID "5w01g46fu8d27" was executed 249 times and had
      an average elapsed time of 85 seconds.
   Rationale
      I/O and Cluster wait for TABLE "BSSZSK_BANEW.MS_YJ02" with object ID
      93657 consumed 98% of the database time spent on this SQL statement.

   Recommendation 2: SQL Tuning
   Estimated benefit is 1.81 active sessions, 9.41% of total activity.
   -------------------------------------------------------------------
   Action
      Run SQL Tuning Advisor on the SELECT statement with SQL_ID
      "bh8hjuragdnkh".
      Related Object
         SQL statement with SQL_ID bh8hjuragdnkh.
         select count(*) as col_0_0_ from MS_CF01 ms_cf01x0_, MS_BRDA
         ms_brda1_ where ms_cf01x0_.BRID=ms_brda1_.BRID and ms_cf01x0_.FYBZ=0
         and (ms_cf01x0_.FPHM is not null) and ms_cf01x0_.ZFPB=0 and (YFSB=:1
         or SJYF=:2)
   Rationale
      The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
      This part of database time may be improved by the SQL Tuning Advisor.
   Rationale
      Database time for this SQL was divided as follows: 100% for SQL
      execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
      execution.
   Rationale
      SQL statement with SQL_ID "bh8hjuragdnkh" was executed 12114 times and
      had an average elapsed time of 0.59 seconds.

   Recommendation 3: SQL Tuning
   Estimated benefit is 1.33 active sessions, 6.91% of total activity.
   -------------------------------------------------------------------
   Action
      Run SQL Tuning Advisor on the SELECT statement with SQL_ID
      "08sv5tu39ayzf".
      Related Object
         SQL statement with SQL_ID 08sv5tu39ayzf.
         select a.CFSB as CFSB,c.FPHM as FPHM,b.BRXM as BRXM,b.BRXB as
         BRXB,b.CSNY as CSNY,a.YFSB as YFSB,a.CFHM as CFHM,a.CFLX as
         CFLX,a.SJJG as SJJG,a.SJYF as SJYF,a.SJFYBZ as SJFYBZ,c.BRXZ as BRXZ
         from MS_CF01 a left join MS_CF01_WS m on a.cfsb = m.cfsb,MS_BRDA
         b,MS_MZXX c where  a.BRID=b.BRID and a.MZXH = c.MZXH and ((a.FYBZ = 0
         and a.FPHM is not null) and a.ZFPB = 0) and ( YFSB=:1 or SJYF=:2 )and
         a.JGID=:3 and (m.wsfs is null or m.wsfs = 5)  order by c.SFRQ
   Rationale
      The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
      This part of database time may be improved by the SQL Tuning Advisor.
   Rationale
      Database time for this SQL was divided as follows: 100% for SQL
      execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
      execution.
   Rationale
      SQL statement with SQL_ID "08sv5tu39ayzf" was executed 12117 times and
      had an average elapsed time of 0.4 seconds.

   Recommendation 4: SQL Tuning
   Estimated benefit is .98 active sessions, 5.08% of total activity.
   ------------------------------------------------------------------
   Action
      Run SQL Tuning Advisor on the SELECT statement with SQL_ID
      "dgtr5zj23crnb".
      Related Object
         SQL statement with SQL_ID dgtr5zj23crnb.
         SELECT to_date(to_char(INPUTDATE, 'yyyy-mm-dd'), 'yyyy-mm-dd') as
         INPUTDATE FROM mdc_hpts4visit WHERE SERIALNUMBER = :1
   Rationale
      The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
      This part of database time may be improved by the SQL Tuning Advisor.
   Rationale
      Database time for this SQL was divided as follows: 100% for SQL
      execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
      execution.
   Rationale
      SQL statement with SQL_ID "dgtr5zj23crnb" was executed 16186 times and
      had an average elapsed time of 0.21 seconds.

   Recommendation 5: SQL Tuning
   Estimated benefit is .89 active sessions, 4.63% of total activity.
   ------------------------------------------------------------------
   Action
      Run SQL Tuning Advisor on the SELECT statement with SQL_ID
      "67gpyhh6jw4pn".
      Related Object
         SQL statement with SQL_ID 67gpyhh6jw4pn.
         select sum(HJJE) as HJJE,sum(YLSL) as YLSL from (select c.brxm as
         BRXM, b.fymc as FYMC, a.yldj as YLDJ, a.ylsl * nvl(a.TS,1) as YLSL,
         a.hjje as HJJE, e.personname as YSXM, d.sfrq as SFRQ,c.KDRQ as KDRQ
         from ms_yj02 a, gy_ylsf b, ms_yj01 c, ms_mzxx d, sys_personnel e
         where a.ylxh = b.fyxh and a.yjxh = c.yjxh and c.mzxh = d.mzxh and
         c.ysdm = e.personid and a.JGID = '440306008003' and b.FYMC like
         '%动态心电图%' and c.ZFPB = 0  and d.SFRQ <=  to_date( '2021-03-14

         23:59:59',  ' yyyy-mm-dd HH24:mi:ss')and d.SFRQ >=to_date('2021-03-01
         00:00:00',  ' yyyy-mm-dd HH24:mi:ss') order by d.sfrq desc )
   Rationale
      The SQL spent 99% of its database time on CPU, I/O and Cluster waits.
      This part of database time may be improved by the SQL Tuning Advisor.
   Rationale
      Database time for this SQL was divided as follows: 100% for SQL
      execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
      execution.
   Rationale
      SQL statement with SQL_ID "67gpyhh6jw4pn" was executed 38 times and had
      an average elapsed time of 83 seconds.
   Rationale
      I/O and Cluster wait for TABLE "BSSZSK_BANEW.MS_YJ02" with object ID
      93657 consumed 99% of the database time spent on this SQL statement.


Finding 2: Top Segments by "User I/O" and "Cluster"
Impact is 5.6 active sessions, 29.08% of total activity.
--------------------------------------------------------
Individual database segments responsible for significant "User I/O" and
"Cluster" waits were found.

   Recommendation 1: Segment Tuning
   Estimated benefit is 5.06 active sessions, 26.27% of total activity.
   --------------------------------------------------------------------
   Action
      Run "Segment Advisor" on TABLE "BSSZSK_BANEW.MS_YJ02" with object ID
      93657.
      Related Object
         Database object with ID 93657.
   Action
      Investigate application logic involving I/O on TABLE
      "BSSZSK_BANEW.MS_YJ02" with object ID 93657.
      Related Object
         Database object with ID 93657.
   Action
      Look at the "Top SQL Statements" finding for SQL statements consuming
      significant I/O on this segment. For example, the SELECT statement with
      SQL_ID "5w01g46fu8d27" is responsible for 100% of "User I/O" and
      "Cluster" waits for this segment.
   Rationale
      The I/O usage statistics for the object are: 288 full object scans,
      96427346 physical reads, 996 physical writes and 0 direct reads.

   Recommendation 2: Segment Tuning
   Estimated benefit is .54 active sessions, 2.82% of total activity.
   ------------------------------------------------------------------
   Action
      Investigate application logic involving I/O on TABLE
      "BSSZSK_BANEW.MS_BCJL" with object ID 93650.
      Related Object
         Database object with ID 93650.
   Rationale
      The I/O usage statistics for the object are: 0 full object scans, 117769
      physical reads, 2730 physical writes and 0 direct reads.

   Symptoms That Led to the Finding:
   ---------------------------------
      Wait class "User I/O" was consuming significant database time.
      Impact is 2.39 active sessions, 12.43% of total activity.


Finding 3: Global Cache Messaging
Impact is 4.87 active sessions, 25.29% of total activity.
---------------------------------------------------------
Inter-instance messaging was consuming significant database time on this
instance.

   Recommendation 1: Application Analysis
   Estimated benefit is 4.87 active sessions, 25.29% of total activity.
   --------------------------------------------------------------------
   Action
      Look at the "Top SQL Statements" finding for SQL statements consuming
      significant time on Cluster waits. For example, the SELECT statement
      with SQL_ID "5w01g46fu8d27" is responsible for 79% of Cluster wait
      during the analysis period.

   Symptoms That Led to the Finding:
   ---------------------------------
      Wait class "Cluster" was consuming significant database time.
      Impact is 4.88 active sessions, 25.35% of total activity.


Finding 4: Buffer Busy - Hot Objects
Impact is 4.18 active sessions, 21.7% of total activity.
--------------------------------------------------------
Read and write contention on database blocks was consuming significant
database time.

   No recommendations are available.

   Symptoms That Led to the Finding:
   ---------------------------------
      Read and write contention on database blocks was consuming significant
      database time.
      Impact is 4.18 active sessions, 21.7% of total activity.
         Inter-instance messaging was consuming significant database time on
         this instance.
         Impact is 4.87 active sessions, 25.29% of total activity.
            Wait class "Cluster" was consuming significant database time.
            Impact is 4.88 active sessions, 25.35% of total activity.


Finding 5: Undersized SGA
Impact is 1.86 active sessions, 9.67% of total activity.
--------------------------------------------------------
The SGA was inadequately sized, causing additional I/O or hard parses.
The value of parameter "sga_target" was "122880 M" during the analysis period.

   Recommendation 1: Database Configuration
   Estimated benefit is 1.86 active sessions, 9.67% of total activity.
   -------------------------------------------------------------------
   Action
      Increase the size of the SGA by setting the parameter "sga_target" to
      222720 M.

   Symptoms That Led to the Finding:
   ---------------------------------
      Wait class "User I/O" was consuming significant database time.
      Impact is 2.39 active sessions, 12.43% of total activity.



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

          Additional Information	##附加的信息、额外的信息
          ----------------------

Miscellaneous Information	##多样化的信息
##Oracle把等待事件大概归成了十多类
-------------------------
Wait class "Application" was not consuming significant database time.
Wait class "Commit" was not consuming significant database time.
##等待类commit没有消耗大量的数据库时间（commit会触发LGWR工作）
Wait class "Concurrency" was not consuming significant database time.
##等待类并发没有消耗过多的数据库时间
Wait class "Configuration" was not consuming significant database time.
##等待类配置没有消耗过多的数据库时间，也就是说配置没有问题
CPU was not a bottleneck for the instance.
##总结一句话，CPU不是这个实例的瓶颈
Wait class "Network" was not consuming significant database time.
##等待类网络没有消耗过多的数据库时间
The network latency of the cluster interconnect was within acceptable limits
of 1 milliseconds.
##群集互连的网络延迟在可接受的范围内1毫秒 
Session connect and disconnect calls were not consuming significant database
time.
##session的连接、断开、调用都没有消耗过多的数据库时间（大量的session连接会调用process对性能会有影响，断开的时候pmon进程要释放大量的资源所以说session的连接、断开会影响性能）
Hard parsing of SQL statements was not consuming significant database time.
##sql语句的硬解析没有消耗大量的数据库时间
The database's maintenance windows were active during 99% of the analysis
period.
##在99%的分析期间内，数据库的维护窗口处于活动状态

如果是windows上跑addm报告的话发现问题的第一条通常是Virtual Memory Paging（虚拟内存换页），这是因为收到了Oracle参数的影响

SQL> show parameter sga;

NAME				     TYPE	 VALUE
------------------    ----------- -------------------------
lock_sga			     boolean	 FALSE
pre_page_sga			 boolean	 FALSE	##就是这个参数
sga_max_size			 big integer 596M
sga_target			     big integer 596M

这里我们指定了sga_target为596M，但是当Oracle启动的时候并没有立马给Oracle分配596M，而是分配了一部分内存，足够让Oracle实例起来，因为如果立马分配所有的内存CPU扫描内存需要较长的时间，这样就延长了实例启动的时间，Oracle的策略是当pre_page_sga是false的时候实例启动的时候没有立即分配所有的内存，如果是true的话就是立即分配所有的sga内存
这个参数是一个静态参数

SQL> alter system set pre_page_sga = TRUE scope = spfile;

System altered.

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  622149632 bytes
Fixed Size		    	  2255792 bytes
Variable Size		      234882128 bytes
Database Buffers	      377487360 bytes
Redo Buffers		      7524352 bytes
Database mounted.
Database opened.

SQL> show parameter sga;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
lock_sga			     boolean	 FALSE
pre_page_sga			 boolean	 TRUE
sga_max_size			 big integer 596M
sga_target			     big integer 596M