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

http://space.itpub.net/?uid-35489-action-viewspace-itemid-697546

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, 2005, Oracle.  All rights reserved.

连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data 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 <return> 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名称 ....

......

<P>
<P>
End of Report
</BODY></HTML>
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正在执行什么SQL及等待
什么资源。

从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$_*  代表的是11g新功能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 table 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. RAC环境中的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报告

见下一篇