Oracle SQL调优系列之AWR报告简介

灰太狼 2021-09-19 00:48 379阅读 0赞

### 文章目录 ###

*   *  一、AWE报告生成步骤
     *   *  1.1 工具选择
         *  1.2 自动创建快照
         *  1.3 手工创建快照
         *  1.4 生成AWR报告
     *  二、AWR报告分析
     *   *  2.1 AWR之DB Time
         *  2.2 AWR之load\_profile
         *  2.3 AWR之efficiency percentages
         *  2.4 AWR之top 10 events
         *  2.5 AWR之SQL Statistics

## 一、AWE报告生成步骤 ##

对于SQL调优，局部SQL，我们可以直接使用执行计划等直接调优，而对于整个系统来说？这时候就可以用Oracle系统自带的报告对系统进行整体分析了，Oracle提供好几种性能分析的报告，比如AWR、ASH、ADDM等等  
这篇博客主要介绍AWR

AWR全称Automatic Workload Repository，自动负载信息库，是Oracle 10g版本后推出的一种性能收集和分析工具，提供了一个时间段内整个系统的报表数据。通过AWR报告，可以分析指定的时间段内数据库系统的性能。

整体分析调优工具

*  AWR：关注数据库的整体性能的报告；
 *  ASH：数据库中的等待事件与哪些SQL具体对应的报告；
 *  ADDM：oracle给出的一些建议
 *  AWRDD：Oracle针对不同时段的性能对比报告
 *  AWRSQRPT：oracle获取统计信息与执行计划

不同场景对应工具

局部分析调优工具：

*  explain plan for
 *  set autotrace on
 *  statistics\_level=all
 *  直接通过sql\_id获取
 *  10046 trace
 *  awrrpt.sql

整体性能工具要点

*  AWR关注点：load profile、efficiency percentages、top 5 time events、SQL Statistics、segment\_statistics
 *  ASH关注点：等待事件与sql完美结合
 *  ADDM：各种建议与对应SQL
 *  AWRDD：不同时期 load profile的比较、不同时期等待事件的比较、不同时期TOP SQL的比较
 *  AWRSQRPT：获取与关注点(统计信息与执行计划)

select output from table (dbms_workload_repository.awr_report_html(v_dbid,v_instance_number,v_min_snap_id,v_max_snap_id));

相关查询试图：

*  v$session (当前正在发生)
 *  v$session\_wait(当前正在等待)
 *  v$session\_wait\_history (会话最近的10次等待事件)
 *  v$active\_session\_history (内存中的ASH采集信息，理论为1小时)
 *  wrh$\_active\_session\_history (写入AWR库中的ASH信息，理论为1小时以上)
 *  dba\_hist\_active\_sess\_history (根据wrh$\_active\_session\_history生成的视图)

### 1.1 工具选择 ###

对于Oracle数据库可以使用sqlplus或者plsql developer客户端软件  
**sqlplus 使用**  
可以使用sqlplus工具登录  
进入数据库

sqlplus / as sysdba

查看用户

show parameter db_name

用登录之后才可以使用  
**plsql developer使用**  
plsql developer也可以使用，登录之后，选择文件(File)->新建(New)->命令窗口(Command Window)  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70]

### 1.2 自动创建快照 ###

开始压测后执行

exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();

可以通过dba\_hist\_wr\_control查看当前的配置情况，当前awr为每1小时做一次数据快照，保留时间为8天。

select * from dba_hist_wr_control;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 1]  
修改配置，每隔30分钟收集一次，保存1天

execute dbms_workload_repository.modify_snapshot_settings(interval=>30,retention=>14000);

关闭AWR自动收集

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

注：10g默认是自动开启awr信息收集的，会对系统有一定的影响（很小）；如果要关闭awr信息收集，只需设置interval参数为0即可。但interval设0后，AWR报告无法生成。

### 1.3 手工创建快照 ###

除了自动创建快照，也可以手工创建快照

select dbms_workload_repository.create_snapshot() from dual;

### 1.4 生成AWR报告 ###

在sqlplus或者plsql使用命令，$\{ORACLE\_HOME\}是Oracle的安装路径

@/${ORACLE_HOME}/.../RDBMS/ADMIN/awrrpt.sql

例如我的命令为：

@D:/oracle/product/11.1.0/db_1/RDBMS/ADMIN/awrrpt.sql

sqlplus登录的可以使用

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

**@?/rdbms/admin/awrrpt; 本实例AWR包括：**  
@?/rdbms/admin/awrrpti; RAC中选择实例号  
@?/rdbms/admin/awrddrpt; AWR 比对报告  
@?/RDBMS/admin/awrgrpt; RAC全局AWR报告

执行命令之后，会提示你输入一些参数

*  (1) Enter value of report\_type  
    意思是生成报告的格式有两种，html和txt，这里选择html
 *  (2) Enter value of num\_days  
    收集几天的报告信息，数字，可以输入1
 *  (3) Enter value of begin\_snap  
    输入开始快照id，要根据日志打印的快照id范围来填

例如我实验时候，日志打印的快照id范围为：6727 ~6745

Listing the last day's Completed Snapshots 
     
    INST_NAME    DB_NAME       SNAP_ID SNAPDAT            LV
    ------------ ------------ -------- ------------------ --
    orcl        ORCL              6727 17 4月  2019 00:00  1
    orcl        ORCL              6728 17 4月  2019 01:00  1
    orcl        ORCL              6729 17 4月  2019 02:00  1
    orcl        ORCL              6730 17 4月  2019 03:00  1
    orcl        ORCL              6731 17 4月  2019 04:00  1
    orcl        ORCL              6732 17 4月  2019 05:00  1
    orcl        ORCL              6733 17 4月  2019 06:00  1
    orcl        ORCL              6734 17 4月  2019 07:00  1
    orcl        ORCL              6735 17 4月  2019 08:00  1
    orcl        ORCL              6736 17 4月  2019 09:00  1
    orcl        ORCL              6737 17 4月  2019 10:00  1
    orcl        ORCL              6738 17 4月  2019 11:00  1
    orcl        ORCL              6739 17 4月  2019 12:00  1
    orcl        ORCL              6740 17 4月  2019 13:00  1
    orcl        ORCL              6741 17 4月  2019 14:00  1
    orcl        ORCL              6742 17 4月  2019 14:13  1
    orcl        ORCL              6743 17 4月  2019 14:15  1
    orcl        OANET            6744 17 4月  2019 14:16  1
    orcl        OANET            6745 17 4月  2019 14:40  1

所以我随意填写：6743

*  (4) Enter value of end\_snap  
    输入结束快照id，要根据日志打印的快照id范围来填，所以我随意填写：6745

SQL> @D:/oracle/product/11.1.0/db_1/RDBMS/ADMIN/awrrpt.sql
     
    Current Instance
    ~~~~~~~~~~~~~~~~
     
          DBID DB_NAME      INST_ INST_NAME
    ---------- ------------ ----- ------------
    4279242421 ORCL            1 orcl
    rpt_options
    ---------
    0
     
    Specify the Report Type
    ~~~~~~~~~~~~~~~~~~~~~~~
    Would you like an HTML report, or a plain text report?
    Enter 'html' for an HTML report, or 'text' for plain text
    Defaults to 'html'
    Type Specified:  html
    Cannot SET TRIMSPOOL
    Cannot SET UNDERLINE
     
     
    Instances in this Workload Repository schema
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     
    DBBID        INSTT DBB_NAME     INSTT_NAME   HOST
    ------------ ----- ------------ ------------ ------------
    * 4279242421     1 ORCL        ORCL        zwdb
     
    Using 4279242421 for database Id
    Using 1 for instance number
    dbid
    ---------
    4279242421
    inst_num
    ---------
    1
    inst_num
    ---------
    1
    dbid
    ---------
    4279242421
    max_snap_time
    ---------
    17/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 day's Completed Snapshots 
     
    INST_NAME    DB_NAME       SNAP_ID SNAPDAT            LV
    ------------ ------------ -------- ------------------ --
    orcl        ORCL              6727 17 4月  2019 00:00  1
    orcl        ORCL              6728 17 4月  2019 01:00  1
    orcl        ORCL              6729 17 4月  2019 02:00  1
    orcl        ORCL              6730 17 4月  2019 03:00  1
    orcl        ORCL              6731 17 4月  2019 04:00  1
    orcl        ORCL              6732 17 4月  2019 05:00  1
    orcl        ORCL              6733 17 4月  2019 06:00  1
    orcl        ORCL              6734 17 4月  2019 07:00  1
    orcl        ORCL              6735 17 4月  2019 08:00  1
    orcl        ORCL              6736 17 4月  2019 09:00  1
    orcl        ORCL              6737 17 4月  2019 10:00  1
    orcl        ORCL              6738 17 4月  2019 11:00  1
    orcl        ORCL              6739 17 4月  2019 12:00  1
    orcl        ORCL              6740 17 4月  2019 13:00  1
    orcl        ORCL              6741 17 4月  2019 14:00  1
    orcl        ORCL              6742 17 4月  2019 14:13  1
    orcl        ORCL              6743 17 4月  2019 14:15  1
    orcl        OANET            6744 17 4月  2019 14:16  1
    orcl        OANET            6745 17 4月  2019 14:40  1
    dbid
    ---------
    4279242421
    inst_num
    ---------
    1
    max_snap_time
    ---------
    17/04/2019
     
     
    Specify the Begin and End Snapshot Ids
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Begin Snapshot Id specified: 6743
     
    End   Snapshot Id specified: 6745
     
    bid
    ---------
    6743
    eid
    ---------
    6745
     
    inst_num
    ---------
    1
    dbid
    ---------
    4279242421
    bid
    ---------
    6743
    eid
    ---------
    6745
    Cannot SET TRIMSPOOL
    Cannot SET UNDERLINE
     
    Specify the Report Name
    ~~~~~~~~~~~~~~~~~~~~~~~
    The default report file name is awrrpt_1_6743_6745.html.  To use this name,
    press <return> to continue, otherwise enter an alternative.
     
    Using the report name awr.html 
    Started spooling to D:\Program Files\PLSQL Developer 8.0.3.1510\awr.html

## 二、AWR报告分析 ##

### 2.1 AWR之DB Time ###

DB Time主要用来判断当前系统有没有相关瓶颈，是否较为频繁访问系统导致等待时间很长？然后要怎么看？一般来说，Elapsed时间乘以CPU个数如果大于DB Time，就是正常的，系统压力不大，反之就说明压力较大，例子如图，24.93\*8很明显大于0.49，所以说明系统压力很小  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 2]

### 2.2 AWR之load\_profile ###

load\_profile指标主要用来显示当前系统的一些指示性能的总体参数，这里介绍一些Redo\_size，用来显示平均每秒的日志尺寸和平均每个事务的日志尺寸，有时候可以结合Transactions这个每秒事务数，**分析当前事务的繁忙程度**

如图，平均每秒的事务数Transactions非常小，说明系统压力非常小，一般来说Transactions不超过200都是正常的，或者200左右都是正常的，超过1000就是非常繁忙了，再看看平均每秒的日志尺寸是4位数的，平均每个事务的日志尺寸是5位数的，说明了系统访问不是很频繁，而单个业务是比较复杂的，如果反过来，平均每秒日志尺寸比平均每秒事务日志尺寸大很多，说明系统访问很频繁，而业务比较简单，不需要响应很久  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 3]

### 2.3 AWR之efficiency percentages ###

efficiency percentages是一些命中率指标。Buffer Hint、Library Hint等表示SGA(System global area)的命中率；Soft Parse指标表示共享池的软解析率，如果小于90%，就说明存在未绑定变量的情况  
![在这里插入图片描述][20190420150807279.png]

### 2.4 AWR之top 10 events ###

Top 10 Foreground Events by Total Wait Time，等待事件是衡量数据库优化情况的重要指标，通过观察Event和%DB time两列就可以直观看出当前数据库的主要等待事件  
如图可以看出系统面试的主要是CPU被占用太多了和锁等待  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 4]

### 2.5 AWR之SQL Statistics ###

SQL Statistics从几个维度列举了系统执行比较慢的SQL，可以点击，然后拿SQL去调优，调优SQL可以用执行计划看看

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 5]

对于AWR的性能指标还有很多，本博客是看了《收获，不止SQL优化》一书的笔记，这里只简单介绍一些比较重要的指标

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70]: /images/20210812/7fe29ef3fcc84a8283c037fb5301a52a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 1]: /images/20210812/da77e9b24a0049c699508c22f40394b7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 2]: /images/20210812/dde15bacbc6f49f5be435b71b73458ad.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 3]: /images/20210812/9cdc4cb5c0d944cdb52bae913185016e.png
[20190420150807279.png]: /images/20210812/868538c984d049859f5df49935529dd5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 4]: /images/20210812/f16b3fd581d84501aed12e23256471c2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 5]: /images/20210812/d2d05b0f45a24d05b04d653812335365.png