Oracle AWR 介绍及报告分析 final

Oracle AWR 介绍及报告分析(1) final

1. AWR基本操作  

C:\>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 5月 25 08:20:25 2011

Copyright (c) 1982, , .  All rights reserved.

连接到:
Oracle Database  Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and  Mining Scoring Engine options

SQL> @D:\oracle\product\10.2.0\db_2\RDBMS\ADMIN\awrrpt.sql

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

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 3556425887 TEST01              1 test01

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'
输入 report_type 的值:

Type Specified:                  html

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

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 3556425887        1 TEST01       test01       PCE-TSG-036

Using 3556425887 for database Id
Using          1 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 without
specifying a number lists all completed snapshots.

输入 num_days 的值:  2

Listing the last 2 days of Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
test01       TEST01             214 24 5月  2011 07:53     1
                                215 24 5月  2011 09:00     1
                                216 24 5月  2011 10:01     1
                                217 24 5月  2011 11:00     1
                                218 24 5月  2011 12:00     1
                                219 24 5月  2011 13:01     1
                                220 24 5月  2011 14:00     1
                                221 24 5月  2011 15:00     1
                                222 24 5月  2011 16:00     1
                                223 24 5月  2011 17:00     1

                                224 25 5月  2011 07:51     1

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

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

declare
*
第 1 行出现错误:
ORA-20200: The instance was shutdown between snapshots 223 and 224
ORA-06512: 在 line 42

从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining Scoring Engine options 断开

再来一次:

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

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

随后输入要生成的report名称 ....

......

End of Report

Report written to awrrpt_1_0524_08_09.html
SQL>

生成的报告暂时不做分析 。我们先认识ASH及AWR. 

 

2.  认识 ASH (Active Session History)

2.1  ASH (Active Session History) 体系结构

在Oracle10g之前，当前会话记录保存在v$session中；处于等待状态的会话会被复制一份放在
v$session_wait中。当该连接断开后，其原来的连接信息在v$session和v$session_wait中就
会被删除。没有视图能提供有关session在历史上的每个时间点上都在做什么，以及在等待
什么资源。原来的v$session及v$session_wait只是显示当前session正在执行什么及等待
什么资源。

从Oracle10g开始，Oracle提供了Active Session History (ASH)来解决这个问题。 每隔1秒钟
ASH都会将当前活动的session的信息记录在SGA的一个缓冲区(循环使用)中。在ASH中，这个过
程称为采样(Sampling)。ASH缺省每一秒收集一下v$session中活动会话的情况，记录会话等待
的事件，不活动的会话不会被采样, 间隔时间由 _ash_sampling_interval 参数确定 .

在10g中新出现了一个视图：v$session_wait_history。这个视图保存了每个活动session在
v$session_wait中最近10次的等待事件.  但这对于一段时期内的数据性能状况的监测是远远不够
的，为了解决这个问题，在10g中还新添加了一个视图：v$active_session_history。这就是ASH
（active session history）。

2.2  ASH采用的策略 ---

典型的情况下,为了诊断当前的状态，需要最近的五到十分钟的详细信息。然而，由于记录
session的活动信息是很费时间和空间的，ASH采用的策略是:  保存处于等待状态的活动session的
信息，每秒从v$session_wait及v$session中采样一次，并将采样信息保存在内存中(注意：ASH的
采样数据是保存在内存中)。

 

2.3  ASH的工作原理 ---

对于Active Session的采样(每秒收集相关视图中的信息)数据存放在SGA中，SGA中分配给ASH的大小
可以从v$sgastat中查询(Shared Pool下ASH buffers)，该空间可以循环使用，如果需要，以前的信
息可以被新的信息覆盖。要把所有session的所有活动记录下来是非常消耗资源的。因此ASH只能从
V$SESSION 等少数相关视图中获取那些活动的session的信息。ASH每隔1秒收集session信息时，不是
通过SQL语句完成的，而是采用直接访问内存的方式，相对更高效。

因为每秒需要采样数据，所以ASH缓存里数据量非常大，将他们全部刷新到磁盘上的话，会非常消
耗磁盘空间，因此在将ASH缓存中的数据刷新到AWR相关表中的时候， 采取以下策略： 

1.  MMON 默认每隔60分钟 (可以调整) 将ash buffers 中的数据的1/10  flush到磁盘 。
2.  MMNL 默认当ash buffers 满 66% 的时候将ash buffers 中的 1/10 的数据写入磁盘 (具体1/10是哪些数据，遵循FIFO原则) 。
3.  MMNL 写入的采用数据百分比 10%  表示的是写入磁盘的数据占 ash buffers 中采样数据量的百分比 (而不是占ash buffers 总大小的比例)    
4.  为了节省空间，AWR中采集的数据默认在7天后自动清除。

具体参考隐含参数：

_ash_sampling_interval：每秒钟采样一次
_ash_size：定义的ASH Buffer最小值，默认为1M
_ash_enable：启用ASH采样
_ash_disk_write_enable：将采样数据写入磁盘
_ash_disk_filter_ratio：写入磁盘的采样数据占ASH buffer里总采样数据的百分比，默认10%
_ash_eflush_trigger：ASH buffer满了多少以后会写出，默认66%
_ash_sample_all：如果设置为TRUE，则所有会话都会被采样，包括那些处于空闲等待的会话。默认是FALSE。

ASH缓存是SGA中一个固定大小的区域，每个CPU对应2M空间。ASH缓存最大不能超过shared pool的
5% 或者是 sga_target 的2% 。

ASH buffers中的数据查询：      v$active_session_history
ASH buffers中的数据刷新到表：  wrh$_active_session_history
                              （是一个分区表，WRH=Workload Repository History）
上表相关视图 ：      dba_hist_active_sess_history

 

2.4  访问ASH --- 

通过v$active_session_history视图可以获取相关数据，也可以通过此视图获得
一些性能方面的信息 。

-----------
采样信息
-----------
SAMPLE_ID      取样ID  
SAMPLE_TIME    取样时间 
IS_AWR_SAMPLE  是否是AWR采样数据，基本是1/10数据

----------------------
唯一标识session的信息
----------------------
SESSION_ID     对应 V$SESSION下的SID 
SESSION_SERIAL#   唯一标识一个session的objects
SESSION_TYPE   表示是后台还是前台程序FOREGROUND/BACKGROUND
USER_ID      Oracle user identifier; maps to V$SESSION.USER#
SERVICE_HASH   Hash that identifies the Service; maps to V$ACTIVE_SERVICES.NAME_HASH
PROGRAM        操作程序 
MODULE         操作程序对应的及版本
ACTION        
CLIENT_ID      Client identifier of the session

----------------------
session执行的SQL语句信息
----------------------
SQL_ID              在取样时正在执行的SQL的ID  
SQL_CHILD_NUMBER    在取样时正在执行的SQL的子游标Number 
SQL_PLAN_HASH_VALUE  SQL计划hash值
SQL_OPCODE     指出SQL语句处于哪个阶段的操作 对应V$SESSION.COMMAND
QC_SESSION_ID  
QC_INSTANCE_ID

----------------------
session等待状态
----------------------
SESSION_STATE   session状态WAITING/ON CPU
WAIT_TIME 

----------------------
session等待事件信息 
----------------------
EVENT
EVENT_ID
EVENT#
SEQ#
P1
P2
P3
TIME_WAITED

----------------------
session等待的对象信息 
----------------------
CURRENT_OBJ#
CURRENT_FILE#
CURRENT_BLOCK#

 

 

3  AWR (Automatic Workload Repository)

ASH的采样数据是保存在内存中。而分配给ASH的内存空间是有限的，当所分配空间
占满后，旧的记录就会被覆盖掉；而且数据库重启后，所有的这些ASH信息都会消失。
这样，对于长期检测oracle的性能是不可能的。在Oracle10g中，提供了永久保留ASH
信息的方法，这就是AWR（automatic workload repository）。Oracle建议用AWR取代
Statspack (10gR2中还是保留了statspack).

3.1  ASH到AWR

ASH到AWR的过程可以用下面图标简单描述：

v$session --> v$session_wait --> v$session_wait_history(其实没有这一步)
--> v$active_session_history (ASH) --> wrh$_active_session_history (AWR)
--> dba_hist_active_sess_history

v$session代表数据库活动的开始，是为源起；
v$session_wait视图用以实时记录活动session的等待情况，是当前信息；
v$session_wait_history是对v$session_wait的简单增强，记录活动session的最近10次等待；
v$active_session_history是ASH的核心，用以记录活动session的历史等待信息，每秒采样1次，
这部分内容记录在内存中，期望值是记录1个小时的内容；
wrh$_active_session_history是v$active_session_history在AWR的存储池，
v$active_session_history中记录的信息会被定期（每小时1次）地刷新到负载库中，并缺省
保留一个星期用于分析；
dba_hist_active_sess_history视图是wrh$_active_session_history视图和其他几个视图的
联合展现，我们通常通过这个视图进行历史数据的访问。

 

上面有谈到ASH通过MMON,MMNL后台进程默认每隔1个小时从ASH Buffers中采样一次数据，那么
采集来的这些数据存储在哪里呢 ？

AWR用多个表来存储采集的性能统计数据，表都存储在SYSAUX表空间中SYS用户下，并且以WRM$_*
和 WRH$_*，WRI$_*, WRR$_* 的格式命名。AWR的历史数据主要存储在基础表wrh$_active_session_history
(分区表).

WRM$_*  类型存储AWR的元数据信息（如检查的数据库和采集的快照），M代表metadata 
WRH$_*  类型保存采样快照的历史统计数据。H代表“历史数据”
WRI$_*  类型表示存储数据库建议功能(advisor)相关的数据
WRR$_*  代表的是新功能Workload Capture以及Workload Replay相关信息

在这些表上构建了几种带前缀DBA_HIST_ 的视图，这些视图可以用来编写您自己的性能
诊断工具。视图的名称直接与表相关；例如，视图 DBA_HIST_SYSMETRIC_SUMMARY 是在
WRH$_SYSMETRIC_SUMMARY 表上构建的。

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

3.2  设置 AWR 

要使用AWR必须设置STATISTICS_LEVEL参数，共有三个值：BASIC,TYPICAL,ALL。

A. typical -- 默认值，启用所有的自动化功能，并会为此收集数据库里所需要
的相关信息。收集的信息包括： Buffer Cache Advice, MTTR Advice,Timed
Statistics, Segment Level Statistics , PGA Advice .....等等，可以通
过 select statistics_name , activation_level from v$statistics_level
order by 2; 来查询收集的信息 。 Oracle建议使用默认值typical 。

B. all -- 如果设置为all, 那么除了typical之外，还会收集额外的信息，包括
plan execution statistics 和Timed OS statistics(参考A中的SQL查询语句).  
在该设置下，可能为了收集诊断信息而消耗过多资源。

C. basic -- 关闭所有自动化功能。

 

3.3  AWR相关：数据，收集及管理   

3.3.1  数据

其实AWR记录的信息不仅是ASH，还可以收集到数据库运行的各方面统计信息和等待
信息，用以诊断分析。

AWR的采样方式是，以固定的时间间隔为其所有重要的统计信息和负载信息执行一次采样，
并将采样信息保存在AWR中。可以这样说：ASH中的信息被保存到了AWR中的视图
wrh$_active_session_history中。ASH是AWR的真子集。

这些采样数据都存储在SYSAUX表空间中, 当SYSAUX表空间满后，AWR将自动覆盖掉旧的
信息，并在警告日志中记录一条相关信息：

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

3.3.2  收集及管理  

AWR永久地保存系统的性能诊断信息，由SYS用户拥有。一段时间后，你可能想清除掉
这些信息；有时候为了性能诊断，你可能需要自己定义采样频率来获取系统快照信息。
Oracle 10g在包dbms_workload_repository中提供了很多过程，通过这些过程，你可
以快照并设定基线等。

通过修改retention参数可以修改awr信息的保留期限。默认的是七天,最小的值是一天。
如果把retention设置为零,自动清除就关闭了.如果awr发现sysaux空间不够,它通过删除
那些最老部分的快照来重新使用这些空间.同时, 也会给dba发一条警告,告诉sysaux空间
不够了（在alert log中）.通过修改interval参数可以修改awr信息的采样频率。最小的
值是10分钟,默认的是60分钟.典型的值是10,20,30,60,120等等。把interval设为0则关闭
自动捕捉快照.如将收集间隔时间改为30 分钟一次。并且保留5天时间(注：单位都是为分钟)

MMON收集快照的频率(每小时)和采集的数据保留时间(7天)都可以由用户修改。
查看： select * from dba_hist_wr_control;

例如： 修改为收集快照频率20分钟，保留数据2天：
begin
 dbms_workload_repository.modify_snapshot_settings(interval=> 20,
     retention => 2 * 24 * 60);
end;

 

3.4  手工创建及删除AWR快照 

AWR可以由ORACLE自动产生，也可以通过DBMS_WORKLOAD_REPOSITORY包来手工创建、删除
和修改。可以使用desc命令查看该包中的过程。下面只介绍几个常用的：

SQL> select count(*) from wrh$_active_session_history;
 COUNT(*)
----------
 317 

SQL> begin
  2  dbms_workload_repository.create_snapshot();
  3  end; 
  4  /

PL/SQL 过程已成功完成。

SQL> select count(*) from wrh$_active_session_history;
 COUNT(*)
----------
  320

手工删除指定范围的快照

SQL> select * from wrh$_active_session_history ；

SQL> begin
2  dbms_workload_repository.drop_snapshot_range(low_snap_id => 96,     
     high_snap_id => 96, dbid => 1160732652);
3  end;
4  /

SQL> select * from wrh$_active_session_history where snap_id = 96;
未选定行

 

3.5 设置及删除基线(baseline)

基线（baseline）是一种机制,这样你可以在重要时间的快照信息集做标记。一个基线定义
在一对快照之间,快照通过他们的快照序列号识别.每个基线有且只有一对快照。一次典型
的性能调整实践从采集量度的基准线集合、作出改动、然后采集另一个基准线集合开始。
可以比较这两个集合来检查所作的改动的效果。在 AWR 中，对现有的已采集的快照可以执
行相同类型的比较。

假定一个名称为 apply_interest 的高度资源密集的进程在下午 1:00 到 3:00 之间运行，
对应快照 ID 95 到 98。我们可以为这些快照定义一个名称为apply_interest_1 的基准线:

SQL> select *From dba_hist_baseline;
SQL> select * from wrm$_baseline;
SQL> exec dbms_workload_repository.create_baseline(95, 98, 'apply_interest_1');

在一些调整步骤之后，我们可以创建另一个基准线 — 假设名称为 apply_interest_2，然后
只为那些与这两条基准线相关的快照比较量度

SQL> exec dbms_workload_repository.create_baseline(92, 94, 'apply_interest_2');

可以在分析之后使用 drop_baseline() 来删除基准线；快照将保留(也可级联删除)。 此外，
当清除例程开始删除旧的快照时，与基准线相关的快照不会被清除，从而允许进行进一步的分析.

删除基线：

SQL> exec dbms_workload_repository.drop_baseline(baseline_name=>'apply_interest_1', cascade=>false);

 

4. 环境中的AWR

在rac环境,每个快照包含集群的所有的节点(因为存储在共享的数据库中，不是在每个实例).每个
节点的快照数据有相同的snap_id,  但靠实例id来区分。一般地,rac中的快照是在同一时间捕捉的。
你也可以使用database control来进行手工的快照。手工的快照支持系统产生的自动快照。

5. ADDM 

自动数据库诊断监控： ADDM的引入有了这个AWR这个“数据仓库”之后，Oracle自然可以在此
基础之上实现更高级别的智能应用，更大程度地发挥AWR的信用，这就是Oracle 10g引入的另外
一个功能自动数据库诊断监控程序（Automatic Database Diagnostic Monitor，ADDM），通过
ADDM，Oracle试图使数据库的维护、管理和优化工作变得更加自动和简单。

ADDM可以定期检查数据库的状态，根据内建的专家系统，自动确定潜在的数据库性能瓶颈，并提
供调整措施和建议。由于这一切都是内建在Oracle数据库系统之内的，其执行效率很高，几乎不
影响数据库的总体性能。  新版的Database Control可以以一种方便直观的形式提供ADDM的结果
和建议，并引导管理员逐步实施ADDM的建议，快速解决性能问题。

6. AWR常见操作 

AWR配置都是通过dbms_workload_repository包进行配置。

6.1 调整AWR产生snapshot的频率和保留策略，如将收集间隔时间改为30分钟一次,
并且保留5天时间（单位都是分鍾）：
SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>30, retention=>5*24*60);

6.2 关闭AWR，把interval设为0则关闭自动捕捉快照
SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>0);
　
6.3 手工创建一个快照
SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

6.4 查看快照
SQL> select * from sys.wrh$_active_session_history

6.5 手工删除指定范围的快照
SQL> exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(low_snap_id => 973, high_snap_id => 999, dbid => 262089084);

6.6 创建baseline，保存这些数据用于将来分析和比较
SQL> exec dbms_workload_repository.create_baseline(start_snap_id => 1003, end_snap_id => 1013, 'apply_interest_1');

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

6.8 将AWR数据导出并迁移到其它数据库以便于以后分析
SQL> exec DBMS_SWRF_INTERNAL.AWR_EXTRACT(dmpfile => 'awr_data.dmp', mpdir => 'DIR_BDUMP', bid => 1003, eid => 1013);

6.9 迁移AWR数据文件到其他数据库
SQL> exec DBMS_SWRF_INTERNAL.AWR_LOAD(SCHNAME => 'AWR_TEST', dmpfile => 'awr_data.dmp', dmpdir=>'DIR_BDUMP');
把AWR数据转移到TEST模式中：
  SQL> exec DBMS_SWRF_INTERNAL.MOVE_TO_AWR (SCHNAME => 'TEST');

7. 分析AWR报告

因公司禁止上传文件， 故AWR报告未能上传 。根据描述可以知道对应的字段。

db time= cpu time + wait time（不包含空闲等待） （非后台进程）， 就是db time就是记录的花在运算(非后台进程)和等待(非空闲等待)上的时间 

 

系统为24核CPU , 在snapshot间隔中，总共约1380.04分钟，CPU時間共有1380.4*24=33129.6分钟，这里的DB time为2591.15分钟，表示cpu花费了2591.15分钟在处理非空闲等待和运算上(比方逻辑读)
也就是说cpu有 2591.15/33129.6*100% (的百分比: 7.82%)花费在处理Oracle的操作上，这不包括后台进程, 这台服务器的平均负载相对较低。从awr report的Elapsed 和DB Time就能大概了解db的负载.

就是說：  通過 DB Time / (Elapsed * CPU核數) *100% 得出的值就說明了CPU花费在处理Oracle的操作上的比例 (不包括后台进程)。  比例越高，說明負載越高。

 

Load Profile说明了目前库整体状态。

 

Redo size:  平均每秒或每事务重做产生的日志大小为161K  (单位：Bytes) ，每事务产生5K重做日志。

Physical  writes:  平均每秒物理写为 66.52 blocks . 

Physical reads / Logical reads= 430.48 / 38788.19 = 1.1% 的逻辑读导致了物理I/O。平均每个事物产生逻辑读 1351.11 (blocks) 。这个数字应该越小越好。 Read的單位是block .

 Parses:     CPU每秒需要进行 1454.21 次解析，系统比较繁忙，每秒有35.79次硬解析(硬解析占比例2.5% ) ，说明 1/35.79 = 0.02 秒CPU就要处理一个新的语句，说明系统内不同SQL语句较多，建议多使用绑定变量及procedure处理。

 

Sorts:  每秒70.30次排序也是比较多的。

Transactions：每秒产生的事务数，反映数据库任务繁重与否

% Blocks changed per Read： 说明 1-2.43%=97.57% 的逻辑读是用于那些只读的而不是可修改的块， 平均每次操作只更新占2.43% 的块 。也就是 ( 收集快照的这23个小时中 ) DML更新操作的块占整个被操作(逻辑读)的块的比例是2.43% 。

 

Recursive Call% :    表示71.77%的SQL都是通过PL/SQL来执行的。Recursive：递归的

 

Rollback per transaction %：   表示事务回滚的百分比，越小越好。 19.95值已经非常高了(表示每个事物产生0.1995个回滚)，系统存在回滚方面的问题，因为回滚的代价非常昂贵，即平均每5（1/0.1995）个事务就要产生一次回滚 。 结合前面的transactions 每秒为 28.71个，即每秒会有28.71/5 = 5.7 次回滚。 应该仔细检查系统为何产生如此高的回滚率。

 

数据库实例效率。目标值都是100% 。

 

Buffer Nowait %： 在缓冲区中获取Buffer的未等待比率(buffer cache请求的命中率)，如果Buffer Nowait<99% 说明，有可能是有热块 (查找x$bh的 tch和v$latch_children的cache buffers chains)。

Redo NoWait %： 在Redo缓冲区获取Buffer的未等待比率。

Buffer Hit %： 数据块在数据缓冲区中的命中率，通常应在95%以上，否则，小于95%，需要调整重要的参数，小于90%可能是要加db_cache_size，但是大量的非选择的索引也会造成该值很高（大量的db file sequential read）。

 In-memory Sort %：在内存中的排序率。如果太低，需要考慮增加PGA或检查程序减少排序。

Library Hit %：主要代表SQL在共享区(库缓存)的命中率，通常在95%以上，否则需要考虑加大Shared Pool ，绑定变量，修改cursor_sharing (需要谨慎修改此参数) 等参数。

Soft Parse %：  软解析百分比，近似看作SQL在共享区的命中率，小于<95%,需要考虑到绑定，如果低于80%，那么就可能SQL基本没有被重用。

Execute to Parse % ：   SQL语句执行与解析的比率。如果某条新的SQL语句经过一次解析然后执行，且再也不在同一个session中执行的话，那么比率为0，这个比率应该越高越好。比如这里的36.04%说明， 同一个session中执行的SQL语句中只有36.04%的SQL是已经解析好了的(不需要再次解析)。 说明DB中新的SQL语句相对较多。

Execute to parse＝round(100 * (1-Parses/Executions),2),   如果parse次数大于executions，可能会导致此值为负数，对性能会有影响 。 这个值越接近100%越好 (即Parses/Executions 越接近0，也即几乎所有SQL都是已经解析过的，只要执行就好了)。

Latch Hit %：   每次申请一个latch的时候，成功的概率有多少。如果低于99%, 则说明有latch竞争问题。 要确保>99%，否则存在严重的性能问题， 比如绑定变量使用，热点块分散，共享池调整(过小) 等。

Parse CPU to Parse Elapsd %：

计算公式为：Parse CPU to Parse Elapsd %= 100*(parse time cpu / parse time elapsed)。即：解析实际运行时间/(解析实际运行时间+解析中等待资源时间)。此处为89.28%，用于解析花费的每个CPU秒花费了大约1/0.8928=1.12秒的wall clock（挂钟）时间， 这说明花了0.12秒时间等待一个资源。如果该比率为100%，意味着CPU时间等于经过的时间，没有任何等待。值越大，说明消耗在等待资源上的时间越少。

% Non-Parse CPU：     计算公式为：% Non-Parse CPU =round(100*1-PARSE_CPU/TOT_CPU),2)。太低表示解析消耗时间过多。这个比值越接近100% 越好，说明数据库大部分时间是花在执行SQL语句上，而不是解析SQL语句。 

Memory Usage%   :   表示被使用的部分占shared pool总大小的百分比，如果太低，浪费内存，如果值太高，说明利用率过大，可能因为shared pool中对象被经常刷新出内存，导致SQL语句硬解析增加。这个数字应该长时间稳定在75%～90% 。

 %SQL with executions>1:  表示shared pool中执行次数大于1次的SQL语句占SQL语句总数比例是94.48%。

 %Memory for SQL w/exec>1:    这是与不频繁使用的SQL语句相比，频繁使用的SQL语句消耗的内存占shared pool的百分比。这个数字将在总体上与% SQL with executions>1非常接近，除非有某些查询任务消耗的内存没有规律。 在稳定状态下，总体上会看见随着时间的推移大约有75%～85%的共享池被使用。如果报表的时间窗口足够大到覆盖所有的周期，执行次数大于一次的SQL语句的百分率应该接近于100%。这是一个受观察之间持续时间影响的统计数字。可以期望它随观察之间的时间长度增大而增大。

Top 5 Timed Events中如果是空闲等待时间，可以无需关注，我们只需要关心非空闲等待事件。  常见的空闲事件有: 

dispatcher timer

　　 lock element cleanup

　　 Null event

　　 parallel query dequeue wait

　　 parallel query idle wait - Slaves

　　 pipe get

　　 PL/SQL lock timer

　　 pmon timer- pmon

　　 rdbms ipc message

　　 slave wait

　　 smon timer

　　 SQL*Net break/reset to client

　　 SQL*Net message from client

　　 SQL*Net message to client

　　 SQL*Net more  to client

　　 virtual circuit status

　　 client message

 

Top 5 Timed Events中列出的等待事件不一定是当时列出的5项，每次收集都会变化，

这里列出经常出现的一些事件做简单分析。  注意在Oracle 9.2 以前这个项目叫做Top 5 Wait Events， 在9.2及之后版本才更改为 Top 5 Timed Events， 且包含”CPU time” 其中的Waits 表示等待次数，Time(s)表示等待时间(秒)，一般主要看等待时间。 Avg Wait (ms) 表示平均每次等待的时间， % Total Call Time 表示该等待事件在总的调用时间里的百分比是多少， Wait Class表示等待的级别。

 

CPU time    :     CPU time其实不是真正的等待事件。是衡量CPU是否瓶颈的一个重要指标, 

Elapsed Time = CPU Time + Wait Time 。  一般来讲，一个良好的系统，CPU TIME 应该排在TOP 5 TIME Event的最前面，否则，就要进行调整以减少其他的WAIT TIME。 当然这也是相对的， 如果不存在显著的 latch wait 或过高的logical read  等,  CPU time 占的比例高才是令人放心的。 也就是说CPU在高效率干活是好事，但是是否因为低效的设置或SQL而消耗CPU时间就需要注意了。  

 

db file sequential read 与 db file scattered read.   

这两个事件是出现比较频繁的事件。 他们表明Oracle内核请求从磁盘读取数据块 (到buffer cache中), 他们的区别就是

sequential 是单块读（串行读），而scattered 表示多块读。(和是否全表扫描无关, 只是全表扫描一般表现为多块读) 。 这两个事件描述的是如何将数据块存储到内存中的, 而不是如何从磁盘进行读取。

  

db file scattered read 

一次获取的block被分散在buffer的不连续空间中，通常表示全表扫描过多，可检查应用程序是否合理的使用了索引，数据库是否合理的创建了索引 。 db file scattered read是用来表示顺序读取(例如,全表扫描)。

db file sequential read 
通常暗示着通过索引获取数据量比较大（比如通过索引进行范围扫描获取表数据百分比过大或者错误的使用索引），多表连接的时候连接顺序不当，hash join时hash_area_size无法容纳hash table 等。db file sequential read是用来表示随机读取(例如,索引扫描)。

 

深入解析db file sequential read 及 db file scattered read ：

 定义

事件名db file sequential read与db file scattered read描述的是如何将数据块存储到内存中的,而不是如何从磁盘进行读取. 如果填充磁盘读取的内容的内存是连续的, 发生的磁盘读就是db file sequential read ,  当填充从磁盘读取的数据的内存的连续性无法被保证的时候,发生的磁盘读就是db file scattered read. 

 

db file sequential read 

Oracle 为所有的单块读取生成db file sequential read事件（既然是单个，当然是连续的，你可以发现db file sequential read 等待事件的P3参数一般都是1）. Oracle始终将单个数据块存储在单个缓存块(cache buffer)中,  因此单块读取永远不会产生db file scattered read事件.  对于索引块，如果不是快速全索引扫描，一般都是一个一个块读取的，所以说，这个等待事件很多时候都是索引读取引起的。 

这一事件通常显示与单个数据块相关的读取操作(如索引读取)。如果这个等待事件比较显著，可能表示在多表连接中，表的连接顺序存在问题，可能没有正确的使用驱动表; 或者可能说明不加选择地进行索引。 在大多数情况下我们说，通过索引可以更为快速的获取记录，所以对于一个编码规范、调整良好的数据库，这个等待很大是很正常的。但是在很多情况下，使用索引并不是最佳的选择，比如读取较大表中大量的数据，全表扫描可能会明显快于索引扫描，所以在开发中我们就应该注意，对于这样的查询应该进行避免使用索引扫描。

db file scattered read  

db file scattered read 一般都是等待同时读取多个块到内存中。为了性能和更有效的内存空间利用，oracle一般会把这些块分散在内存中。db file scattered read 等待事件的P3参数指出了每次I/O读取的块数。每次I/O读取多少个块， 由参数db_file_multiblock_read_count控制。 全表扫描或者快速全索引扫描时一般使用的这种读取块的方式，所以，该等待很多时候都是因为全表扫描引起的 ；在大部分情况下, 全表扫描与快速全索引扫描都会产生一次或多次db file scattered read. 不过, 有时 , 这些扫描只会产生db file sequential read.  

因为全表扫描被置于LRU（Least Recently Used，最近最少使用）列表的冷端（cold end），对于频繁访问的较小的数据表，可以选择把他们Cache到内存中，以避免反复读取。当这个等待事件比较显著时，可以结合v$session_longops 以及动态性能视图来进行诊断，该视图中记录了长时间（运行时间超过6秒的）运行的事物，可能很多是全表扫描操作（不管怎样，这部分信息都是值得我们注意的）。 

 

latch free 

latch是一种轻量级的锁。一般来说，latch由三种内存元素组成：pid（进程id），内存地址和内存长度。Latch保证对共享数据结构的排它性访问，以此来保证内存结构的完整性不受到损坏。在多个会话同时修改或者检视(inspect)sga中同一个内存结构时，必须串行化访问以保证sga中数据结构的完整性。 

Latch只是用来保护sga中的内存结构。对数据库中的对象的保护，使用的lock而不是latch。Oracle sga中有许多latch，用来保护sga中各种内存结构不会因为并发访问而损坏。常见的Latch Free等待事件是由于热块 (buffer cache中的latch争用) 及未使用绑定变量(shared pool中的latch争用) 导致的。

 最常见的Latch集中于Buffer Cache的竞争和Shared Pool的竞争。和Buffer Cache相关的主要Latch竞争有cache buffers chains和cache buffers lru chain，和Shared Pool相关的主要Latch竞争有Shared Pool Latch和Library Cache Latch等。  Buffer Cache的Latch竞争经常是由于热点块竞争或低效的SQL语句引起； Shared Pool的Latch竞争通常是由于SQL的硬解析引起。过大的共享池可能导致shared pool latch 争用(9i之前的版本)；

当latch在系统范围内的等待时间比较显著时，你可以通过v$latch中的sleeps列来发现争用显著的latch：

Select  name,  gets,  misses, immediate_gets,  immediate_misses,  sleeps
from   v$latch  order  by sleeps  desc  ;    

 

buffer busy waits 

发生条件：

block正被读入缓冲区或者已经在缓冲区正被其他session修改, 一个会话尝试去pin 住它，这时当前block已经被pin住，就发生了竞争，产生一个buffer busy waits, 该值不应该大于1% 。可以查看v$waitstat 看到大概的buffer busy waits 分布 。

解决办法：
出现此情况通常可能通过几种方式调整： 增大data  buffer, 增加freelist，减小pctused, 增加回滚段数目，增大initrans,  考虑使用LMT+ASSM,  确认是不是由于热点块造成(如果是可以用反转索引,或者用更小块大小)  . 

该等待事件表示正在等待一个以非共享方式使用的缓冲区，或者表示当前正在被读入buffer cache。一般来说Buffer Busy Wait不应大于1%。     检查缓冲等待统计部分（如下）Segments By Buffer Busy Waits (或V$WAITSTAT)，看一下等待是否位于段头(Segment Header)。       如果是，可以考虑增加自由列表(freelist，对于Oracle8i DMT)或者增加freelist groups(在很多时候这个调整是立竿见影的， 在8.1.6及以后版本，动态修改feelists需要设置COMPATIBLE至少为8.1.6)， Oracle9i或以后可以使用ASSM .

alter table xxx storage(freelists  n); 

 

--查找等待块类型

SELECT   'segment Header' CLASS,         a.Segment_Type,

         a.Segment_Name,

         a.Partition_Name

  FROM   Dba_Segments a, V$session_Wait b

 WHERE       a.Header_File = b.P1

         AND a.Header_Block = b.P2

         AND b.Event = 'buffer busy waits'

UNION

SELECT   'freelist Groups' CLASS,

         a.Segment_Type,

         a.Segment_Name,

         a.Partition_Name

  FROM   Dba_Segments a, V$session_Wait b

 WHERE   b.P2 BETWEEN a.Header_Block + 1

                  AND  (a.Header_Block + a.Freelist_Groups)

         AND a.Header_File = b.P1

         AND a.Freelist_Groups > 1

         AND b.Event = 'buffer busy waits'

UNION

SELECT   a.Segment_Type || ' Block' CLASS,

         a.Segment_Type,

         a.Segment_Name,

         a.Partition_Name

  FROM   Dba_Extents a, V$session_Wait b

 WHERE       b.P2 BETWEEN a.Block_Id AND a.Block_Id + a.Blocks - 1

         AND a.File_Id = b.P1

         AND b.Event = 'buffer busy waits'

         AND NOT EXISTS (SELECT   1

                           FROM   Dba_Segments

                          WHERE   Header_File = b.P1 AND Header_Block = b.P2);

 

对于不同的等待块类型，我们采取不同的处理办法：

 1.data segment header：
进程经常性的访问data segment header通常有两个原因：获取或修改process freelists信息;  扩展高水位标记。    针对第一种情况，进程频繁访问process freelists信息导致freelist争用，我们可以增大相应的segment对象的存储参数freelist或者freelist groups；若由于数据块频繁进出freelist而导致进程经常要修改freelist，则可以将pctfree值和pctused值设置较大的差距，从而避免数据块频繁进出freelist；  对于第二种情况，由于该segment空间消耗很快，而设置的next extent过小，导致频繁扩展高水位标记，解决的办法是增大segment对象的存储参数next extent或者直接在创建表空间的时候设置extent size uniform. 。 

2.data block：
某一或某些数据块被多个进程同时读写，成为热点块，可以通过如下办法来解决这个问题：
(1) 降低程序的并发度，如果程序中使用了parallel查询，降低parallel degree，以免多个parallel slave同时访问同样的数据对象而形成等待降低性能
(2) 调整应用程序使之能读取较少的数据块就能获取所需的数据，减少buffer gets和physical reads
(3) 减少同一个block中的记录数，使记录分布于更多的数据块中，这可以通过若干途径实现：可以调整segment对象的pctfree值，可以将segment重建到block size较小的表空间中，还可以用alter table minimize records_per_block语句减少每块中的记录数
(4) 若热点块对象是类似自增id字段的索引，则可以将索引转换为反转索引，打散数据分布，分散热点块 ； 如果等待处于索引块，应该考虑重建索引、分割索引或使用反向键索引。

 

对于多事务并发访问的数据表，关于ITL的竞争和等待可能出现，为了减少这个等待，可以增加initrans，使用多个ITL槽.  

3.undo segment header：
undo segment header争用是因为系统中undo segment不够，需要增加足够的undo segment，根据undo segment的方法，若是手工管理模式，需要修改rollback_segments初始化参数来增加rollback segment， 若是自动管理模式，可以减小transactions_per_rollback_segment初始化参数的值来使oracle自动增多rollback segment的数量

4.undo block：
undo block争用是由于应用程序中存在对数据的读和写同时进行（需要适当减少大规模的一致性读取），读进程需要到undo segment中去获得一致性数据，解决办法是错开应用程序修改数据和大量查询数据的时间 ASSM 结合LMT 彻底改变了Oracle 的存储机制，位图freelist 能够减轻缓冲区忙等待(buffer busy wait)，这个问题在Oracle9i 以前的版本里曾是一个严重的问题。

　　Oracle 宣称ASSM 显著地提高了DML 并发操作的性能，因为(同一个)位图的不同部分可以被同时使用，这样就消除了寻找剩余空间的串行化。根据Oracle 的测试结果，使用位图会消除所有分段头部(对资源)的争夺，还能获得非常快的并发插入操作。在Oracle9i或以后版本之中，Buffer Busy wait 不再常见。

 

Free buffer waits 

表示data buffer里没有空闲可用buffer，使得当前会话进程处于Free buffer wiats等待状态， Free buffer waits的等待原因一

般有以下几个：
-  DATA BUFFER太小；
-  DBWR进程写的效率比较低；
-  LGWR写的太慢，导致DBWR等待；
-  正在把大量脏块写入到磁盘；
-  SQL语句效率低，需对Top SQL进行优化。

 enqueue 

队列竞争： enqueue是一种保护共享资源的锁定机制。该锁定机制保护共享资源，如记录中的数据，以避免两个人在同一时间更新同一数据。Enqueue 包括一个排队机制，即FIFO（先进先出）排队机制。  Enqueue等待常见的有ST、HW 、TX 、TM等 

 ST enqueue，用于空间管理和字典管理的表空间(DMT)的区间分配，在DMT中典型的是对于uet$和fet$数据字典表的 争用。对于支持LMT的版本，应该尽量使用本地管理表空间. 或者考虑手工预分配一定数量的区(Extent)，减少动态扩展时发生的严重队列竞争。

HW enqueue指和段的高水位标记相关等待；手动分配适当区可以避免这一等待。

TX锁（事务锁）是最常见的enqueue等待。TX enqueue等待通常是以下三个问题之一产生的结果。
第一个问题是唯一索引中的重复索引，你需要执行提交（commit）/回滚（rollback）操作来释放enqueue。
第二个问题是对同一位图索引段的多次更新。因为单个位图段可能包含多个行地址（rowid），所以当多个用户试图更新同一段时，可能一个用户会锁定其他用户请求的记录，这时等待出现。直到获得锁定的用户提交或回滚， enqueue释放。 第三个问题，也是最可能发生的问题， 多个用户同时更新同一个块。如果没有足够的ITL槽，就会发生块级锁定。通过增大initrans和/或maxtrans以允许使用多个ITL槽（对于频繁并发进行DML操作的数据表，在建表之初就应该考虑为相应参数设置合理的数值，避免系统运行以后在线的更改，在8i之前，freelists等参数不能在线更改，设计时的考虑就尤为重要），或者增大表上的pctfree值，就可以很容易的避免这种情况。

 

TM enqueue队列锁在进行DML操作前获得，以阻止对正在操作的数据表进行任何DDL操作(在DML操作一个数据表时，其结构不能被更改)。

log file parallel write  /  log file sync (日志文件同步)

如果你的Log group 存在多个组成员，当flush log buffer 时，写操作是并行的，这时候此等待事件可能出现。 

触发LGWR进程的条件有：
  1. 用户提交
  2. 有1/3重做日志缓冲区满 
  3. 有大于1M的重做日志缓冲区未被写入磁盘
  4. 3秒超时
  5. DBWR 需要写入的数据的SCN大于LGWR记录的SCN，DBWR 触发LGWR写入。

 

当一个用户提交(commits)或者回滚(rollback),session的redo信息需要写出到redo logfile中.用户进程将通知LGWR执行写出操作,LGWR完成任务以后会通知用户进程.  这个等待事件就是指用户进程等待LGWR的写完成通知.    对于回滚操作，该事件记录从用户发出rollback命令到回滚完成的时间.

如果该等待过多，可能说明LGWR的写出效率低下，或者系统提交过于频繁.  针对该问题，可以关注:  log file parallel write等待事件 。  user commits,user rollback等统计信息可以用于观察提交或回滚次数 

 

解决方案： 
1.提高LGWR性能：尽量使用快速磁盘，不要把redo log file存放在raid 5的磁盘上
2.使用批量提交
3.适当使用NOLOGGING/UNRECOVERABLE等选项

可以通过如下公式计算平均redo写大小：

avg.redo write size = (Redo block written/redo writes)*512 bytes 

如果系统产生redo很多，而每次写的较少，一般说明LGWR被过于频繁的激活了.
可能导致过多的redo相关latch的竞争。  

 

下面的等待事件与有关(节点间资源争用)：

gc current block busy  ：

gcs log flush sync  ：

gc buffer busy ：热点块问题；节点隔离/业务隔离以减少节点间的资源争用；

 

 

Log File Switch

当这个等待出现时，表示所有的提交(commit)的请求都需要等待"日志文件切换"的完成。这个等待事件出现时通常是因为日志组循环写满以后，第一个日志归档尚未完成，出现该等待。出现该等待，可能表示io 存在问题。

解决办法:

　　可以考虑增大日志文件和增加日志组

　　移动归档文件到快速磁盘

　　调整log_archive_max_processes .

　　log file switch (checkpoint incomplete)-日志切换(检查点未完成)

　　该等待事件通常表示你的DBWR 写出速度太慢或者IO 存在问题。

         需要考虑增加额外的DBWR 或者增加你的日志组或日志文件大小。

 

control file sequential read / control file parallel write

如果等待时间比较长，非常明显， 需要考虑改善控制文件所在磁盘I/O .

 

 

SQL Statistics 按照不同指标排序统计的数据非常有用，结合所有的统计信息，可以

找出运行性能差的SQL语句，以及运行不合理(比如运行次数非常多)的SQL .  比较容易看懂，这里不详细介绍。 

 

 

上面的很多项都比较好理解，这里简单说一下下面的几个：

  SQL ordered by Parse Calls : 什么是parse calls 请参考（包括硬解析，软解析，及softer解析）：  

http://space.itpub.net/35489/viewspace-697499

  

SQL ordered by Version Count :   包含版本较多的sql语句，即parent cursors相同，而children cursors不同的sql语句。  即，SQL文本一模一样，父亲游标可以共享，但是因为优化器环境设置不同( OPTIMIZER_MISMATCH)，  绑定变量的值的长度在第二次执行的时候发生显著的变化(BIND_MISMATCH) , 授权关系不匹配(AUTH_CHECK_MISMATCH ) 或者 基础物件转换不匹配(TRANSLATION_MISMATCH) 等导致子游标不能共享，需要新生成一个子游标 。 这与SQL共享 (即游标共享) 是有关系的 。 这种情况下的执行计划可能不同，也可能相同(我们可以通过plan_hash_value看出);   具体的mismatch可以查询 V$SQL_SHARED_CURSOR

 Advisory Statistics

 除了通过此项查看相关建议之外。 还可以通过如下的视图查询。

GV_$DB_CACHE_ADVICE
GV_$MTTR_TARGET_ADVICE
GV_$PGATARGET_ADVICE_HISTOGRAM
GV_$PGA_TARGET_ADVICE
GV_$SHARED_POOL_ADVICE
V_$DB_CACHE_ADVICE
V_$MTTR_TARGET_ADVICE
V_$PGA_TARGET_ADVICE
V_$PGA_TARGET_ADVICE_HISTOGRAM
V_$SHARED_POOL_ADVICE

 

Buffer Pool Advisory  /  PGA Memory Advisory  / SGA Target Advisory / 。。。。。。

 

 Wait Statistics

说明buffer wait 是什么样的等待块类型（参考前面的buffer wait 说明及改善方法）。 

Segment Statistics ：

    * Segments by Logical Reads

    * Segments by Physical Reads

    * Segments by Row Lock Waits

    * Segments by ITL Waits

    * Segments by Buffer Busy Waits

    * Segments by Global Cache Buffer Busy

    * Segments by CR Blocks Received

    * Segments by Current Blocks Received

 

........