Oracle活动会话历史(ASH)及报告解读

淩亂°似流年 2022-06-12 23:26 447阅读 0赞

对于数据库运行期间的各种状态的实时监控以及相关性能数据捕获对于解决性能问题，提高整体业务系统运行效率是至关重要的。在Oracle数据库中，实时捕获相关性能数据是通过ASH工具来实现的。ASH通过每秒钟抽取活动会话样本，为分析在最近时刻的性能问题提供最直接最有效的依据。本文主要讲述ASH的用法及使用。

## 一、活动会话历史概述 ##

Oracle v$active\_session\_history视图提供了实例中的活动会话采样。通过该视图提供的最详细最完整性能数据，可作为定位性能故障的一手证据。任一连接到数据库时，那些不属于空闲等待类的事件的会话被认为是活动会话。这包括在采样时在CPU上的任何会话。

活动会话样本存储在SGA中的循环缓冲区中。随着系统活动的增加，可以存储在循环缓冲区中的会话活动的秒数将减少。会话样本的时间保留在v$视图中。在v$视图中显示的会话活动的秒数是完全依赖于数据库活动的。

当自动工作负载信息库（AWR）快照的创建，动态性能视图v$active\_session\_history的内容被刷新到磁盘。通过只捕获活动会话，表示一组可管理的数据，它的大小直接关系到正在执行的工作，而不是系统上允许的会话数。

数据字典dba\_hist\_active\_sess\_history是视图v$active\_session\_history的历史数据，dba\_hist\_active\_sess\_history视图默认每十秒收集一次信息储存在磁盘中。也就是说最终保存到磁盘的数据也就是实时收集数量量的1/10。相应地，可用于诊断性能的数据也就没有v$active\_session\_history更详细，更丰富。

活动会话数据流：

v$session(ASH Buffer) —>v$active\_session\_history—>dba\_hist\_active\_sess\_history(AWR仓库)

活动会话历史样本通常包括如下：

SQL语句的SQL标识符

对象号，文件号和块号

等待事件标识符和参数

会话标识符和会话序列号

模块和动作名称

会话的客户端标识符

服务哈希标识符

阻塞会话

## 二、活动会话历史逻辑架构图 ##

![这里写图片描述][SouthEast]

如上图所示，ASH从V$SESSION提取信息样本。每秒提取一个样本，直接读取Oracle使用的特定结构数据，而不是使用SQL，因此该方式比较高效。

ASH被设计为内存中的滚动缓冲区，以前的信息在需要时被覆盖。由于ASH缓冲区中的数据量可能非常大，并且将其全部刷新到磁盘是不可接受的。更有效的方法是过滤历史数据，同时将其刷新到工作负载存储库。每隔60分钟通过可管理性监视器（MMON）进程自动执行此操作，并且每当缓冲区已满时，都通过MMNL进程完成。

注意：ASH的存储器来自系统全局区域（SGA），它在实例的使用寿命期间是固定的。它代表每个CPU 2 MB的内存。 ASH不能超过共享池大小的百分之五，也就是SGA\_TARGET的百分之五。

SQL> select * from v$version where rownum<2;
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    
    SQL> select pool, name, bytes/1024/1024 From v$sgastat where name like '%ASH %';
    
    POOL         NAME                       BYTES/1024/1024
    ------------ -------------------------- ---------------
    shared pool  ASH buffers                     10.4000015
    
    SQL> select 'Leshami' Author,'http://blog.csdn.net/leshami' Blog,
      2  '645746311' QQ from dual;
    
    AUTHOR  BLOG                         QQ
    ------- ---------------------------- ---------
    Leshami http://blog.csdn.net/leshami 645746311

## 三、ASM采样示例 ##

如前所述，ASH代表了近期活动的历史。 该图显示了当活动时如何采样会话。 每秒钟，Oracle数据库服务器查看活动会话，并记录这些会话正在等待的事件。 非活动会话不被采样。 采样非常高效，因为它直接访问Oracle数据库内部结构。  
![这里写图片描述][SouthEast 1]

如上图中，活动会话1 Wait I/O以及Wait Block被记录到v$active\_session\_history视图。

## 四、访问活动会话数据 ##

检查当前活动会话历史：v$active\_session\_history

检查活动会话历史数据：dba\_hist\_active\_sess\_history

生成ASH报告

通过OEM 诊断包性能页面

## 五、生成ASH报告 ##

SQL> @?/rdbms/admin/ashrpt.sql
    
    Current Instance
    ~~~~~~~~~~~~~~~~
       DB Id    DB Name      Inst Num Instance
    ----------- ------------ -------- ------------
       42938845 ORA11G              1 ora11g
    
    Specify the Report Type 
    ~~~~~~~~~~~~~~~~~~~~~~~
    Enter 'html' for an HTML report, or 'text' for plain text
    Defaults to 'html'
    Enter value for report_type: html --指定报告类型，此处使用html
    Type Specified:  html
    
    Instances in this Workload Repository schema
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
       DB Id     Inst Num DB Name      Instance     Host
    ------------ -------- ------------ ------------ ------------
    * 42938845          1 ORA11G       ora11g       ydq05
    
    Defaults to current database
    Using database id: 42938845
    
    Enter instance numbers. Enter 'ALL' for all instances in a
    RAC cluster or explicitly specify list of instances (e.g., 1,2,3).
    Defaults to current instance.
    Using instance number(s): 1 --输入实例号，RAC可以选择ALL或者特定实例号
    
    ASH Samples in this Workload Repository schema--当前已保存的ASH sample数据
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Oldest ASH sample available:19-Apr-17 22:00:37[  12689 mins in the past]
    Latest ASH sample available:28-Apr-17 17:31:09[      0 mins in the past]
    
    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         缺省为-15分钟
    Enter value for begin_time:  未输入则使用缺省值
    Report begin time specified:
    
    Enter duration in minutes starting from begin time:
    Defaults to SYSDATE - begin_time
    Press Enter to analyze till current time
    Enter value for duration:    输入持续时间，缺省为当前 
    Report duration specified:
    
    Using 28-Apr-17 17:16:09 as report begin time
    Using 28-Apr-17 17:31:09 as report end time
    
    Specify Slot Width (using ashrpti.sql) for 'Activity Over Time' section
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    
    -- Explanation:
    -- In the 'Activity Over Time' section of the ASH report,
    -- the analysis period is divided into smaller slots
    -- and top wait events are reported in each of those slots.
    
    -- Default:
    -- The analysis period will be automatically split upto 10 slots
    -- complying to a minimum slot width of
    -- 1 minute, if the source is V$ACTIVE_SESSION_HISTORY or
    -- 5 minutes, if the source is DBA_HIST_ACTIVE_SESS_HISTORY.
    
    Specify Slot Width in seconds to use in the 'Activity Over Time' section:
    Defaults to a value as explained above:
    Slot Width specified:
    
    -- 以下部分描述如何生成特定的ASH报告，如根据SESSION，SQL_ID，模块等生成，省略
                   ....................
    Specify the Report Name
    ~~~~~~~~~~~~~~~~~~~~~~~
    The default report file name is ashrpt_1_0428_1731.html.  To use this name,
    press <return> to continue, otherwise enter an alternative.
    Enter value for report_name: 
    
    Using the report name ashrpt_1_0428_1731.html

## 六、ASH报告结构 ##

ASH报告结构，如下图所示：  
![这里写图片描述][SouthEast 2]

Top Events：

报告的用户事件，背景，事件，和事件的参数值

Load Profile：  
  报告顶级服务/模块，顶级客户端，识别SQL命令和执行SQL的类型

Top SQL：  
  报告与首要事件相关SQL语句，与rowsources相关SQL，完整SQL语句，SQL语句绑定变量使用

Top PL/SQL Procedures：  
  列出的PL/SQL程序，占百分比最高的采样会话活动

Top Java Workload：  
  描述了在采样会话活动java程序

Top Sessions：  
  首要会话，阻塞会话和并行相关会话

Top Objects/Files/Latches：  
  报告对象，文件，或锁存

Activity Over Time：  
  报告前三等待事件为10个同样大小的时间报告期，该报告可以让你看到在最后时刻非常详细的活动。

## 七、ASH报告分析 ##

### 1、头部信息： ###

![这里写图片描述][SouthEast 3]  
注，DataSource 可以有2个来源，一个是V$ACTIVE\_SESSION\_HISTORY，一个是DBA\_HIST\_ACTIVE\_SESS\_HISTORY

### 2、首要等待事件 ###

首要等待事件部分描述了被抽样会话活动中由用户，后台等产生的首要等待事件，首要等待事件，意味着采样期间这些事件是产生性能问题的根源。  
![这里写图片描述][SouthEast 4]  
首要等待事件包含以下部分:  
（1）Top User Events首要用户事件  
首要用户事件，也成为前台等待事件，信息显示了在抽样会话活动中占很高百分比的用户进程等待事件。

（2）Top Background Events首要后台事件  
这部分信息显示了在抽样会话活动中占很高百分比的后台进程等待事件。

（3）Top Event P1/P2/P3 Values首要等待事件参数P1/P2/P3  
这部分信息显示了在抽样会话活动中占很高百分比的等待事件的参数值它通过总的等待时间(%Event)百分比进行排序后被显示.对于每一个等待事件p1，p2，p3的值与等待事件参数parameter 1，parameter 2，parameter 3这三个列相关联，分别是文件号，块号，set-id\#

如上图所示，当前的数据库主要事件为

free buffer waits  
  服务器进程扫描LRU列表获得空闲的缓冲区（例如，从磁盘读取数据块，或者构造一致性读Cr块等到缓冲区时）。扫描到一个阈值后，如果服务器进程无法找到可用缓冲区，它请DBWR从LRU列表将脏缓冲区写出到磁盘，等待直到缓冲释放。在DBWR写出脏缓冲释放前的等待，称为free buffer waits。通常2个主要的情形导致free buffer waits，一个是DBWR写出脏块速度不够快，二是Buffer cache过小。

write complete waits  
  DBWR将脏缓冲区记录到磁盘上的期间，对缓冲区以exclusive模式占有buffer lock，此时，读取或修改缓冲区的其他进程就需要等待此项工作结束，这时等待write complete waits事件。

buffer busy waits  
  缓冲区繁忙等待，发生这个事件的两个主要情况是：另一个会话正将块读到缓冲区中；另一个会话以不兼容的方式持有我们所请求的有缓冲区。

### 3、Load Profile ###

![这里写图片描述][SouthEast 5]

### 4、Top SQL ###

![这里写图片描述][SouthEast 6]

### 5、完整SQL列表 ###

![这里写图片描述][SouthEast 7]

### 6、首要会话 ###

![这里写图片描述][SouthEast 8]

### 7、首要对象，文件，栓 ###

![这里写图片描述][SouthEast 9]

### 8、分时活动 ###

![这里写图片描述][SouthEast 10]  
该部分内容将报告期间按不同时间片段来展现活动等待事件。  
如上图所示，activity over time被分成8个时段，前3个等待事件会出现在每一个时间段。  
从上图可知，基本上来说，整个采样期间都是经历三个和buffer相关的等待事件。%event并没有出现明显的尖波。

下面是每列的描述  
列 描述  
slot time(持续时间) 时段的持续时间  
solt count 在时段中抽样会话的数量  
event 在时段中顶级的三个等待事件  
event count ash抽样等待的等待事件的数量  
%event ash抽样等待的等待事件在整个分析期间所占的百分比

### 9、报告得到的初步结论 ###

1) 整个采样期间，OLTP特征显著，主要表现为大量的DML操作  
2) 首要的等待事件表现为Buffer相关，很容易联想到增加Buffer size，但是从报告头部可知Buffer 仅占用23.5%  
3) 对于首要等待事件，DBWR存在调整及优化的可能，例如增加DBWn进程数目，加快写入，以及优化磁盘IO  
4) 优化检查点，以加快数据写出到磁盘  
5) 优化SQL以减少过多的IO负载，同时也可以考虑优化SQL所在的包，存储过程  
6) 热对象的分区以及索引分离，反向索引设计等

## 八、更多参考 ##

[Oracle ADDM性能诊断利器及报告解读][Oracle ADDM]  
[Oracle AWR特性描述][Oracle AWR]  
[Oracle AWR管理与维护][Oracle AWR 1]  
[Oracle自动性能统计][Oracle]  
[Oracle Time Model Statistics(时间模型统计)][Oracle Time Model Statistics]

[![DBA牛鹏社(SQL/NOSQL/LINUX)][DBA_SQL_NOSQL_LINUX]][DBA_SQL_NOSQL_LINUX_DBA_SQL_NOSQL_LINUX]

![这里写图片描述][1493001734_6055.jpg]

[SouthEast]: /images/20220613/d59416c675d1414c9e955955fb50d92d.png
[SouthEast 1]: /images/20220613/6e51df52690849c7931f1b58b30d68fd.png
[SouthEast 2]: /images/20220613/14077f801b5e4f28acbc0b113cffcb0e.png
[SouthEast 3]: /images/20220613/65f43b416c2b45adb4819477324b0ce0.png
[SouthEast 4]: /images/20220613/2e3d34f8dfa34efcb218704c7e3c48a5.png
[SouthEast 5]: /images/20220613/5d7c8f4c735c4491a78dfd6ad926a337.png
[SouthEast 6]: /images/20220613/6221ddc6e7584745838fd34a984bebf7.png
[SouthEast 7]: /images/20220613/5f2df68740bb4060aef55748e7f90ba1.png
[SouthEast 8]: /images/20220613/520a7bb93c2747f9a437f8639061ea82.png
[SouthEast 9]: /images/20220613/4534bd2f60c34a589a81bd3760301f53.png
[SouthEast 10]: /images/20220613/557e8b604a2a4ecb93ef5d9dd69f967b.png
[Oracle ADDM]: http://blog.csdn.net/leshami/article/details/70859672
[Oracle AWR]: http://blog.csdn.net/leshami/article/details/70569966
[Oracle AWR 1]: http://blog.csdn.net/leshami/article/details/70578291
[Oracle]: http://blog.csdn.net/leshami/article/details/70154415
[Oracle Time Model Statistics]: http://blog.csdn.net/leshami/article/details/69267347
[DBA_SQL_NOSQL_LINUX]: http://pub.idqqimg.com/wpa/images/group.png
[DBA_SQL_NOSQL_LINUX_DBA_SQL_NOSQL_LINUX]: /images/20220613/50f7ad95d90f4f06b15d55ffe3246cb3.png
[1493001734_6055.jpg]: https://img-my.csdn.net/uploads/201704/24/1493001734_6055.jpg