ORACLE中AWR快照的深入分析和方法

1、.ORACLE 中 AWR 快照的深入分析和方法我想 ，对于每一个从事数据库管理信息系统的IT 人士 ，都对 ORACLE 性能分析和性能研究产生一些兴趣 ，同时仪受到性能方面的一些困扰。根据我个人经验 ，认为每一个要成为ORACLE 性能分析的高手 ，都应该对 ORACLE 的 AWR 有一个比较深入的理解和使用 。学会从 AWR 快照分析中获得对自己有用的信息，是性能分析专家不可少的一个方面，如果再能结合应用服务的日志结合分析的话，是能够分析出一些关键的存在性能问题的SQL，从这些 SQL 中再进行反推是应用的哪些功能造成的，由此组织技术团队进行优化，循序渐进 ，不断改进 。一个大的项目的

2、性能优化是一个长期的过程。以下是 AWR 的一些关键知识点，不是本人原创 ，我感觉解释的很透彻。望阅读后能加深对AWR 的理解和认识 。一、 WHY 为什么会出现ASH 和 AWR ？1. 10g 之前用户的连接将产生会话，当前会话记录保存在v$session 中；处于等待状态的会话会被复制一份放在v$session_wait中。 当该连接断开后，其原来的连接信息在v$session 和v$session_wait中就会被删除。这是 10g 之前的状况 。2. v$session_wait_history 与 ASH若是一个普通的会话（我是指没有大量地耗费资源）， 则对于性能调整来说无足轻重。

3、但若该会话在活动时大量占用了资源（比如： CPU，内存 ， I/O 等），该会话信息的丢失，将.专业专注.无法评测当时的系统瓶颈究竟是什么。令 DBA 高兴的是 ， oracle10g中保留下了v$session_wait中的这些信息。在 10g 中新出现了一个视图： v$session_wait_history。这个视图保存了每个活动session在 v$session_wait 中最近 10 次的等待事件 。 但这对于一段时期内的数据库性能状况的监测是远远不够的 ，为了解决这个问题 ，在 10g 中还新添加了一个视图 ：v$active_session_history 。 这就是 ASH（

4、 active session history ）。典型的情况下 ,为了诊断当前数据库的状态，需要最近的五到十分钟的详细信息。然而，由于记录 session 的活动信息是很费时间和空间的，ASH 采用的策略是 ：保存处于等待状态的活动 session 的信息 ，每秒从 v$session_wait中采样一次 ，并将采样信息保存在内存中 。3.AWR注意 ， ASH 的采样数据是保存在内存中。 而分配给 ASH 的内存空间是有限的，当所分配空间占满后 ，旧的记录就会被覆盖掉 ；而且数据库重启后 ，所有的这些 ASH 信息都会消失 。这样 ，对于长期检测 oracle 的性能是不可能的。 在 Or

5、acle10g 中，提供了永久保留 ASH 信息的方法 ，这就是 AWR （auto workload repository）。由于全部保存 ASH 中的信息是非常耗费时间和空间的， AWR 采用的策略是 ：每小时对v$active_session_history进行采样一次，并将信息保存到磁盘中，并且保留 7 天， 7 天后旧的记录才会被覆盖。这些采样信息被保存在视图wrh$_active_session_history中。而这个采样频率 （ 1 小时）和保留时间 （ 7 天）是可以根据实际情况进行调整的，这就给 DBA 们提供了更加有效的系统监测工具。AWR 永久地保存系统的性能诊断信息，

6、由 SYS用户拥有 。一段时间后 ，你可能想清除掉这些信息 ；有时候为了性能诊断，你可能需要自己定义采样频率来获取系统快照信息。Oracle 10g在包 dbms_workload_repository中提供了很多过程，通过这些过程 ，你可以管.专业专注.理快照并设定基线(baselines) 。4.小结这样 ，我们就知道了ASH 和 AWR 产生的原因和功能。 ASH 保存了系统最新的处于等待的会话记录 ，可以用来诊断数据库的当前状态；而 AWR 中的信息最长可能有1 小时的延迟 ，所以其采样信息并不能用于诊断数据库的当前状态，但可以用来作为一段时期内数据库性能调整的参考 。对于这些视图间的

7、继承关系， eygle 给出了一个关系图：图 1 各个视图的层次其中视图 dba_hist_active_sess_history是 wrh$_active_session_history和其他几个视图的联合展现 ，通常通过这个视图进行历史数据的访问。二、 WHAT 什么是 AWR ？现在我们稍微详细地了解一下刚才所说内容。1.ash 占用的内存大小ASH 的采集信息保存在内存中，在旧的信息被采样到AWR 中后 ，可被新采集的信息覆盖，重启 oracle 后该信息被清除。分配给 ASH 的内存大小可以查询到：.专业专注.SQL> select pool, name, bytes/1024

8、/1024 From v$sgastat where name like '%ASH %'POOLNAMEBYTES/1024/1024- - -shared poolASH buffers22. AWR 更正为了便于描述和理解，在第一部分中，我们说 AWR 就是保存ASH 中的信息 。其实 ， AWR 记录的信息不仅是ASH ，还可以收集到数据库运行的各方面统计信息和等待信息，用以诊断分析。AWR 的采样方式是 ，以固定的时间间隔为其所有重要的统计信息和负载信息执行一次采样，并将采样信息保存在AWR 中。可以这样说 ： ASH 中的信息被保存到了AWR 中的视图wrh$_ac

9、tive_session_history中。ASH 是 AWR 的真子集 。3.mmon进程与 mmnl进程快照由一个称为MMON的新的后台进程（及其从进程 ）以及 MMNL后台进程自动地每隔固定时间采样一次。 我们先来看一下10g 的概念指南中对这两个新增加的后台进程的介绍：*MMON进程负责执行多种和管理相关（ manageability-related）的后台任务 ，例如：* 当某个测量值 （ metrics ）超过了预设的限定值（ threshold value）后提交警告* 创建新的MMON隶属进程 （MMON slave process）来进行快照 （ snapshot ）* 捕获

10、最近修改过的SQL 对象的统计信息.专业专注.*MMNL进程负责执行轻量级的且频率较高的和可管理性相关的后台任务，例如捕获会话历史信息，测量值计算等 。AWR 的采样工作由MMON进程每个 1 小时执行一次， ASH 信息同样会被采样写出到AWR负载库中 。虽然 ASH buffer被设计为保留1 小时的信息 ，但很多时候这个内存是不够的，当 ASH buffer写满后 ，另外一个后台进程MMNL将会主动将ASH 信息写出 。4.SYSAUX 表空间这些采样数据都存储在SYSAUX 表空间中 ，并且以 WRM$_*和 WRH$_* 的格式命名 。前一种类型存储元数据信息（如检查的数据库和采集的

11、快照），后一种类型保存实际采集的统计数据 。SQL> select table_name from dba_tables where table_name like 'WRM$%'TABLE_NAME-WRM$_WR_CONTROLWRM$_SNAP_ERRORWRM$_SNAPSHOTWRM$_DATABASE_INSTANCEWRM$_BASELINE当 SYSAUX 表空间满后 ， AWR 将自动覆盖掉旧的信息 ，并在警告日志中记录一条相关信息：.专业专注.ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSIO

12、N_HISTORY partitionWRH$_ACTIVE_3533490838_1522 by 128 intablespace SYSAUX5. 采样频率和保留时间可以通过查询视图dba_hist_wr_control或（wrm$_wr_control）来查询 AWR 的采样频率和保留时间 。默认为每1 小时采样一次，采样信息保留时间为7 天 。SQL> select * from dba_hist_wr_control;DBID SNAP_INTERVAL RETENTIONTOPNSQL- - - -1148 +00000 00:1+00007 00:0 DEFAULTSQL&

13、gt; select DBID, SNAP_INTERVAL, SNAPINT_NUM, RETENTION from wrm$_wr_control;DBID SNAP_INTERVALSNAPINT_NUM RETENTION- - - -1160732652 +00000 01:00:00.03600 +00007 00:00:00.06.采样数据量由于数据量巨大，把所有 ASH 数据写到磁盘上是不可接受的。一般是在写到磁盘的时候过滤这个数据 ，写出的数据占采样数据的10% ，写出时通过direct-path insert完成 ，尽量减少日志生成 ，从而最小化数据库性能的影响。7. 初始

14、化参数 statistics_levelAWR 的行为受到参数STATISTICS_LEVEL的影响 。 这个参数有三个值：.专业专注.*BASIC： awr 统计的计算和衍生值关闭.只收集少量的数据库统计信息.*TYPICAL：默认值 只有部分的统计收集.他们代表需要的典型监控oracle 数据库的行为 .* ALL : 所有可能的统计都被捕捉 . 并且有操作系统的一些信息 .这个级别的捕捉应该在很少的情况下 ,比如你要更多的 sql 诊断信息的时候才使用 .三、 HOW 如何使用AWR ？AWR 由 ORACLE 自动产生 ，但是也可以通过DBMS_WORKLOAD_REPOSITORY

15、包来手工创建 、删除和修改 。可以使用desc 命令查看该包中的过程。下面只介绍几个常用的：1. 手工创建一个快照SQL> select count(*) from wrh$_active_session_history;COUNT(*)-317SQL> begin2 dbms_workload_repository.create_snapshot();3 end;4 /PL/SQL过程已成功完成。.专业专注.SQL> select count(*) from wrh$_active_session_history;COUNT(*)-3202. 手工删除指定范围的快照SQL&

16、gt; select * from wrh$_active_session_history where snap_id = 96;SNAP_IDDBID INSTANCE_NUMBER SAMPLE_ID SAMPLE_TIME- - - - -96 11607326521236930 06-10月-07 11.26.04.562上午96 11607326521236930 06-10月-07 11.26.04.562上午96 11607326521236930 06-10月-07 11.26.04.562上午SQL> begin2 dbms_workload_repository.dr

17、op_snapshot_range(low_snap_id => 96, high_snap_id =>96, dbid => 1160732652);3 end;4 /PL/SQL过程已成功完成。.专业专注.SQL> select * from wrh$_active_session_history where snap_id = 96;未选定行3. 修改采集时间和统计信息保留时间PROCEDURE MODIFY_SNAPSHOT_SETTINGS参数名称类型输入 / 输出默认值 ?- - - -RETENTIONNUMBERINDEFAULTINTERVALNUMB

18、ERINDEFAULTTOPNSQLNUMBERINDEFAULTDBIDNUMBERINDEFAULT通过修改 retention参数可以修改awr 信息的保留期限。默认的是七天,最小的值是一天。如果把 retention设置为零 ,自动清除就关闭了.如果 awr 发现 sysaux 空间不够 ,它通过删除那些最老部分的快照来重新使用这些空间.同时 ,也会给 dba 发一条警告 ,告诉 sysaux 空间不够了（在警告日志中） .通过修改 interval参数可以修改awr 信息的采样频率。最小的值是10 分钟 ,默认的是60 分钟.典型的值是10,20,30,60,120等等 。 把 in

19、terval设为 0 则关闭自动捕捉快照.如将收集间隔时间改为30 分钟一次 。 并且保留5 天时间 （注：单位都是为分钟）：SQL> select *from dba_hist_wr_control;.专业专注.DBID SNAP_INTERVALRETENTIONTOPNSQL- - - -1160732652 +00000 01:00:00.0 +00007 00:00:00.0DEFAULTSQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>30,retention=>5*2

20、4*60);PL/SQL过程已成功完成。SQL> SELECT *from dba_hist_wr_control;DBID SNAP_INTERVALRETENTIONTOPNSQL- - - -1160732652 +00000 00:30:00.0+00005 00:00:00.0DEFAULTSQL>4.设置基线基线 （ baseline ）是一种机制 ,这样你可以在重要时间的快照信息集做标记。一个基线定义在一对快照之间,快照通过他们的快照序列号识别.每个基线有且只有一对快照。一次典型的性能调整实践从采集量度的基准线集合、作出改动 、然后采集另一个基准线集合开始 。 可以比

21、较这两个集合来检查所作的改动的效果。在 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, 9

22、8, 'apply_interest_1');PL/SQL过程已成功完成。这一操作将快照从95 到 98 编号 ，作为上面指定的基准线的一部分。查看现有的基准线：SQL> select *from dba_hist_baseline;DBID BASELINE_ID BASELINE_NAMESTART_SNAP_IDSTART_SNAP_TIMEEND_SNAP_ID END_SNAP_TIME.专业专注.- - - - - -11607326521 apply_interest_195 06-10月 上午98 06-10月下午SQL> select *from

23、wrm$_baseline;DBID BASELINE_ID BASELINE_NAMESTART_SNAP_ID END_SNAP_ID- - - - -11607326521 apply_interest_19598SQL>在一些调整步骤之后，我们可以创建另一个基准线 假设名称为apply_interest_2，然后只为那些与这两条基准线相关的快照比较量度。SQL> exec dbms_workload_repository.create_baseline(92, 94, 'apply_interest_2');PL/SQL过程已成功完成。像这样把快照分隔在仅仅

24、几个集合中有助于研究调整对于性能量度的影响。您可以在分析之后使用drop_baseline()来删除基准线 ；快照将保留 （也可级联删除 ）。 此外，当清除例程开始删除旧的快照时，与基准线相关的快照不会被清除，从而允许进行进一步的分.专业专注.析。5. 删除基线如果要删除一个基准线 :SQL> execdbms_workload_repository.drop_baseline(baseline_name=>'apply_interest_1',cascade=>false);PL/SQL过程已成功完成。SQL> select *from wrh$_ac

25、tive_session_history where snap_id in (95,96,97,98);SNAP_IDDBID INSTANCE_NUMBER SAMPLE_ID SAMPLE_TIME- - - - -95 11607326521235360 06-10月-07 10.56.29.872上午95 11607326521235230 06-10月-07 10.54.19.857上午95 11607326521233130 06-10月-07 10.19.19.478上午95 11607326521232830 06-10月-07 10.14.18.859上午95 1160732

26、6521232250 06-10月-07 10.04.38.481上午97 11607326521238600 06-10月-07 12.33.08.420下午97 11607326521238600 06-10月-07 12.33.08.420下午97 11607326521238600 06-10月-07 12.33.08.420下午97 11607326521238600 06-10月-07 12.33.08.420下午.专业专注.97 11607326521238600 06-10月-07 12.33.08.420下午97 11607326521238600 06-10月-07 12.

27、33.08.420下午SNAP_IDDBID INSTANCE_NUMBER SAMPLE_ID SAMPLE_TIME- - - - -97 11607326521238420 06-10月-07 11.50.55.686上午97 11607326521238230 06-10月-07 11.47.45.687上午98 11607326521239140 06-10月-07 01.42.00.976下午98 11607326521239140 06-10月-07 01.42.00.976下午98 11607326521239140 06-10月-07 01.42.00.976下午98 116

28、07326521239140 06-10月-07 01.42.00.976下午98 11607326521239140 06-10月-07 01.42.00.976下午98 11607326521239130 06-10月-07 01.27.04.161下午98 11607326521239130 06-10月-07 01.27.04.161下午98 11607326521239130 06-10月-07 01.27.04.161下午已选择 21 行。SQL> execdbms_workload_repository.drop_baseline(baseline_name=>

29、9;apply_interest_2',cascade=>true);.专业专注.PL/SQL过程已成功完成。SQL> select *from wrh$_active_session_history where snap_id in (92,93,94);未选定行SQL>6. 生成报表awr 有个报表生成机制,可以对存储在workload资料库的统计产生汇总报表。这个分析对一段时间的统计做的。这个报表生成机制很像statspack 。可以使用脚本 awrrpt.sql 或 awrrpti.sql来查看 AWR 报告 （非常类似statspack 中的spreport

30、.sql ）， 这两个脚本都在目录$ORACLE_HOME/rdbms/admin中 。 Awrrpt.sql脚本可以显示指定快照 id 范围的诊断信息 ，报告可以保存为文本文件或HTML 文件；awrrpti.sql 脚本与 awrrpt.sql 类似，唯一的不同就是在awrrpti.sql 脚本中 ，你可以指定数据库 ID 和实例 ID （作为参数 ）。 报告包括如下诊断信息：1Report summary1Wait events statistics1SQL statistics1 Instance activity statistics1 I/O statistics1 Buffer

31、pool statistics1 Advisory statistics.专业专注.1 Wait statistics1 Undo statistics1 Latch statisticsSegment statistics1 Dictionary cache statistics1 Library cache statistics1 SGA statistics1 Resource limit statistics1 init.ora parameters通过运行 $ORACLE_HOME/rdbms/admin目录中的 awrrpt.sql脚本 ， AWR 的功能可以立即通过它从采集的统计

32、数据和量度中生成的报表得到最好的说明。这个脚本显示所有的现有AWR 快照并请求两个特定的快照作为时间间隔边界。它产生两种类型的输出：文本格式（类似于 Statspack 报表的文本格式但来自于AWR 信息库 ）和默认的 HTML 格式 （拥有到部分和子部分的所有超链接），从而提供了非常用户友好的报表。运行这个脚本必须要select any dictionary权限 .这个脚本提示你输入选项如怎么和在哪里生成这个报表 :*首先 ,你需要指明你要生成html 还是 text 格式的*要选择快照的天数:输入天数 ,和你最近的快照,可选的 ,你可以使用dba_hist_snapshot表来看你要用哪个

33、snap_id.*开始 snap_id 和终止 snap_id, 这个快照对定义你的报表产生的时间间隔.*文件名称 ,报告写的用户指定的文件.现在运行该脚本以查看报表，从而对 AWR 的报表功能有一个直观的了解。.专业专注.Current InstanceDB IdDB NameInst Num Instance- - - -1160732652 ORCL1 orclSpecify the Report TypeWould you like an HTML report, or a plain text report?Enter 'html' for an HTML repor

34、t, or 'text' for plain textDefaults to 'html'输入report_type的值 :Type Specified:htmlInstances in this Workload Repository schema.专业专注.DB IdInst Num DB NameInstanceHost- - - - -* 11607326521 ORCLorclYUECHAOTIANUsing 1160732652 for database IdUsing1 for instance numberSpecify the number o

35、f days of snapshots to choose fromEntering 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的值 : 4Listing the last 4 days of Completed SnapshotsSnapInstanceDB NameSnap IdSnap StartedLevel.专业专注.- - - - -orclORCL81 04 10月 2007 07:24183 05 10月 2007 13:34184 05 10月 2007 16:19185 05 10月 2007 17:00186 05 10月 2007 18:00187 05 10月 2007 19:10188 05 10月 2007 20:00189 05 10月