使用工具来了解全局信息（awr、awrdd、addm等报告）

目录

• 使用工具来了解全局信息（awr、awrdd、addm等报告）
• 1.AWR
  • 1.1 生成awr报告
  • 1.2 awr保存策略
    • 1.2.1调整awr策略
    • 1.2.2关闭awr
    • 1.2.3手动生成快照
    • 1.2.4手工删除指定范围的快照
    • 1.2.5创建baseline，保存这些数据用于将来分析和比较
    • 1.2.6查看基线
    • 1.2.7 删除baseline
    • 1.2.8 将AWR数据导出并迁移到其它数据库以便于以后分析
    • 1.2.9 迁移AWR数据文件到其他数据库
    • 1.2.10 AWR报告显示TOP 50条SQL
• 2.ASH
• 3.ADDM
  • 3.1addm报告案例
• 4.AWRDD
• 5.AWRSQ

使用工具来了解全局信息（awr、awrdd、addm等报告）

对oracle整体分析的工具，大名鼎鼎的往往是AWR报告。其他报告可能使用的人很少，AWR报告信息固然详细，但如果不是经验颇深，一些疑难杂症可能单靠AWR的话，可能无法迅速定位。同样的工具还有ASH、ADDM、AWRDD、AWRSQRPT等，配合起来，事半功倍。在实际中当然也不能仅仅关注工具报告，也要结合系统、网络、存储综合考虑。

简单对这些工具的功能概括：

工具	功能概要
AWR	收集、处理和维护用于问题诊断的性能统计信息（整体情况）
AWRDD	不同时段的性能对比
ASH	sql对应的等待事件，更精准
ADDM	根据运行情况，给出建议
AWRSQRPT	根据sql_id得到执行计划细节

1.AWR

AWR（Automatic Workload Repository）报告是我们进行日常数据库性能评定、问题SQL发现的重要手段。

AWR 实质上是一个 Oracle 的内置工具，它采集与性能相关的统计数据，并从那些统计数据中导出性能量度，以跟踪潜在的问题。快照由一个称为 MMON 的后台进程及其从进程自动地每小时采集一次。为了节省空间，采集的数据在 7 天后自动清除。快照频率和保留时间都可以由用户修改。它产生两种类型的输出：文本格式和默认的 HTML 格式，从而提供了非常用户友好的报表。

AWR 使用几个表来存储采集的统计数据，所有的表都存储在SYSAUX 的特定表空间中的 SYS 模式下，并且以 WRM$* 和 WRH$* 的格式命名。前一种类型存储元数据信息（如检查的数据库和采集的快照），后一种类型保存实际采集的统计数据。H 代表“历史数据 (historical)”而 M 代表“元数据 (metadata)”。在这些表上构建了几种带前缀 DBA_HIST_ 的视图，这些视图可以用来编写您自己的性能诊断工具。视图的名称直接与表相关；例如，视图 DBA_HIST_SYSMETRIC_SUMMARY 是在WRH$_SYSMETRIC_SUMMARY 表上构建的。

获取 AWR 报告的方式有两种，一种是直接获取方式，调后台脚本 awrrpt.sql 来获取，执行 方式一般是在 sqlplus 下执行@?/rdbms/admin/awrrpt.sql ，另一种则是通过调用命令包，获取 dbms_workload_repository 这个包的 awr_report_html程序，用SQL命令的形式输出内容。

一般采用的还是直接调用@?/rdbms/admin/awrrpt.sql（也就是$ORACLE_HOME/rdbms/admin/awrrpt.sql）脚本生成html文件

1.1 生成awr报告

sqlplus中生成

连接到:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL>@?/rdbms/admin/awrrpt.sql

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
AWR reports can be generated in the following formats.  Please enter the
name of the format at the prompt.  Default value is 'html'.

'html'          HTML format (default)
'text'          Text format
'active-html'   Includes Performance Hub active report

输入 report_type 的值:	#回车，默认html
........
........
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.

#输入天数，不输入默认回车会列出所有快照
输入 num_days 的值:1

#截取时间段，截取的时间段中，实例要没有shutdown过
Listing the last day's Completed Snapshots
Instance     DB Name      Snap Id       Snap Started    Snap Level
------------ ------------ ---------- ------------------ ----------

orcl         ORCL                53  17 1月  2022 09:55   1
                                 54  17 1月  2022 11:00   1
                                 55  17 1月  2022 12:00   1
                                 56  17 1月  2022 13:00   1
                                 57  17 1月  2022 14:00   1
                                 58  17 1月  2022 15:00   1
                                 59  17 1月  2022 16:00   1
                                 60  17 1月  2022 17:02   1


Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
输入 begin_snap 的值:  53
Begin Snapshot Id specified: 53

输入 end_snap 的值:  55
End   Snapshot Id specified: 55



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

##awr输出位置
输入 report_name 的值:  /tmp/test_awr.html
........ 
Report written to /tmp/test_awr.html

plsql客户端生成

select * from sys.wrh$_active_session_history order by snap_id desc;


   SELECT *  FROM TABLE( dbms_workload_repository.awr_report_html(
   (select dbid
   from v$database), 1,
   56896,  -- begin  snap_id 比如上午8点的时候的snap_id
   56897  --end snap_id 比如下午5点的时候的snap_id
    ));


#把查看的结果全部展开，并全COPY 出来,保存到txt 文件，并修改后缀为html, 即生成 AWR报告。

1.2 awr保存策略

awr默认一小时生成一次快照，因为快照由一个称为 MMON 的新的后台进程及其从进程自动地每小时采集一次

查看当前awr保存策略

set linesize 250
col SNAP_INTERVAL format a20
col RETENTION format a20
SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL        CON_ID   SRC_DBID SRC_DBNAME
---------- -------------------- -------------------- ---------- ---------- ---------- --------------------------------------------------------------------------------------------------------------------------------
1620367844 +00000 01:00:00.0    +00008 00:00:00.0    DEFAULT             0 1620367844 CDB$ROOT

#也就是一小时一次快照，保留8天（一般是7或8天，10g是7天，11g之后都是8天）

1.2.1调整awr策略

调整AWR产生snapshot的频率和保留策略，如将收集间隔时间改为15 分钟一次。并且保留10天时间（单位都是分钟）：

SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>15, retention=>10*24*60);

1.2.2关闭awr

把interval设为0则关闭自动捕捉快照

SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>0);

1.2.3手动生成快照

awr频率太低，在问题发生时手动生成，以协助问题分析

SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

1.2.4手工删除指定范围的快照

SQL> exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(low_snap_id => 55, high_snap_id => 60, dbid => 2754187159);

1.2.5创建baseline，保存这些数据用于将来分析和比较

SQL> exec dbms_workload_repository.create_baseline(start_snap_id => 1000, end_snap_id => 1010, 'apply_interest_1');

1.2.6查看基线

SQL> select dbid,baseline_id,baseline_name,start_snap_id,end_snap_id from dba_hist_baseline;

1.2.7 删除baseline

SQL> exec DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE(baseline_name => 'apply_interest_1', cascade => FALSE);

1.2.8 将AWR数据导出并迁移到其它数据库以便于以后分析

SQL> exec DBMS_SWRF_INTERNAL.AWR_EXTRACT(dmpfile => 'awr_data.dmp', mpdir => 'DIR_BDUMP', bid => 1000, eid => 1010);

1.2.9 迁移AWR数据文件到其他数据库

SQL> exec DBMS_SWRF_INTERNAL.AWR_LOAD(SCHNAME => 'AWR_TEST', dmpfile => 'awr_data.dmp', dmpdir => 'DIR_BDUMP');

把AWR数据转移到SYS模式中：

SQL> exec DBMS_SWRF_INTERNAL.MOVE_TO_AWR (SCHNAME => 'TEST');

1.2.10 AWR报告显示TOP 50条SQL

exec DBMS_WORKLOAD_REPOSITORY.AWR_SET_REPORT_THRESHOLDS(top_n_sql=>50);

2.ASH

从Oracle 10g开始，Oracle引入了ASH新特性，也就是活动Session历史信息记录（Active Session History，ASH）。

ASH以v$session为基础，每秒钟采样一次，记录活动会话等待的事件。因为记录所有会话的活动是非常昂贵的，
所以不活动的会话不会被采样。采样工作由Oracle 10g新引入的一个后台进程MMNL来完成.

获取ash报告

···ash同样也可以通过调用包来生成

#同样sqlplus中@?/rdbms/admin/ashrpt.sql即可

连接到:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> @?/rdbms/admin/ashrpt.sql
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
输入 report_type 的值:		#默认html
......

Specify the timeframe to generate the ASH report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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)

Defaults to -15 mins
输入 begin_time 的值:	#默认15min之前
Report begin time specified:

Enter duration in minutes starting from begin time:
Defaults to SYSDATE - begin_time
Press Enter to analyze till current time
输入 duration 的值:		#默认当前
Report duration specified:

Using 18-1月 -22 14:29:30 as report begin time
Using 18-1月 -22 14:44:35 as report end time
.......
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is ashrpt_1_0118_1444.html.  To use this name,
press <return> to continue, otherwise enter an alternative.
输入 report_name 的值:  /tmp/ashrpt_1_0118_1444.html	#保存位置

查看ash启用状态相关

SELECT x.ksppinm NAME, y.ksppstvl VALUE,x.ksppdesc describ
FROM SYS.x$ksppi x, SYS.x$ksppcv y
WHERE x.inst_id = USERENV ('Instance')
AND y.inst_id = USERENV ('Instance')
AND x.indx = y.indx
AND x.ksppinm LIKE '%&par%'

#两个值ash_en和ash_sample

ash与awr不同的是，快照段直接有无重启不影响ash

如果是以调用包的方式：

#调用的是dbms_workload_repository包的ash_report_html
#语法如下，没测试使用过
select output from table(dbms_workload_repository.ash_report_html
( dbid,inst_num,l_btime,l_etime));

select output from table(dbms_workload_repository.ash_report_html
(1620367844,1,sysdate-30/1440,sysdate-1/1440));

#inst_num，1是标识数据库实例，如果是rac就要1和2两个
#l_btime开始时间
#l_etime结束时间

ASH信息被设计为在内存中滚动的，在需要时早期的信息是会被覆盖的。ASH记录的信息可以通过v$active_session_history视图来访问，
对于每个活动session，每次采样会在这个视图中记录一行信息。

select * from v$sgastat where name like'%ASH%';
select * from v$active_session_history;
select * from dba_hist_active_sess_history; 

3.ADDM

根据一段时间的运行情况给出相应的建议,这个报告是很有用的

同样：

SQL> @?/rdbms/admin/addmrpt

3.1addm报告案例

有台生产环境数据库，硬件配置32c，64g,业务量很小，没过一段时间，前端就很慢。重启数据库之后又正常了。

原因是应用生成的sql命中率极低，几乎全部是硬解析，没跑多久却有三亿多次硬解析，几乎80%以上的硬解析

addm中建议绑定变量等去优化，但是sql由框架生成无法更改，所以最后还是采用addm的建议：

Alternatively, you may set the parameter "cursor_sharing" to "force".（默认为exact）

alter system set cursor_sharing='force' scope=both;

刚刚修改可能不会立马看到效果，确认不影响业务后，刷新共享池在做测试

也就是只要oracle将类似sql的谓词看作是一个变量，也就意味着把它们当作一条sql
(这是开发中的问题，无法修改现有代码逻辑，只能在数据库上变动，一般不要更改这个参数)

awr报告：

在修改cursor_sharing参数之后第二天的awr:

下面是addm报告原文：

          ADDM Report for Task 'TASK_16533'
          ---------------------------------

Analysis Period
---------------
AWR snapshot range from 3224 to 3255.
Time period starts at 17-JAN-22 12.00.31 AM
Time period ends at 18-JAN-22 07.01.01 AM

Analysis Target
---------------
Database 'ORCL' with DB ID 1607561855.
Database version 11.2.0.3.0.
ADDM performed an analysis of instance orcl, numbered 1 and hosted at mtdb01.

Activity During the Analysis Period
-----------------------------------
Total database time was 86567 seconds.
The average number of active sessions was .78.

Summary of Findings
-------------------
   Description                      Active Sessions      Recommendations
                                    Percent of Activity
   -------------------------------  -------------------  ---------------
1  Hard Parse Due to Literal Usage  .39 | 50.07          1
2  Commits and Rollbacks            .13 | 16.28          2
3  Soft Parse                       .03 | 3.68           2
4  Top SQL Statements               .02 | 2.09           1


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


          Findings and Recommendations
          ----------------------------

Finding 1: Hard Parse Due to Literal Usage
Impact is .39 active sessions, 50.07% of total activity.
--------------------------------------------------------
SQL statements were not shared due to the usage of literals. This resulted in
additional hard parses which were consuming significant database time.

   Recommendation 1: Application Analysis
   Estimated benefit is .39 active sessions, 50.07% of total activity.
   -------------------------------------------------------------------
   Action
      Investigate application logic for possible use of bind variables instead
      of literals.
   Action
      Alternatively, you may set the parameter "cursor_sharing" to "force".
   Rationale
      At least 3673 SQL statements with FORCE_MATCHING_SIGNATURE
      8464371428475771961 and PLAN_HASH_VALUE 3531511240 were found to be
      using literals. Look in V$SQL for examples of such SQL statements.
   Rationale
      At least 121 SQL statements with FORCE_MATCHING_SIGNATURE
      16690906714929480517 and PLAN_HASH_VALUE 3496227569 were found to be
      using literals. An example is SQL statement with SQL_ID "2ygfgj0pbsb63".
      Related Object
         SQL statement with SQL_ID 2ygfgj0pbsb63.

   Rationale
      At least 121 SQL statements with FORCE_MATCHING_SIGNATURE
      16690906714929480517 and PLAN_HASH_VALUE 3496227569 were found to be
      using literals. An example is SQL statement with SQL_ID "183bzftd6jkvq".
      Related Object
         SQL statement with SQL_ID 183bzftd6jkvq.

   Rationale
      At least 78 SQL statements with FORCE_MATCHING_SIGNATURE
      3805397472661015378 and PLAN_HASH_VALUE 3496227569 were found to be
      using literals. An example is SQL statement with SQL_ID "gfzdcyt5mxjq5".
      Related Object
         SQL statement with SQL_ID gfzdcyt5mxjq5.

   Rationale
      At least 78 SQL statements with FORCE_MATCHING_SIGNATURE
      3805397472661015378 and PLAN_HASH_VALUE 3496227569 were found to be
      using literals. An example is SQL statement with SQL_ID "7754p6x0r6kzr".
      Related Object
         SQL statement with SQL_ID 7754p6x0r6kzr.

   Rationale
      At least 74 SQL statements with FORCE_MATCHING_SIGNATURE
      12299295095435347513 and PLAN_HASH_VALUE 1419987341 were found to be
      using literals. An example is SQL statement with SQL_ID "bmxwt43j38wb6".
      Related Object
         SQL statement with SQL_ID bmxwt43j38wb6.

   Rationale
      At least 74 SQL statements with FORCE_MATCHING_SIGNATURE
      12299295095435347513 and PLAN_HASH_VALUE 1419987341 were found to be
      using literals. An example is SQL statement with SQL_ID "0r81jw9mg5tx4".
      Related Object
         SQL statement with SQL_ID 0r81jw9mg5tx4.

   Rationale
      At least 70 SQL statements with FORCE_MATCHING_SIGNATURE
      14736279330203604968 and PLAN_HASH_VALUE 1419987341 were found to be
      using literals. An example is SQL statement with SQL_ID "4g5mh3yg5csyc".
      Related Object
         SQL statement with SQL_ID 4g5mh3yg5csyc.

   Rationale
      At least 70 SQL statements with FORCE_MATCHING_SIGNATURE
      14736279330203604968 and PLAN_HASH_VALUE 1419987341 were found to be
      using literals. An example is SQL statement with SQL_ID "0yhm43m16jc86".
      Related Object
         SQL statement with SQL_ID 0yhm43m16jc86.


   Symptoms That Led to the Finding:
   ---------------------------------
      Hard parsing of SQL statements was consuming significant database time.
      Impact is .42 active sessions, 53.92% of total activity.


Finding 2: Commits and Rollbacks
Impact is .13 active sessions, 16.28% of total activity.
--------------------------------------------------------
Waits on event "log file sync" while performing COMMIT and ROLLBACK operations
were consuming significant database time.

   Recommendation 1: Application Analysis
   Estimated benefit is .13 active sessions, 16.28% of total activity.
   -------------------------------------------------------------------
   Action
      Investigate application logic for possible reduction in the number of
      COMMIT operations by increasing the size of transactions.
   Rationale
      The application was performing 12974 transactions per minute with an
      average redo size of 558 bytes per transaction.

   Recommendation 2: Host Configuration
   Estimated benefit is .13 active sessions, 16.28% of total activity.
   -------------------------------------------------------------------
   Action
      Investigate the possibility of improving the performance of I/O to the
      online redo log files.
   Rationale
      The average size of writes to the online redo log files was 3 K and the
      average time per write was 1 milliseconds.
   Rationale
      The total I/O throughput on redo log files was 136 K per second for
      reads and 132 K per second for writes.
   Rationale
      The redo log I/O throughput was divided as follows: 0% by RMAN and
      recovery, 49% by Log Writer, 0% by Archiver, 0% by Streams AQ and 50% by
      all other activity.

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


Finding 3: Soft Parse
Impact is .03 active sessions, 3.68% of total activity.
-------------------------------------------------------
Soft parsing of SQL statements was consuming significant database time.

   Recommendation 1: Application Analysis
   Estimated benefit is .03 active sessions, 3.68% of total activity.
   ------------------------------------------------------------------
   Action
      Investigate application logic to keep open the frequently used cursors.
      Note that cursors are closed by both cursor close calls and session
      disconnects.

   Recommendation 2: Database Configuration
   Estimated benefit is .03 active sessions, 3.68% of total activity.
   ------------------------------------------------------------------
   Action
      Consider increasing the session cursor cache size by increasing the
      value of parameter "session_cached_cursors".
   Rationale
      The value of parameter "session_cached_cursors" was "50" during the
      analysis period.


Finding 4: Top SQL Statements
Impact is .02 active sessions, 2.09% 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 .01 active sessions, 1.86% of total activity.
   ------------------------------------------------------------------
   Action
      Run SQL Tuning Advisor on the SELECT statement with SQL_ID
      "aq9t7f6xqvpjs".
      Related Object
         SQL statement with SQL_ID aq9t7f6xqvpjs.
         SELECT companyname FROM license
   Rationale
      The SQL spent 89% 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: 90% for SQL
      execution, 10% for parsing, 0% for PL/SQL execution and 0% for Java
      execution.
   Rationale
      SQL statement with SQL_ID "aq9t7f6xqvpjs" was executed 20449339 times
      and had an average elapsed time of 0.000062 seconds.



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

          Additional Information
          ----------------------

Miscellaneous Information
-------------------------
Wait class "Application" was not consuming significant database time.
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.
Wait class "Network" was not consuming significant database time.
Wait class "User I/O" was not consuming significant database time.
Session connect and disconnect calls were not consuming significant database
time.

The database's maintenance windows were active during 19% of the analysis
period.

4.AWRDD

awrdd要输入两次begin snap_id和end snap_id也就是两个时间段，如何精选对比，生成报告

SQL> @?/rdbms/admin/awrddrpt

5.AWRSQ

与其他报告获取不同的是，它需要一个SQL的SQL_ID

常见的操作是在awr报告中拿到需要分析的SQL_ID，然后将这个SQL_ID给awrsq报告

报告包含了详细的执行计划和统计等

SQL> @?/rdbms/admin/awrsqrpt
......
输入 num_days 的值:  1
......
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
输入 begin_snap 的值:  69
Begin Snapshot Id specified: 69

输入 end_snap 的值:  70
End   Snapshot Id specified: 70


#这个sql_id来自awr中的一个sql，它需要结合快照段来分析
Specify the SQL Id
~~~~~~~~~~~~~~~~~~
输入 sql_id 的值:  drktaf71uygnb

Listing all available Container DB Ids for SQL Id drktaf71uygnb
  Container DB Id Container Name
----------------- --------------
*      1620367844 CDB$ROOT

Using Container DB Id 1620367844


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

输入 report_name 的值:  /tmp/awrqs_test.html

示例：

......