Oracle自带性能分析工具-awr介绍和分析

杭州华三通信技术有限公司

目  录

1 Oracle 10g的AWR性能优化工具简介

2 AWR配置

2.1 AWR统计数据的缺省配置

2.2 修改AWR统计数据的设置

2.3 AWR的使用

2.3.1 AWR产生快照点(时间点)的方法：手动或自动方式

2.3.2 AWR产生报表的方法：使用SQL命令，根据快照产生相应实例的报表

2.4 关注报表具体内容

2.4.1 Load Profile

2.4.2 Instance Efficiency Percentages (Target 100%)

2.4.3 IO Stats

3 对AWR报表内容进行分析的ADDM工具

3.1 ADDM概述

3.2 ADDM的使用方法

3.2.1 在所有Oracle服务器上确认EM的状态，并启动EM进程

3.2.2 通过IE浏览器，登录EM界面

3.3 ADDM查询和诊断到的问题有那些

3.3.1 查看集群中Oracle服务器的硬件配置

3.3.2 CPU的瓶颈

3.3.3 内存结构上的瓶颈？

3.3.4 内存参数SGA、PGA查看和分析

3.3.5 I/O 是否存在性能瓶颈? (I/O子系统是否工作正常) ?

3.3.6 高负载的SQL语句 (是否存在消耗过量系统资源的SQL) ?

3.4 ADDM 分析的结果

4 总结

5 附录

1  Oracle 10g的AWR性能优化工具简介

AWR (Automatic Workload Repository)既自动工作负载信息库是Oracle 10g新提供的收集数据库统计信息的内置工具。它比之前的statspack有显著的改进，收集的信息也更多、更全面，使用方法也更简单。

它主要采集与性能相关的统计数据，并从那些统计数据中导出性能量度，以跟踪潜在的问题，如包括AWR内存区，历史数据存储文件和ASH等部件。

AWR报告的内容繁多，官方文档也没有对所有参数给出说明。AWR产生的报表包括以下几部分。报表具体内容参见如下插入的对象。

1、  Report Summery

Cache sizes

Load profile

Instance Efficiency Percentages (Target 100%)

Shared Pool Statistics

Top 5 Timed Events

2、  RAC Statistics

Global Cache Load Profile

Global Cache Efficiency Percentages (Target local+remote 100%)

Global Cache and Enqueue Services - Workload Characteristics

Global Cache and Enqueue Services - Messaging Statistics

3、  Wait Events Statistics

Time Model Statistics

Wait Class

Wait Events

Background Wait Events

Operating System Statistics

Service Statistics

Service Wait Class Stats

4、  SQL Statistics

SQL ordered by Elapsed Time

SQL ordered by CPU Time

SQL ordered by Gets

SQL ordered by Reads

SQL ordered by Executions

SQL ordered by Parse Calls

SQL ordered by Sharable Memory

SQL ordered by Version Count

SQL ordered by Cluster Wait Time

Complete List of SQL Text

5、  Instance Activity Statistics

Instance Activity Stats

Instance Activity Stats - Absolute Values

Instance Activity Stats - Thread Activity

6、 IO Stats

Tablespace IO Stats

File IO Stats

7、  Buffer Pool Statistics

8、  Advisory Statistics

Instance Recovery Stats

Buffer Pool Advisory

PGA Aggr Summary

PGA Aggr Target Stats

PGA Aggr Target Histogram

PGA Memory Advisory

Shared Pool Advisory

SGA Target Advisory

Streams Pool Advisory

Java Pool Advisory

9、  Wait Statistics

Buffer Wait Statistics

Enqueue Activity

10、 Undo Statistics

Undo Segment Summary

Undo Segment Stats

11、 Latch Statistics

Latch Activity

Latch Sleep Breakdown

Latch Miss Sources

Parent Latch Statistics

Child Latch Statistics

12、 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

13、 Dictionary Cache Statistics

Dictionary Cache Stats

Dictionary Cache Stats (RAC)

14、 Library Cache Statistics

Library Cache Activity

Library Cache Activity (RAC)

15、 Memory Statistics

Process Memory Summary

SGA Memory Summary

SGA breakdown difference

16、 Streams Statistics

Streams CPU/IO Usage

Streams Capture

Streams Apply

Buffered Queues

Buffered Subscribers

Rule Set

17、 Resource Limit Stats

18、 init.ora Parameters

19、 Global Enqueue Statistics

20、 Global CR Served Stats

21、 Global CURRENT Served Stats

22、 Global Cache Transfer Stats

2  AWR配置

2.1  AWR统计数据的缺省配置

AWR 实质上是一个 Oracle 的内置工具，它采集与性能相关的统计数据，并从那些统计数据中导出性能量度，以跟踪潜在的问题。AWR利用多个快照(既时间点)来采集数据。快照由一个称为 MMON 的新后台进程及其该进程自动地每小时采集一次。为了节省空间，采集的数据在 7 天后自动清除。快照频率（SNAP_INTERVAL）和保留时间（RETENTION）都可以由用户修改。查看当前的缺省设置，可以使用下面的语句：

SQL> select snap_interval, retention from dba_hist_wr_control;

SNAP_INTERVAL    RETENTION

+00000 01:00:00.0     +00007 00:00:00.0

这些 SQL 语句显示快照每小时采集一次，采集的数据保留 7 天。

 注意：

1、snap_interval 取值范围为[ 10分钟 52560000分钟 ]

2.2  修改AWR统计数据的设置  

例如，根据使用需要，修改快照时间间隔为 20 分钟，保留时间为2天 ，通过如下命令修改，参数以分钟为单位。

修改：

SQL> begin

       dbms_workload_repository.modify_snapshot_settings (

          interval => 20,

          retention => 2*24*60

       );

    end;

    /

PL/SQL 过程已成功完成。

修改后，查询结果：

SQL> select snap_interval, retention from dba_hist_wr_control;

SNAP_INTERVAL   RETENTION

+00000 00:20:00.0     +00002 00:00:00.0

2.3  AWR的使用

2.3.1  AWR产生快照点(时间点)的方法：手动或自动方式

1. AWR手动产生快照

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL 过程已成功完成。

2. AWR自动产生快照

根据所设定的快照频率（SNAP_INTERVAL）自动产生快照

2.3.2  AWR产生报表的方法：使用SQL命令，根据快照产生相应实例的报表

使用SQL> @?/rdbms/admin/awrrpt 命令产生报表。该命令实际调用的是位于名称：$ORACLE_HOME/rdbms/admin/awrrpt.sql下的脚本

awr提供一个脚本来生成报表。报表的形式有两种：text和html。建议生成html格式的报表，因为可读性非常。

用脚本生成awr报表需要以sys用户登录并执行命令。执行过程需要输入报表类型、天数（用来决定显示那几天内的快照(snapshot)）、begin_snap、end_snap、以及报表名称等5个参数。

1. AWR产生报表举例

(1)          推荐采用通过客户端连接实例产生报表

有两种方法执行如下命令：通过客户端连接实例或登录到Oracle服务器再连接实例。

通过不同的方式登录，产生报表的位置是不同的。

1、通过Windows客户端 DOS窗口连接实例，产生报表的位置位于默认登录路径，如下：

通过Windows DOS窗口登录并执行@?/rdbms/admin/awrrpt命令，所产生的报表位于C:\目录下

2、通过Oracle服务器连接实例，产生报表位于/home/oracle目录下

# cd /home/oracle

# ls -l awrrpt_2_1_3.html

-rw-r--r--   1 oracle   oinstall     324609 Oct 16 14:23 awrrpt_2_12_13.html

(2)          查看产生快照时间点间隔和采集数据保持的天数

SQL> select snap_interval, retention from dba_hist_wr_control;

SNAP_INTERVAL   RETENTION

+00000 01:00:00.0     +00007 00:00:00.0

 注意：

默认情况下，Oracle数据库每隔1个小时会自动生成1个快照点，并保持7天内的快照点的数据，以便用户分析。

(3)          执行SQL命令，产生指定快照时间点间隔的数据报表

SQL> @?/rdbms/admin/awrrpt

Current Instance

 DB Id           DB Name      Inst Num    Instance

-----------        ------------      --------        ------------

 12884956 ORA              2                 ora2      //生成实例ora2的统计报表

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 的值:  html                  //报表类型

Type Specified:  html

Instances in this Workload Repository schema

   DB Id               Inst Num       DB Name      Instance     Host

------------             --------           ------------     ------------ ------------

* 12884956        2                 ORA               ora2         aix53

  12884956         1                 ORA               ora1         aix54

Using 12884956 for database Id

Using          2 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 的值:  3          //天数(用来决定显示那几天内的快照点(snapshot))

Listing the last 3 days of Completed Snapshots              

Instance  DB Name   Snap Id      Snap Started       Snap Level

ora2            ORA           1       16 10月 2008 12:00    1

                      2       16 10月 2008 13:00     1

                      3       16 10月 2008 14:00     1

                      4       16 10月 2008 15:00     1

                      5       16 10月 2008 16:00     1

                      6       16 10月 2008 17:00     1

                      7       16 10月 2008 17:15     1 //手动生成的快照点

                      8       16 10月 2008 18:00     1

                      9       16 10月 2008 19:00     1

                     10       16 10月 2008 20:00    1

                     11       16 10月 2008 21:00    1

                     12       16 10月 2008 22:00    1

                     13       16 10月 2008 23:00    1

                     14       17 10月 2008 00:00    1

                     15       17 10月 2008 01:00    1                                       

注：以上是根据AWR的默认设置，每小时产生一个快照点数据

Specify the Begin and End Snapshot Ids

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

输入 begin_snap 的值:  12

Begin Snapshot Id specified: 12

输入 end_snap 的值:  15

End   Snapshot Id specified: 15

Specify the Report Name

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

The default report file name is awrrpt_2_12_13.html.  To use this name, press to continue, otherwise enter an alternative.

输入 report_name 的值:           //回车，可以使用缺省值：awrrpt_2_12_13.html

2.4  关注报表具体内容

报表内的参数内容繁多，oracle官方网站搜索，也没有找到文档对如此众多参数的介绍。从存储的角度，个人认为需要关注的内容主要有：

Report Summery

Cache sizes

Load profile

Instance Efficiency Percentages (Target 100%)

Shared Pool Statistics

Top 5 Timed Events

IO Stats

Tablespace IO Stats

File IO Stats

2.4.1  Load Profile

1. Redo size

表示instance每秒/每事务产生的日志大小(单位字节)，可表示数据库任务的繁重与否。如日志量比较大，特别是每个事务的日志量都比较大的时候，需要检查是否有较大的事务在运行。

2. Logical reads

表示instance每秒/每事务产生的逻辑读，单位是block。

3. block changes

表示instance每秒/每事务block变化数量，数据库事务带来改变的块数量

4. Physical reads

表示instance每秒/每事务产生的物理读，单位是block。如果说逻辑读是从内存中获得数据，那么，物理读就是要实实在在的从硬盘或者磁盘阵列中获得数据，这个取决于磁盘和阵列的相应速度。与逻辑读同样，物理读也可能来自于select语句，而不一定仅是来自于事务。

5. Physical writes

表示instance每秒/每事务产生的物理写，单位是block。这个指标在很大程度上决定了数据库系统的写压力。

6. User calls

表示instance每秒/每事务产生的用户调用次数。User calls/Executes基本代表了每个语句的请求次数，Executes越接近User calls越好。

7. Parses

表示instance每秒/每事务解析总次数。包括软分析和硬分析，但是不包括快速分析。近似反应每秒/事务语句的执行次数,软解析每秒超过300次意味着你的"应用程序"效率不高，没有使用soft parse，建议调整session_cursor_cache。

8. Hard parses

表示instance每秒/每事务产生的硬解析次数，这在高可用的OLTP环境中应当尽可能避免，应当正确地使用绑定变量避免这样的情况发生。每秒超过100次，就可能说明你绑定使用的不好

9. Sorts

表示instance每秒/每事务产生的排序次数。

10. Executes

表示instance每秒/每事务产生的语句执行次数，包括了用户执行的SQL语句与系统执行的SQL语句，表示一个系统SQL语句的繁忙程度。

11. Transactions

表示instance每秒/每事务产生的事务数，表示一个数据库系统的事务繁重程度。这在不同的系统中，略有差异，如在一个典型的交易系统中（如：证券、银行），可能主要是事务比较多；而在一个网站系统，可能主要是Web查询（select）比较多。

12. Rollback per transaction%

看回滚率是不是很高，因为回滚很耗资源。如果回滚过高，可能说明你的数据库经历太多的无效操作。过多的回滚可能还会带来Undo Block的竞争。

2.4.2  Instance Efficiency Percentages (Target 100%)

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

2. Redo NoWait %

在Redo缓冲区获取Buffer的未等待比率

3. Buffer Hit %

数据块在数据缓冲区中得命中率。如果在典型的OLTP环境中，通常应在95%以上。否则应考虑加大db_cache_size,但是大量的非选择的索引也会造成该值很高（大量的db file sequential read）。

4. In-memory Sort %

在内存中的排序率。

5. Library Hit %

主要代表sql在共享区的命中率，通常在95%以上，否则，需要要考虑加大共享池，绑定变量，修改cursor_sharing等参数。

6. Soft Parse %

近似看作sql在共享区的命中率，小于95%，需要考虑到绑定。如果低于80%，那么可能是sql没有被重用。

7. Execute to Parse %

sql语句解析后被重复执行的次数，如果过低，可以考虑设置 session_cached_cursors参数, 公式为100 * (1 - Parses/Executions) = Execute to Parse所以如果系统Parses > Executions,就可能出现该比率小于0的情况, 该值<0通常说明shared pool设置或效率存在问题造成反复解析，reparse可能较严重,或者可是同snapshot有关如果该值为负值或者极低,通常说明数据库性能存在问题。

8. Latch Hit %

表示内部结构维护锁命中率，通常高于99％。其值低是因为shared_pool_size过大或没有使用绑定变量导致硬解析过多。

9. Parse CPU to Parse Elapsd %

解析实际运行事件/解析实际运行时间。

2.4.3  IO Stats

1. Tablespace IO Stats

ordered by IOs (Reads + Writes) desc

ordered by Tablespace, File

3.1  ADDM概述

现在 Oracle Database 10g 中推出的这个引擎称为自动数据库诊断监控程序既ADDM  (Automatic Performance Diagnosis)。为了作出决策，ADDM 使用了由 AWR 采集的数据。在 AWR 进行的每一次快照采集之后，调用 ADDM来检查量度并生成建议。因此，实际上拥有了一个一天二十四小时工作的自动数据库管理员，它主动地分析数据并生成建议，从而把DBA出来，使能够关注更具有战略意义的问题。

缺省情况下，在安装 Oracle 10g RAC软件时,即安装 EM 10g（Oracle Enterprise Manager）,在概念上它与以前版本的不同之处在于，它不是客户端安装的工具；实际上它是位于数据库服务器本身上的 HTTP 服务器（称为 DB 控制台）。（参见图）您可以使用任何浏览器查看 EM 界面。

3.2  ADDM的使用方法

3.2.1  在所有Oracle服务器上确认EM的状态，并启动EM进程

1. 登录所有Oracle服务器，查看EM的运行状态

#su - oracle

$ emctl status dbconsole

Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0 

Copyright (c) 1996, 2005 Oracle Corporation.  All rights reserved.

http://aix53:1158/em/console/aboutApplication

EM Daemon is not running.     // EM没有运行

------------------------------------------------------------------

Logs are generated in directory /oracle/OraHome/aix54_ora1/sysman/log

2. 如果没有运行，登录所有Oracle服务器开启EM进程

$ emctl start dbconsole

Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0 

Copyright (c) 1996, 2005 Oracle Corporation.  All rights reserved.

http://aix53:1158/em/console/aboutApplication

Starting Oracle Enterprise Manager 10g Database Control ..... started.

------------------------------------------------------------------

Logs are generated in directory /oracle/OraHome/aix54_ora1/sysman/log

3.2.2  通过IE浏览器，登录EM界面

1. 可通过如下方法查看端口号

$ pwd

/oracle/OraHome/install

$ more portlist.ini

iSQL*Plus HTTP port number =5560

Enterprise Manager Console HTTP Port (ora) = 1158

Enterprise Manager Agent Port (ora) = 3938

从这个文件中我们了解到，数据库 ora 的代理程序监听端口 3938，而 EM 控制台监听 1158。

2. 在IE浏览器内输入以下 URL来调用 EM 登录画面

输入http://aix53:1158/em/console/ 如果不能弹出界面，请用实际IP地址登录如下：http://203.203.2.153:1158/em/console/。该 URL 调出登录画面，从中您可以用 DBA 用户登录。

(1)          登录界面

(2)          主目录显示Oracle数据库大致运行信息

如：从GUI上看出“有问题的表空间”有“1”个，点击“1”可进一步查看是哪个表空间出现问题。

3.3  ADDM查询和诊断到的问题有那些

3.3.1  查看集群中Oracle服务器的硬件配置

1. 进入“集群数据库：ora”的“首目录”

2. 选择红框内的“集群：crs”并进入

3. 选择红框内“主机”数据，并进入

4. 选择“硬件：IBM_9110_510 powerpc”查看硬件信息或选择“操作系统详细资料”查看软件信息

信息显示如下

3.3.2  CPU的瓶颈

    通过查看主页面内的：主机 CPU 来实时监测CPU的状态和瓶颈。

3.3.3  内存结构上的瓶颈？

1.   进入“集群数据库：ora”的“首目录”

2. 选择红框内的“集群：crs”并进入

3. 选择“性能”标签，并选择具体主机并查看“内存详细资料”

4. 详细信息如下

        从以上可以很明显的看出：内存存在瓶颈。已经使用了90.35%。

3.3.4  内存参数SGA、PGA查看和分析

1. 进入“集群数据库：ora”的“首目录”

2. 拉到最底端，选择“指导中心”连接

3. 在进入“内存指导”连接

4. 选择具体实例查看内存参数，并选择“继续”

5. 显示具体实例的PGA和SGA的具体内容

3.3.5  I/O 是否存在性能瓶颈? (I/O子系统是否工作正常) ?

1.   进入“集群数据库：ora”的“首目录”

2. 选择红框内的“集群：crs”并进入

3. 选择“性能”标签，并选择具体主机并查看“磁盘详细资料”

4. 详细信息如下

     查看磁盘设备的写入的速度和磁盘使用率，来确认是否是磁盘存在瓶颈。

3.3.6  高负载的SQL语句 (是否存在消耗过量系统资源的SQL) ?

1. 进入“集群数据库：ora”的“首目录”

2. 选择“SQL优化指导”并进入

3. 选择具体实例查看内存参数，并选择“继续”

4. 选择“周期SQL”

5. 查看SQL语句的使用情况，根据“执行占用时间”查看哪个SQL语句高负荷

3.4  ADDM 分析的结果 

ADDM 分析的结果以一些"Finding"的样式来表达。 每个"Finding"都属于以下三种类型之一：

1. 问题: 描述了导致数据库性能问题的根源。

2. 征兆: 包含了可能导致其他问题的信息。

3. 信息: 报告其他没有问题的模块。

4  总结

AWR报表是针对实例的，多个实例需要多个AWR报表，从而了解到每个实例在应用环境的状态。

AWR报表的产生的过程大致如下：1、先生成快照点（手动或每隔一段时间自动产生）2、再通过@?/rdbms/admin/awrrpt命令选取相应的快照点，生成该快照点之间（时间段）的一个AWR报表。

ADDM工具，主要是用户或用服在现场观察用户Oracle实际运行情况。可以把如下图截取下来进行分析。

AWR报表内的参数有1000多个，不可能所有参数都去关注。从存储角度讲，如下参数应该关注，当然根据不同需要，可适当关注其他参数。

1. Redo size

表示instance每秒/每事务产生的日志大小(单位字节)，可表示数据库写任务的繁重与否。如日志量比较大，特别是每个事务的日志量都比较大的时候，需要检查是否有较大的事务在运行。

2. Logical reads

表示instance每秒/每事务产生的逻辑读，单位是block。

3. block changes

表示instance每秒/每事务block变化数量，数据库事务带来改变的块数量

4. Physical reads

表示instance每秒/每事务产生的物理读，单位是block。如果说逻辑读是从内存中获得数据，那么，物理读就是要实实在在的从硬盘或者磁盘阵列中获得数据，这个取决于磁盘和阵列的相应速度。与逻辑读同样，物理读也可能来自于select语句，而不一定仅是来自于事务。

5. Physical writes

表示instance每秒/每事务产生的物理写，单位是block。这个指标在很大程度上决定了数据库系统的写压力。

6. Transactions

表示instance每秒/每事务产生的事务数，表示一个数据库系统的事务繁重程度。这在不同的系统中，略有差异，如在一个典型的交易系统中（如：证券、银行），可能主要是事务比较多。而在一个网站系统，可能主要是Web查询（select）比较多。

5  附录

1、数据库事务：是指单个逻辑工作单元执行的一系列操作（SQL语句）。

2、从数据库优化的角度说，收益最大的是优化应用设计以及数据库设计，其次才是SQL语句优化。

3、在一个设计良好的OLTP环境中，必须最大限度的减少总体逻辑读，这是减少服务器CPU消耗的关键。

4、绑定变量

 在oracle 中，对于一个提交的sql语句，存在两种可选的解析过程， 一种叫做硬解析，一种叫做软解析。

一个硬解析需要经解析，制定执行路径、优化访问计划等许多的步骤。硬解释不仅仅耗费大量的cpu，更重要的是会占据重要的latch资源，严重的影响系统的规模的扩大（即了系统的并发行）， 而且引起的问题不能通过增加内存条和cpu的数量来解决。之所以这样是因为latch是为了顺序访问以及修改一些内存区域而设置的，这些内存区域是不能被同时修改。当一个sql语句提交后，oracle会首先检查一下共享缓冲池（shared pool）里有没有与之完全相同的语句，如果有的话只须执行软分析即可，否则就得进行硬分析。

而唯一使得oracle 能够重复利用执行计划的方法就是采用绑定变量。绑定变量的实质就是用于替代sql语句中的常量的替代变量。绑定变量能够使得每次提交的sql语句都完全一样。