Oracle等待事件分析与AWR和ASH报告

1 ASH和AWR

1.1 ASH

用户的连接将产生会话，当前会话记录保存在v$session中，通过该视图，DBA可以查看用户实际执行的操作，或者当前的等待事件等。
处于等待状态的会话会被复制一份放在v$session_wait中。
但是，一旦连接断开，其原来的连接信息在v$session和v$session_wait中就会被删除。这是10g之前的状况。

若是一个普通的会话一般没有大量地耗费资源，则对于性能调整来说无足轻重。但若该会话在活动时大量占用了资源（比如：CPU，内存，I/O等），该会话信息的丢失，将无法评测当时的系统瓶颈究竟是什么。令DBA高兴的是，Oracle 10g中保留下了v$session_wait中的这些信息。
在Oracle 10g以后新出现了一个视图：v$session_wait_history。这个视图保存了每个活动session在v$session_wait中最近10次的等待事件。但这对于一段时期内的数据库性能状况的监测是远远不够的，为了解决这个问题，在10g中还新添加了一个视图：v$active_session_history，活动会话的历史记录。即使用户操作完成后，断开了连接也不怕，因为其会话的情况已经被记录了下来，这项特性就是ASH了，全称与视图名相同，正是：ACTIVE SESSION HISTORY。
典型的情况下，为了诊断当前数据库的状态，需要最近的五到十分钟的详细信息。然而，由于记录session的活动信息是很费时间和空间的，ASH采用的策略是：保存处于等待状态的活动session的信息，每秒从v$session_wait中采样一次，并将采样信息保存在内存中。

1.1.1 ash占用的内存大小

ASH的采集信息保存在内存中，在旧的信息被采样到AWR中后，可被新采集的信息覆盖，重启oracle后该信息被清除。动态性能视图其实上是ORACLE自行构造的一堆存在于SGA内存区的虚表，就是说，ASH的数据是保存在内存里的，实际上，ORACLE分配给ASH的空间并不是无限大(更何况ORACLE自身管理的内存空间也不是无限大)，分配给ASH的内存大小可以查询到：

SQL> select pool,name,bytes/1024/1024  Mb  From v$sgastat where name like 'ASH%';POOL         NAME                MB
------------ -------------------------- ----------
shared pool  ASH buffers            96

直白的讲 ，v$active_session_history中能够记录多少会话信息， 一方面取决于该数据库的SGA分配给ASH buffers的大小 ，另一方面取决于数据库的启动和关闭(重启数据库时将重构SGA内存区)。这两方面的因素制约了v$active_session_history中能够保存的会话信息的能力，我们肯定是希望ASH尽可能多的保留关于会话的信息，但目前来看单纯依靠v$active_session_history肯定无法实现这点，ORACLE又提供了AWR特性，ASH收集到的会话信息，是做为AWR中快照信息的一部分，被保存到了硬盘上。

1.2 AWR

ASH的采样数据是保存在内存中。而分配给ASH的内存空间是有限的，当所分配空间占满后，旧的记录就会被覆盖掉；而且数据库重启后，所有的这些ASH信息都会消失。这样，对于长期检测oracle的性能是不可能的。在Oracle10g中，提供了永久保留ASH信息的方法，这就是AWR-Automatic Workload Repository：自动负载信息库。
由于全部保存ASH中的信息是非常耗费时间和空间的，AWR采用的策略是：每小时对v$active_session_history进行采样一次，并将信息保存到磁盘中，并且保留7天，7天后旧的记录才会被覆盖。这些采样信息被保存在视图wrh$_active_session_history中。而这个采样频率（1小时）和保留时间（7天）是可以根据实际情况进行调整的，这就给DBA们提供了更加有效的系统监测工具。
要想让AWR收集到准确的统计信息，从而生成可靠的性能分析报告，必须将初始化参数STATISTICS_LEVEL的值设置为TYPICAL或ALL。

SQL> show parameter STATISTICS_LEVELNAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
statistics_level             string     TYPICAL

AWR永久地保存系统的性能诊断信息，由SYS用户拥有。一段时间后，可能想清除掉这些信息；有时候为了性能诊断，可能需要自己定义采样频率来获取系统快照信息。Oracle 10g在包dbms_workload_repository中提供了很多过程，通过这些过程，可以管理快照并设定基线(baselines)。
注意
其实，AWR记录的信息不仅是ASH，还可以收集到数据库运行的各方面统计信息和等待信息，用以诊断分析。
AWR的采样方式是，以固定的时间间隔为其所有重要的统计信息和负载信息执行一次采样，并将采样信息保存在AWR中。
可以这样说：ASH中的信息被保存到了AWR中的视图wrh$_active_session_history中。ASH是AWR的真子集。

1.3 等待事件分析

这样，我们就知道了ASH和AWR产生的原因和功能。

• ASH保存了系统最新的处于等待的会话记录，可以用来诊断数据库的当前状态
• AWR中的信息最长可能有1小时的延迟，所以其采样信息并不能用于诊断数据库的当前状态，但可以用来作为一段时期内数据库性能调整的参考。

对于这些视图间的继承关系，eygle给出了一个关系图：

其中视图dba_hist_active_sess_history是wrh$_active_session_history和其他几个视图的联合展现，通常通过这个视图进行历史数据的访问。

1.4 MMON进程与MMNL进程

快照由一个称为MMON 的新的后台进程（及其从进程）以及MMNL后台进程自动地每隔固定时间采样一次。

1.4.1 MMON进程

MMON进程负责执行多种和管理相关（manageability-related）的后台任务：

• 当某个测量值（metrics）超过了预设的限定值（threshold value）后提交警告。
• 创建新的 MMON 隶属进程（MMON slave process）来进行快照（snapshot）。
• 捕获最近修改过的 SQL 对象的统计信息。

1.4.2 MMML进程

MMNL进程负责执行轻量级的且频率较高的和可管理性相关的后台任务，例如捕获会话历史信息，测量值计算等。

AWR的采样工作由MMON进程每个1小时执行一次，ASH信息同样会被采样写出到AWR负载库中。虽然ASH buffer被设计为保留1小时的信息，但很多时候这个内存是不够的，当ASH buffer写满后，后台进程MMNL将会主动将ASH信息写出。

1.5 SYSAUX表空间

这些采样数据都存储在SYSAUX表空间中，并且以WRM$_*和 WRH$_*的格式命名。前一种类型存储元数据信息（如检查的数据库和采集的快照），后一种类型保存实际采集的统计数据。

SQL> SELECT TABLE_NAME FROM DBA_TABLES WHERE TABLE_NAME LIKE 'WRM$%';TABLE_NAME
------------------------------
WRM$_BASELINE
WRM$_BASELINE_DETAILS
WRM$_BASELINE_TEMPLATE
WRM$_COLORED_SQL
WRM$_DATABASE_INSTANCE
WRM$_SNAPSHOT
WRM$_SNAPSHOT_DETAILS
WRM$_SNAP_ERROR
WRM$_WR_CONTROL
WRM$_WR_USAGE10 rows selected.

当SYSAUX表空间满后，AWR将自动覆盖掉旧的信息，并在警告日志中记录一条相关信息：

ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition   WRH$_ACTIVE_3533490838_1522 by 128 in tablespace SYSAUX

1.6 采样频率和保留时间

可以通过查询视图dba_hist_wr_control或wrm$_wr_control来查询AWR的采样频率和保留时间。默认为每1小时采样一次，采样信息保留时间为7天。

2 生成AWR和ASH报告

2.1 生成AWR报告

• sqlplus下执行：

@?/rdbms/admin/awrrpt.sql

或者

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

• 输入要生成报告的文件格式

Type Specified:  html

• 输入要生成报告相隔的天数

Enter value for num_days: 1

• 输入相隔的快照之间的Snap Id开始号和结束号

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 101
Enter value for end_snap: 102
End   Snapshot Id specified: 102

• 输入生成报告的名字：

Enter value for report_name: 0531awr.html

• 查看awr报告方式：
  生成报告后，退出sqlplus，文件存储在当前目录下，可以拷贝到电脑上查看；或者打开linux自带浏览器，输入目录
  file:///home/oracle/0531awr.html

2.2 AWR的主要参数

AWR报告要根据系统实际情况(OLAP or OLTP)来进行分析,例如对于一个OLTP系统，library hit和buffer hit应比较关注。而对于OLAP不甚重要。

AWR报告不需要全面阅读，全面阅读可能思路会更乱，如果性能问题由某个原因引起，那么会在报表的各个部分都会有相应呈现。

在RAC结构的数据库里做性能分析，通常需要对每个实例做一个AWR性能报告，原因是无法知道每个用户连接到哪个实例当中去。

对于一个系统，需要多做几次AWR报告，以便得到所有时间段的系统性能数据。在查看AWR报告时，如果了解数据库业务,应该有针对性的看一些可能存在性能问题的部分，并结合业务的实际情况来判断；也可以从TOP 10的等待事件触发，按照等待事件的类型，到相应的部分获取详细的信息来对系统性能问题作出判断。

通过AWR报告可以：
1）查看系统的负载和繁忙程度；
2）查看系统的瓶颈，发生的等待事件；
3）查看可优化的sql；

1.Summary:

• SESSIONS：实例连接的会话数，数据库大概的并发用户数；
• cursors/session：每个会话平均打开的游标数；

2.Report Summary:

• load profile：数据库资源负载的一个明细列表，用来判断系统的繁忙程度。分割为每秒钟的资源负载和每个事务的资源负载情况。
  1）DB time：用户操作花费的时间的合集，包括CPU时间和等待事件；
  

• Instance Efficiency Percentages：内存效率的统计信息，对于OLTP系统，应尽可能的接近100%。如果哪个数据偏低，就要做相关的分析研究。
  1）buffer nowait：非等待方式获取数据库；
  2）redo nowait：非等待方式获取redo数据；
  3）buffer hit：内存数据块命中率；
  4）in-memory sort：数据块在内存中排序的百分比；
  5）library hit：共享池中sql解析的命中率；
  6）execute to parse：执行次数对分析次数的百分比；
  7）Soft Parse %：软解析比例
  Execute to Parse %-sql 语句解析后被重复执行的次数， 计算公式=100*（1-Parses/Executions），很明显，如果SQL重用率比较高，那么该比例就很高。如果过低，可以考虑设置session_cached_cursors 参数
  Soft Parse %-软解析比例， 公式=softs/softs+hards，近似于sql在共享区的命中率，小于95%,需要考
  虑使用绑定变量，如果低于80%，那么就可能sql 基本没有被重用；
  8）latch Hit：latch命中率百分比；
  9）parse cpu to parse elapsed：解析总时间中消耗CPU的时间百分比；
  10）non-parse cpu：cpu非分析时间在整个cpu时间的百分比；
  

• cache sizes：列举awr在性能采集开始和结束的时候，数据缓冲池和共享池的大小，可以了解系统内存消耗的变化，可以判断SGA的内存分配是否合理；
  

• TOP 10 Foreground Events by Total Wait Time：查看最高10个耗费时间及等待事件，要联系报告的采集周期来看耗费时间是否合理。一般来说，CPU time出现在TOP10中的第一位，并且消耗时间占总时间的大部分比例。可以说明系统在正常运行。
  

以上这部分就是awr报告的总体概要，是需要重点关注的部分，根据这些信息可以知道等待时间比较长的事件，然后根据这些事件，去下面具体的部分查找问题原因。

3.Wait Events Statistics：

• Time Model Statistics列出了各种操作占用的数据库时间比例；
  
• Foreground Wait Class列出了等待事件类型，可以看到等待时间最长的事件；
  
• Foreground Wait Events是第一部分 Top 10 Foreground Events by Total Wait Time的详细部分；
  
• Background Wait Events实例的后台进程等待事件；
  
  4.SQL Statistics：
  
• SQL ordered by Elapsed Time：按照sql的执行时间从长到短的排序；
  1）elapsed time：sql执行时间；
  2）executions：sql执行的次数；
  3）elapsed per exec：每次执行消耗的执行时间；
  
• SQL ordered by CPU Time：按照sql的CPU时间从长到短排序：
  
• SQL ordered by User I/O Wait Time：
  
• SQL ordered by Gets：按照sql获取的内存数据块的数量排序：
  
• SQL ordered by Reads按照sql执行物理读排序：
  
• SQL ordered by Physical Reads (UnOptimized)：
  
• SQL ordered by Executions：按照sql的执行次数的排序；
  
• SQL ordered by Parse Calls：按照sql被分析次数（不区分软解析还是硬解析）的信息排序；
  
• SQL ordered by Version Count：sql产生多版本的信息；version count是sql的版本数；
  以上指标孤立起来看都没有实际意义，需要看系统的类型和性能问题是什么方面，有侧重的进行分析。例如SQL ordered by Executions和SQL ordered by Parse Calls对OLTP比较重要，而OLAP系统不需要太关注。

5.Instance Activity Statistics：

cpu used by this session：oracle消耗的cpu单位,可以看出cpu的负载情况；如果total为1000，per sec 为80，cpu个数为2；
那么就是整个统计周期消耗了1000个cpu单位，每秒钟消耗了80个cpu单位，对应实际的时间是80/100=0.8秒；每秒钟每个cpu消耗80/2=40个cpu单位；每秒钟中每个cpu处理使用的时间是40/100=0.4秒。

6.I\O Stats：

• Tablespace IO Stats：表空间的IO性能统计；
  
• File IO Stats：
  1）reads：发生了多少次物理读；
  2）writes：发生了多少次写；
  3）Av reads：每秒钟物理读的次数；
  4）Av Rd：平均一次物理读的时间；
  5）Blks/Rd：每次读多少个数据块；
  6）Av writes：每秒钟写的次数；
  7）buffer waits：获取内存数据块等待的次数；
  
  segments by logical reads和segment by physical reads从对象角度来展现了IO情况，分析这两部分信息，可以具体知道哪些对象的访问导致了IO性能下降。

2.3 生成AWR报告

ASH侧重于当前数据中活动会话的信息分析，被长期保存在数据字典中，可以通过查询视图V$ACTIVE_SESSION_HISTROY来得到。
也可以运行脚本。
使用同目录下的ashrpti.sql脚本可以生成其他数据库或者实例的ASH性能报告，也可以对一个session ID、SQL_ID、某个程序或者某一类等待事件

• sqlplus下执行：

@?/rdbms/admin/ashrpt.sql

或者

@$ORACLE_HOME/rdbms/admin/ashrpt.sql

• 输入要生成报告的文件格式,默认html
  Enter value for report_type:
• 输入开始时间，默认15min之前,手动输入数字是以分钟为单位

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
Enter value for begin_time:

• 输入间隔，默认至现在

Enter duration in minutes starting from begin time:
Defaults to SYSDATE - begin_time
Press Enter to analyze till current time
Enter value for duration: 

• 输入生成报告的名字：

Enter value for report_name: ash0601.html

• 查看ash报告方式：
  生成报告后，退出sqlplus，文件存储在当前目录下。

2.4 ASH报告主要参数

ASH报告分析如下：
DATA SOURCE如果来自DBA_HIST_ACTIVE_SESS_HISTORY,那么说明这些信息来自于AWR的快照；如果来自于V$ACTIVE_SESSION_HISTORY,那么
视图的信息就意味着性能数据存放到内存中的信息。

• top user events:用户会话的等待事件的信息；
  
• top event p1/p2/p3 values:等待事件的具体描述；
  
• top service/module:按照活动频率列出前五位的应用程序；
  
• top sql command types:列出了数据库中活动最频繁的操作；
  
• top sql with top events:按活动频繁程度列出sql语句；
• top session:列出活动最频繁的会话或进程；
  
• top sessions running PQs:列出活动频繁的前几位并行执行的会话信息；
• top DB files：IO最频繁的数据文件；
• activity over time:按照一个时间间隔对采集时间周期分组后，生成的每个时间间隔里的等待事件信息。