AWR报告解析

本文涉及的产品
全局流量管理 GTM,标准版 1个月
云解析 DNS,旗舰版 1个月
公共DNS(含HTTPDNS解析),每月1000万次HTTP解析
简介: AWR报告解析

     当我们把一条sql送到数据库去执行的时候,我们要知道,什么时候用到cpu,什么时候用到内存,什么时候用到io

在看性能指标的时候,心里先要明白,数据库出现性能问题,一般都在三个地方,io,内存,cpu,这三个又是息息相关的(ps:我们先假设这个三个地方都没有物理上的故障),当io负载增大时,肯定需要更多的内存来存放,同时也需要cpu花费更多的时间来过滤这些数据,相反,cpu时间花费多的话,有可能是解析sql语句,也可能是过滤太多的数据,到不一定是和io或内存有关系了 。
     1. cpu
:解析sql语句,尝试多个执行计划,最后生成一个数据库认为是比较好的执行计划,不一定是最优的,因为关联表太多的时候,数据库并不会穷举所有的执行计划,这会消耗太多的时间,oracle怎么就知道这条数据时你要,另一个就不是你要的呢,这是需要cpu来过滤的
     2.
内存:sql语句和执行计划都需要在内存保留一段时间,还有取到的数据,根据lru算法也会尽量在内存中保留,在执行sql语句过程中,各种表之间的连接,排序等操作也要占用内存
     3. io
:如果需要的数据在内存中没有,则需要到磁盘中去取,就会用到物理io了,还有表之间的连接数据太多,以及排序等操作内存放不下的时候,也需要用到临时表空间,也就用到物理io

          这里有一点说明的是,虽然oracle占用了8G的内存,但pga一般只占8G20%,对于专用服务器模式,每次执行sql语句,表数据的运算等操作,都在pga中进行的,也就是说只能用1.6G左右的内存,如果多个用户都执行
多表关联,而且表数据又多,再加上关联不当的话,内存就成为瓶颈了,所有优化sql很重要的一点就是,减少逻辑读和物理读。

 

  •       AWR

      首先要看俩个时间
     Elapsed: 240.00 (mins)
表明采样时间是240分钟,任何数据都要通过这个时间来衡量,离开了这个采样时间,任何数据都毫无疑义
     DB Time: 92,537.95 (mins)
表明用户操作花费的时候,包括cpu时间喝等待时间,也许有人会觉得奇怪,为什么在采样的240分钟过程中,用户操作时间竟然有92537分钟呢,远远超过了
     采样时间,原因是awr报告是一个数据的集合,比如在一分钟之内,一个用户等待了30秒,那么10个用户就等待了300秒,对于cpu的话,一个cpu处理了30秒,16cpu就是4800秒,这些时间都是以累积的方式记录在awr报告中的。

  •   为了对数据库有个整体的认识,先看下面的性能指标
  • 1.png

  1. Buffer Nowait 说明 在从内存取数据的时候,没有经历等待的比例,期望值是100%
  2. Buffer Hit
说明从内存取数据的时候,buffer的命中率的比例,期望值是100%,但100%并不代表性能就好,因为这只是一个比例而已,举个例子,执行一条sql语句,# 执行计划是需要取10000个数据块,结果内存中还真有这10000个数据块,那么比例是100%,表面上看是性能最高的,还有一个执行计划是需要500 个数据块,内存中有250个,另外250个需要在物理磁盘中取,
     这种情况下,buffer hit50%,结果呢,第二个执行计划性能才是最高的,所以说100%并不代表性能最好
  3. Library Hit
说明sqlShared Pool的命中率,期望值是100%
  4. Execute to Parse
说明解析sql和执行sql之间的比例,越高越好,说明一次解析,到处执行,如果parse多,execute少的话,还会出现负数,因为计算公式是100*1-parse/execute
  5. Parse CPU to Parse Elapsd
说明在解析sql语句过程中,cpu占整个的解析时间比例,,期望值是100%,说明没有产生等待,需要说明的是,即使有硬解析,只要cpu没有出现性能问题,也是可以容忍的,比较硬解析也有它的好处的
  6. Redo NoWait
说明在产生日志的时候,没有产生等待,期望值是100%
  7. Soft Parse
说明软解析的比例,期望值是100%,有一点要说明的是,不要单方面的追求软解析的高比例,而去绑定变量,要看性能的瓶颈在哪里
  8. Latch Hit
说明latch的命中率,期望值是100%latch类似锁,是一种内存锁,但只会产生等待,不会产生阻塞,和lock还是有区别的,latch是在并发的情况下产生的
  9. Non-Parse CPU
说明非解析cpu的比例,越高越好,用100减去这个比例,可以看出解析sql所花费的cpu100-99.30=0.7,说明花费在解析sql上的cpu很少

 

  • Time Model Statistics

2.png

 可以看出,在整个sql执行时间(sql execute elapsed time)时间为5552019秒中,解析时间(parse time elapsed)用了36秒,硬解析时间(hard parse elapsed time)用了34秒虽然硬解析时间占了整个解析时间的绝大部分,但解析时间是花的很少的,所以可以判断出,sql的解析没有成为性能的瓶 颈,进一步推测,sql在获取数据的过程中遇到了瓶颈

 

 

  • Top 5 Timed Events,从这里可以看出等待时间在前五位的是什么事件,基本上就可以判断出性能瓶颈在什么地方

3.png

 1. buffer busy waits 说明在获取数据的过程中,频繁的产生等待事件,很有可能产生了热点块,也就是说,很多会话都去读取同样的数据块,这一事件等待了5627394次,总共等待了5322924秒,平均等待时间为946毫秒,而且频率也是最高的,有95.9%,等待类别是并发
     这里有一个概念:oracle操作的最小单位是块,当一个会话要修改这个块中的一条记录,会读取整个块,如果另一个会话要修改的数据也正好在这个块中,虽然这俩个
  2.
会话修改的记录不一样,也会产生等待direct path write tempdirect path read temp 说明用到了临时表空间,那我们再看一下Tablespace IO Stats

 4.png

各项指标都是非常高的,再根据上面的In-memory Sort100%,没有产生磁盘排序,也就在排序的时候没有用到临时表空间,进一步推测,多个session,每个session执行的sql语句中多表关联,产生了很多中间数据,pga内存中放不下,
         用到了临时表空间,也有可能是用到了lob字段,在用lob字段的时候,也会用到临时表

   *
继续看SQL Statistics
     
根据buffer busy waits等待次数,时间,频率都是最高的,我们重点看逻辑读,物理读,和执行时间最长的sql,把排在前几位的拿出来优化
     优化的原则为降低物理读,逻辑读,sql语句中的子操作执行次数尽量少,在看oracle估计出来的执行计划是看不出子操作的执行次数的,要看运行时的执行计划

   *
有兴趣的话还可以看一下Segment Statistics
     
列出了用到的索引和表的使用情况,从这里也能看出索引和表的使用频率

   *
也可以看一下Load Profile
     
里面列出了每秒,每个事务所产生的日志,逻辑读和物理读等指标

AWR的数据主要有两部分组成:
1
)保存在内存中的系统负载和性能统计数据,主要通过v$视图查询 ;
2
mmon进程定期以快照(snapshot)的方式将内存中的AWR数据保存到SYSAUX表空间中,主要通过DBA_*视图访问。

1. AWR
快照的生成
默认情况下,每隔一小时自动产生一个快照,保存最近7天的信息,可以通过以下语句查询:
SQL>select SNAP_INTERVAL,RETENTION from dba_hist_wr_control;

SNAP_INTERVAL       RETENTION
----------------------------------------------------------
+00000 01:00:00.0       +00007 00:00:00.0
可以通过以下语句修改时间间隔和保存时间(以分钟为单位)
exec dbms_workload_repository.modify_snapshot_settings(interval => 30, retention = > 10*24*60);
也可以根据需要随时手动生成快照:
exec dbms_workload_repository.create_snapshot;

2. AWR报告的生成
sysdba运行如下命令:
@?/rdbms/admin/awrrpt.sql

3. AWR报告的分析
策略

因为AWR报告非常长,不可能从头到尾一字不漏的去看,要有选择的去看重点部分。最好能对照的来读,即和系统正常情况下的AWR报告对比,找差异。

AWR
报告采用总分的形式,前面是系统的整体情况,后面是各个部分细节,一开始不要陷入细节,先分析系统的整体状况,对于后面的专题分析,要根据关注点的不同,采取跳跃式分析。
还要根据具体业务的不同,决定某种现象是否正常。

系统整体状况方面
1
Load Profile:分析系

了解系统整体负载状况,如每秒中的事务数/语句数,每秒/每事务物理读写次数(Physical Reads/Writes), 逻辑读写次数(Logical Reads/Writes)SQL语句的解析(Parse),特别是硬解析次数等。

2Instance Efficiency Percentages各指标都应接近100%,除了:execute to parse (70%以上)parse cpu to parse elapsed。如果不符合,基本可以确定系统存在性能问题;但是如果反过来,即都符合,也不能说明系统完全正常,还要看实际情况。

具体状况方面
1
Top 5 Timed Events:这里列出消耗时间最多的5个等待事件,每种等待说明,都表示一种原因,如:db file sequential read表示按索引访问出现等待,db file scattered reade表示全表扫描访问出现等待事件。
2
Top N SQL:根据时间消耗,内存消耗,物理I/O等排序,对相关SQL分析执行计划
3
)如果是RAC环境,需要特别关注RAC Statistic中的相关指标
4
SGA PGA分析
5
)分析表空间、数据文件I/O

 

 

WORKLOAD REPOSITORY report for

DB Name

DB Id

Instance

Inst num

Release

RAC

Host

ICCI

1314098396

ICCI1

1

10.2.0.3.0

YES

HPGICCI1

 

 

 

Snap Id

Snap Time

Sessions

Cursors/Session

Begin Snap:

2678

25-Dec-08 14:04:50

24

1.5

End Snap:

2680

25-Dec-08 15:23:37

26

1.5

Elapsed:

 

78.79 (mins)

 

 

DB Time:

 

11.05 (mins)

 

 

DB Time不包括Oracle后台进程消耗的时间。如果DB Time远远小于Elapsed时间,说明数据库比较空闲。

79分钟里(其间收集了3次快照数据),数据库耗时11分钟,RDA数据中显示系统有8个逻辑CPU4个物理CPU),平均每个CPU耗时1.4分钟,CPU利用率只有大约2%1.4/79)。说明系统压力非常小。

可是对于批量系统,数据库的工作负载总是集中在一段时间内。如果快照周期不在这一段时间内,或者快照周期跨度太长而包含了大量的数据库空闲时间,所得出的分析结果是没有意义的。这也说明选择分析时间段很关键,要选择能够代表性能问题的时间段。

 

Report Summary

Cache Sizes

 

Begin

End

 

 

Buffer Cache:

3,344M

3,344M

Std Block Size:

8K

Shared Pool Size:

704M

704M

Log Buffer:

14,352K

显示SGA中每个区域的大小(在AMM改变它们之后),可用来与初始参数值比较。

shared pool主要包括library cachedictionary cachelibrary cache用来存储最近解析(或编译)后SQLPL/SQLJava classes等。library cache用来存储最近引用的数据字典。发生在library cachedictionary cachecache miss代价要比发生在buffer cache的代价高得多。因此shared pool的设置要确保最近使用的数据都能被cache

 

Load Profile

 

Per Second

Per Transaction

Redo size:

918,805.72

775,912.72

Logical reads:

3,521.77

2,974.06

Block changes:

1,817.95

1,535.22

Physical reads:

68.26

57.64

Physical writes:

362.59

306.20

User calls:

326.69

275.88

Parses:

38.66

32.65

Hard parses:

0.03

0.03

Sorts:

0.61

0.51

Logons:

0.01

0.01

Executes:

354.34

299.23

Transactions:

1.18

 

 

% Blocks changed per Read:

51.62

Recursive Call %:

51.72

Rollback per transaction %:

85.49

Rows per Sort:

########

显示数据库负载概况,将之与基线数据比较才具有更多的意义,如果每秒或每事务的负载变化不大,说明应用运行比较稳定。单个的报告数据只说明应用的负载情况,绝大多数据并没有一个所谓“正确”的值,然而Logons大于每秒1~2个、Hard parses大于每秒100、全部parses超过每秒300表明可能有争用问题。

Redo size:每秒/每事务产生的redo大小(单位字节),可标志数据库任务的繁重程序。

Logical reads:每秒/每事务逻辑读的块数

Block changes:每秒/每事务修改的块数

Physical reads:每秒/每事务物理读的块数

Physical writes:每秒/每事务物理写的块数

User calls:每秒/每事务用户call次数

ParsesSQL解析的次数

Hard parses:其中硬解析的次数,硬解析太多,说明SQL重用率不高。

Sorts:每秒/每事务的排序次数

Logons:每秒/每事务登录的次数

Executes:每秒/每事务SQL执行次数

Transactions:每秒事务数

Blocks changed per Read:表示逻辑读用于修改数据块的比例

Recursive Call:递归调用占所有操作的比率

Rollback per transaction:每事务的回滚率

Rows per Sort:每次排序的行数

:

Oracle的硬解析和软解析

  提到软解析(soft prase)和硬解析(hard prase),就不能不说一下Oraclesql的处理过程。当你发出一条sql语句交付Oracle,在执行和获取结果前,Oracle对此sql将进行几个步骤的处理过程:

  1、语法检查(syntax check)

  检查此sql的拼写是否语法。

  2、语义检查(semantic check)

  诸如检查sql语句中的访问对象是否存在及该用户是否具备相应的权限。

  3、对sql语句进行解析(prase)

  利用内部算法对sql进行解析,生成解析树(parse tree)及执行计划(execution plan)

  4、执行sql,返回结果(execute and return)

  其中,软、硬解析就发生在第三个过程里。

  Oracle利用内部的hash算法来取得该sqlhash值,然后在library cache里查找是否存在该hash值;

  假设存在,则将此sqlcache中的进行比较;

  假设“相同”,就将利用已有的解析树与执行计划,而省略了优化器的相关工作。这也就是软解析的过程。

  诚然,如果上面的2个假设中任有一个不成立,那么优化器都将进行创建解析树、生成执行计划的动作。这个过程就叫硬解析。

  创建解析树、生成执行计划对于sql的执行来说是开销昂贵的动作,所以,应当极力避免硬解析,尽量使用软解析。

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %:

100.00

Redo NoWait %:

100.00

Buffer Hit %:

98.72

In-memory Sort %:

99.86

Library Hit %:

99.97

Soft Parse %:

99.92

Execute to Parse %:

89.09

Latch Hit %:

99.99

Parse CPU to Parse Elapsd %:

7.99

% Non-Parse CPU:

99.95

本节包含了Oracle关键指标的内存命中率及其它数据库实例操作的效率。其中Buffer Hit Ratio 也称Cache Hit RatioLibrary Hit ratio也称Library Cache Hit ratio。同Load Profile一节相同,这一节也没有所谓“正确”的值,而只能根据应用的特点判断是否合适。在一个使用直接读执行大型并行查询的DSS环境,20%Buffer Hit Ratio是可以接受的,而这个值对于一个OLTP系统是完全不能接受的。根据Oracle的经验,对于OLTPT系统,Buffer Hit Ratio理想应该在90%以上。

Buffer Nowait表示在内存获得数据的未等待比例。

buffer hit表示进程从内存中找到数据块的比率,监视这个值是否发生重大变化比这个值本身更重要。对于一般的OLTP系统,如果此值低于80%,应该给数据库分配更多的内存。

Redo NoWait表示在LOG缓冲区获得BUFFER的未等待比例。如果太低(可参考90%阀值),考虑增加LOG BUFFER

library hit表示OracleLibrary Cache中检索到一个解析过的SQLPL/SQL语句的比率,当应用程序调用SQL或存储过程时,Oracle检查Library Cache确定是否存在解析过的版本,如果存在,Oracle立即执行语句;如果不存在,Oracle解析此语句,并在Library Cache中为它分配共享SQL区。低的library hit ratio会导致过多的解析,增加CPU消耗,降低性能。如果library hit ratio低于90%,可能需要调大shared pool区。

Latch HitLatch是一种保护内存结构的锁,可以认为是SERVER进程获取访问内存数据结构的许可。要确保Latch Hit>99%,否则意味着Shared Pool latch争用,可能由于未共享的SQL,或者Library Cache太小,可使用绑定变更或调大Shared Pool解决。

Parse CPU to Parse Elapsd:解析实际运行时间/(解析实际运行时间+解析中等待资源时间),越高越好。

Non-Parse CPU SQL实际运行时间/(SQL实际运行时间+SQL解析时间),太低表示解析消耗时间过多。

Execute to Parse:是语句执行与分析的比例,如果要SQL重用率高,则这个比例会很高。该值越高表示一次解析后被重复执行的次数越多。

In-memory Sort:在内存中排序的比率,如果过低说明有大量的排序在临时表空间中进行。考虑调大PGA

Soft Parse:软解析的百分比(softs/softs+hards),近似当作sql在共享区的命中率,太低则需要调整应用使用绑定变量。

Shared Pool Statistics

 

Begin

End

Memory Usage %:

47.19

47.50

% SQL with executions>1:

88.48

79.81

% Memory for SQL w/exec>1:

79.99

73.52

Memory Usage %:对于一个已经运行一段时间的数据库来说,共享池内存使用率,应该稳定在75%-90%间,如果太小,说明Shared Pool有浪费,而如果高于90,说明共享池中有争用,内存不足。

SQL with executions>1:执行次数大于1sql比率,如果此值太小,说明需要在应用中更多使用绑定变量,避免过多SQL解析。

Memory for SQL w/exec>1:执行次数大于1SQL消耗内存的占比。

Top 5 Timed Events

Event

Waits

Time(s)

Avg Wait(ms)

% Total Call Time

Wait Class

CPU time

 

515

 

77.6

 

SQL*Net more data from client

27,319

64

2

9.7

Network

log file parallel write

5,497

47

9

7.1

System I/O

db file sequential read

7,900

35

4

5.3

User I/O

db file parallel write

4,806

34

7

5.1

System I/O

这是报告概要的最后一节,显示了系统中最严重的5个等待,按所占等待时间的比例倒序列示。当我们调优时,总希望观察到最显著的效果,因此应当从这里入手确定我们下一步做什么。例如如果‘buffer busy wait’是较严重的等待事件,我们应当继续研究报告中Buffer WaitFile/Tablespace IO区的内容,识别哪些文件导致了问题。如果最严重的等待事件是I/O事件,我们应当研究按物理读排序的SQL语句区以识别哪些语句在执行大量I/O,并研究TablespaceI/O区观察较慢响应时间的文件。如果有较高的LATCH等待,就需要察看详细的LATCH统计识别哪些LATCH产生的问题。

在这里,log file parallel write是相对比较多的等待,占用了7%CPU时间。

通常,在没有问题的数据库中,CPU time总是列在第一个。

目录
相关文章
|
Oracle 关系型数据库 数据库
Oracle 11G常见性能诊断报告(AWR/ADDM/ASH)收集
Oracle 11G常见性能诊断报告(AWR/ADDM/ASH)收集
320 0
|
6月前
|
SQL 监控 Oracle
Oracle 性能优化之AWR、ASH和ADDM(含报告生成和参数解读)
Oracle 性能优化之AWR、ASH和ADDM(含报告生成和参数解读)
|
Oracle 关系型数据库 数据库
oracle手工生成AWR报告方法记录
AWR(Automatic Workload Repository)报告是我们进行日常数据库性能评定、问题SQL发现的重要手段。熟练掌握AWR报告,是做好开发、运维DBA工作的重要基本功。
1432 0
|
SQL 数据库
如何生成 AWR 报告和 AWR 基线 (Doc ID 2331572.1)
如何生成 AWR 报告和 AWR 基线 (Doc ID 2331572.1)
142 0
|
Oracle 关系型数据库 BI
|
SQL Oracle 关系型数据库
ORACLE AWR报告生成过程出现多个实例记录分析
在一次生成AWR报告中,发现在“Instances in this Workload Repository schema”部分,出现了多个实例记录信息(host敏感信息被用host1,host2,host3替换)。
1319 0
|
监控 Oracle 关系型数据库
Oracle:AWR报告收集中断的问题
目前的数据库巡检,主要依赖袋鼠云自研管控平台EasyDB,它可以提供完善的数据库和主机性能/资源信息,并且配备有短信、钉钉、电话等告警;可接入本地或云上实例;注册SaaS版可以体验所有功能,不收取费用 https://easydb.
4143 0
|
Web App开发 关系型数据库 数据库
|
SQL Oracle 关系型数据库