Oracle AWR（Automatic Workload Repository）解析与分析

以你之姓@ 2023-10-04 22:29 25阅读 0赞

一. AWR 说明

从Oracle Database 10g 提供了一个新的工具：(AWR:Automatic Workload Repository)。Oracle 建议用户用这个取代 Statspack。AWR 实质上是一个 Oracle 的内置工具，它采集与性能相关的统计数据，并从那些统计数据中导出性能量度，以跟踪潜在的问题。

与 Statspack 不同，快照由一个称为 MMON 的新的后台进程及其从进程自动地每小时采集一次。为了节省空间，采集的数据在 7 天后自动清除。快照频率和保留时间都可以由用户修改。它产生两种类型的输出：文本格式（类似于 Statspack 报表的文本格式但来自于 AWR 信息库）和默认的 HTML 格式（拥有到部分和子部分的所有超链接），从而提供了非常用户友好的报表。

AWR 使用几个表来存储采集的统计数据，所有的表都存储在新的名称为 SYSAUX 的特定表空间中的 SYS 模式下，并且以 WRM$\_\* 和 WRH$\_\* 的格式命名。前一种类型存储元数据信息（如检查的数据库和采集的快照），后一种类型保存实际采集的统计数据。H 代表“历史数据 (historical)”而 M 代表“元数据 (metadata)”。

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

Oracle AWR（Automatic Workload Repository）报告，作为对数据库性能的分析和诊断的重要工具，下面概括了 分析AWR报告的五个要点：

1. 数据库活动：AWR报告提供了有关数据库活动的信息，包括等待事件、会话、事务、SQL语句和I/O操作等。通过分析这些信息，可以了解数据库的瓶颈和性能问题，并对其进行优化。 2. 系统资源：AWR报告提供了有关系统资源的信息，包括CPU使用率、内存使用率、磁盘I/O和网络使用率等。通过分析这些信息，可以了解系统资源的瓶颈和性能问题，并对其进行优化。

3. 性能指标：AWR报告提供了一些性能指标，如平均响应时间、平均等待时间、平均I/O时间等。通过分析这些指标，可以了解数据库的性能和瓶颈，并对其进行优化。

4. 比较分析：AWR报告还提供了不同时间段的比较分析，可以比较不同时间段的性能差异，以及数据库性能的趋势和变化。

5. 建议和优化：AWR报告还提供了一些建议和优化建议，可以指导维护人员对数据库进行优化和改进。这些建议可能包括优化SQL语句、增加系统资源、调整参数设置等。 综上所述，分析AWR报告需要综合考虑数据库活动、系统资源、性能指标、比较分析和建议优化等方面的信息，以便更好地了解数据库的性能和瓶颈，并对其进行优化。

注意一点：

statistics\_level 默认是typical，在10g中表监控是激活的，强烈建议在10g中此参数的值是typical。如果STATISTICS\_LEVEL设置为basic，不仅不能监控表，而且将禁掉如下一些10g的新功能：  
ASH(Active Session History)  
ASSM(Automatic Shared Memory Management)  
AWR(Automatic Workload Repository)  
ADDM(Automatic Database Diagnostic Monitor)

在Oracle 11gR2里禁用的功能如下：

STATISTICS\_LEVEL specifies the level of collection for database and operating system statistics. The Oracle Database collects these statistics for a variety of purposes, including making self-management decisions.

The default setting of TYPICAL ensures collection of all major statistics required for database self-management functionality and provides best overall performance. The default value should be adequate for most environments.

When the STATISTICS\_LEVEL parameter is set to ALL, additional statistics are added to the set of statistics collected with the TYPICAL setting. The additional statistics are timed OS statistics and plan execution statistics.

Setting the STATISTICS\_LEVEL parameter to BASIC disables the collection of many of the important statistics required by Oracle Database features and functionality, including:

*  Automatic Workload Repository (AWR) Snapshots
 *  Automatic Database Diagnostic Monitor (ADDM)
 *  All server-generated alerts
 *  Automatic SGA Memory Management
 *  Automatic optimizer statistics collection
 *  Object level statistics
 *  End to End Application Tracing (V$CLIENT\_STATS)
 *  Database time distribution statistics (V$SESS\_TIME\_MODEL and V$SYS\_TIME\_MODEL)
 *  Service level statistics
 *  Buffer cache advisory
 *  MTTR advisory
 *  Shared pool sizing advisory
 *  Segment level statistics
 *  PGA Target advisory
 *  Timed statistics
 *  Monitoring of statistics

二. AWR使用

SQL>@?/rdbms/admin/awrrpt.sql

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

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 的值: 1

Listing the last day's Completed Snapshots

Snap

Instance DB Name Snap Id Snap Started Level

\------------ ------------ --------- ------------------ -----

orcl10g ORCL10G 142 03 7月 2009 08:11 1

143 03 7月 2009 09:00 1

144 03 7月 2009 10:00 1

145 03 7月 2009 11:00 1

146 03 7月 2009 12:01 1

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

输入 begin\_snap 的值: 142

Begin Snapshot Id specified: 142

输入 end\_snap 的值: 146

End Snapshot Id specified: 146

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is awrrpt\_1\_142\_146.html. To use this name,

press <return> to continue, otherwise enter an alternative.

输入 report\_name 的值: D:/awrrpt\_1\_142\_146.html

Report written to D:/awrrpt\_1\_142\_146.html

三. AWR 操作

3.1. 查看当前的AWR保存策略

SQL> col SNAP\_INTERVAL format a20

SQL> col RETENTION format a20

SQL> select \* from dba\_hist\_wr\_control;

DBID SNAP\_INTERVAL RETENTION TOPNSQL

\---------- -------------------- -------------------- ----------

262089084 +00000 01:00:00.0 +00007 00:00:00.0 DEFAULT

以上结果表示,每小时产生一个SNAPSHOT，保留7天。

3.2. 调整AWR配置

AWR配置都是通过dbms\_workload\_repository包进行配置。

3.2.1 调整AWR产生snapshot的频率和保留策略，如将收集间隔时间改为30 分钟一次。并且保留5天时间（单位都是分钟）：

SQL> exec dbms\_workload\_repository.modify\_snapshot\_settings(interval=>30, retention=>5\*24\*60);

3.2.2 关闭AWR，把interval设为0则关闭自动捕捉快照

SQL> exec dbms\_workload\_repository.modify\_snapshot\_settings(interval=>0);

3.2.3 手工创建一个快照

SQL> exec DBMS\_WORKLOAD\_REPOSITORY.CREATE\_SNAPSHOT();

3.2.4 查看快照

SQL> select \* from sys.wrh$\_active\_session\_history

3.2.5 手工删除指定范围的快照

SQL> exec DBMS\_WORKLOAD\_REPOSITORY.DROP\_SNAPSHOT\_RANGE(low\_snap\_id => 973, high\_snap\_id => 999, dbid => 262089084);

3.2.6 创建baseline，保存这些数据用于将来分析和比较

SQL> exec dbms\_workload\_repository.create\_baseline(start\_snap\_id => 1003, end\_snap\_id => 1013, 'apply\_interest\_1');

3.2.7 删除baseline

SQL> exec DBMS\_WORKLOAD\_REPOSITORY.DROP\_BASELINE(baseline\_name => 'apply\_interest\_1', cascade => FALSE);

3.2.8 将AWR数据导出并迁移到其它数据库以便于以后分析

SQL> exec DBMS\_SWRF\_INTERNAL.AWR\_EXTRACT(dmpfile => 'awr\_data.dmp', mpdir => 'DIR\_BDUMP', bid => 1003, eid => 1013);

3.2.9 迁移AWR数据文件到其他数据库

SQL> exec DBMS\_SWRF\_INTERNAL.AWR\_LOAD(SCHNAME => 'AWR\_TEST', dmpfile => 'awr\_data.dmp', dmpdir => 'DIR\_BDUMP');

把AWR数据转移到SYS模式中：

SQL> exec DBMS\_SWRF\_INTERNAL.MOVE\_TO\_AWR (SCHNAME => 'TEST');

四. AWR 报告分析

4.1 SQL ordered by Elapsed Time

记录了执行总和时间的TOP SQL(请注意是监控范围内该SQL的执行时间总和，而不是单次SQL执行时间 Elapsed Time = CPU Time + Wait Time)。

Elapsed Time(S): SQL语句执行用总时长，此排序就是按照这个字段进行的。注意该时间不是单个SQL跑的时间，而是监控范围内SQL执行次数的总和时间。单位时间为秒。Elapsed Time = CPU Time + Wait Time

CPU Time(s): 为SQL语句执行时CPU占用时间总时长，此时间会小于等于Elapsed Time时间。单位时间为秒。

Executions: SQL语句在监控范围内的执行次数总计。

Elap per Exec(s): 执行一次SQL的平均时间。单位时间为秒。

% Total DB Time: 为SQL的Elapsed Time时间占数据库总时间的百分比。

SQL ID: SQL语句的ID编号，点击之后就能导航到下边的SQL详细列表中，点击IE的返回可以回到当前SQL ID的地方。

SQL Module: 显示该SQL是用什么方式连接到数据库执行的，如果是用SQL\*Plus或者PL/SQL链接上来的那基本上都是有人在调试程序。一般用前台应用链接过来执行的sql该位置为空。

SQL Text: 简单的sql提示，详细的需要点击SQL ID。

4.2 SQL ordered by CPU Time:

记录了执行占CPU时间总和时间最长的TOP SQL(请注意是监控范围内该SQL的执行占CPU时间总和，而不是单次SQL执行时间)。

4.3 SQL ordered by Gets:

记录了执行占总buffer gets(逻辑IO)的TOP SQL(请注意是监控范围内该SQL的执行占Gets总和，而不是单次SQL执行所占的Gets)。

4.4 SQL ordered by Reads:

记录了执行占总磁盘物理读(物理IO)的TOP SQL(请注意是监控范围内该SQL的执行占磁盘物理读总和，而不是单次SQL执行所占的磁盘物理读)。

4.5 SQL ordered by Executions:

记录了按照SQL的执行次数排序的TOP SQL。该排序可以看出监控范围内的SQL执行次数。

4.6 SQL ordered by Parse Calls:

记录了SQL的软解析次数的TOP SQL。说到软解析(soft prase)和硬解析(hard prase)，就不能不说一下Oracle对sql的处理过程。

4.7 SQL ordered by Sharable Memory:

记录了SQL占用library cache的大小的TOP SQL。Sharable Mem (b)：占用library cache的大小，单位是byte。

4.8 SQL ordered by Version Count:

记录了SQL的打开子游标的TOP SQL。

4.9 SQL ordered by Cluster Wait Time:

记录了集群的等待时间的TOP SQL

Oracle AWR（Automatic Workload Repository）解析与分析

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Oracle错误解析：案例分析与应对建议

相关 Oracle SQL错误解析：案例分析与解决方法

相关 Oracle性能瓶颈解析：问题案例分析

相关 Oracle日志分析遇到困惑：实例解析与应对措施

相关 Oracle错误解析：常见日志异常分析

相关 Using Automatic Workload Repository for Database Tuning: Tips for Expert DBAs

相关 Oracle AWR（Automatic Workload Repository）解析与分析

相关 @Repository与@Mapper

相关 oracle用dbms_workload_repository取AWR报告方法

相关 Repository与UnitOfWork引入