专栏首页小麦苗的DB宝专栏【DB笔试面试822】在Oracle中,AWR报告中主要关注哪些方面内容?

【DB笔试面试822】在Oracle中,AWR报告中主要关注哪些方面内容?

题目部分

【DB笔试面试822】在Oracle中,AWR报告中主要关注哪些方面内容?

答案部分

AWR报告中常常需要关注如下的内容:

(一)DB Time/Elapsed

该部分位于AWR报告的头部,如下图所示,需要特别关注DB Time和Elapsed的比值:

Elapsed:60.03(mins)表明采样时间大约是60分钟,任何数据都要通过这个时间来衡量,离开了这个采样时间,任何数据都毫无意义,Elapsed为该AWR性能报告的自然时间跨度,所谓自然时间的跨度,例如前一个快照是4点生成的,后一个快照是6点生成的,如果使用“@?/rdbms/admin/awrrpt”脚本中指定这2个快照的话,那么其Elapsed=(6-4)=2个小时。一个AWR报告至少需要2个AWR快照才能生成(注意在这2个快照之间实例不能重启过,否则指定这2个快照生成AWR报告会报错)。AWR性能报告中的指标往往是后一个快照和前一个快照的指标的delta值,这是因为累计值并不能反映某段时间内的系统负载情况。如果为了诊断特定时段性能问题,那么采用时间不宜过长。如果是看全天负载,那么可以长一些。最常见是60分钟或120分钟。

DB Time:427.44(mins)表明用户操作花费的时间,包括CPU时间和活动的非后台进程的等待时间,也许有人会觉得奇怪,为什么在采样的60分钟过程中,用户操作时间竟然有427分钟呢?远远超过了采样时间,原因是AWR报告是一个数据的集合,例如在一分钟之内,一个用户等待了30秒,那么10个用户就等待了300秒。对于CPU的话,一个CPU处理了30秒,16个CPU就是480秒。这些时间都是以累积的方式记录在AWR报告中的。DB Time不包括Oracle后台进程消耗的时间。一般来说,如果DB Time除以CPU个数大于Elapsed时间,那么说明数据库比较繁忙。

(二)Load Profile

该部分位于AWR报告的总览部分(Report Summary),AWR报告总览部分包括了五个部分:缓存尺寸(Cache Sizes)、负载性能(Load Profile)、数据库效率(Instance Efficiency Percentages)、共享池统计(Shared Pool Statistics)、TOP5事件(Top 5 Timed Events)。这五个部分是整个AWR报告的核心部分,记录了数据库系统的关键性能参数和状况。其中,Load Profile代表负载性能,即系统负载信息,从每秒钟和每个事务两个维度统计的,单纯的数字也无太大意义,需要与Baseline(基线)做比较才有意义。

下表是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:

16.18

对Load Profile中的每个指标的解析如下所示:

v Redo size:每秒/每事务产生的日志大小(单位是字节),可标志数据变更频率,数据库任务的繁重与否。

v Logical reads:平均每秒/每事务产生的逻辑读的块数(单位是Block)。Logical Reads= Consistent Gets + DB Block Gets。

v Block changes:每秒/每事务修改的块数,即数据库事务改变数据块的数量。

v Physical reads:每秒/每事务物理读(磁盘读)的块数(单位是Block)。

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

v User calls:每秒/每事务用户调用次数。

v Parses:SQL每秒/每事务解析的次数,包括Fast Parse、Soft Parse和Hard Parse三种解析的综合。

v Hard parses:每秒/每事务硬解析的次数,硬解析太多,说明SQL重用率不高。每秒产生的硬解析次数超过100次,就可能说明绑定变量使用地不好,也可能是共享池设置不合理。

v Sorts:每秒/每事务的排序次数,对于Sorts大于每秒100,表明排序过多,需要减少SQL代码中排序操作,或调整排序空间。

v Logons:每秒/每事务登录的次数,大于每秒1~2个,表明可能有争用问题。

v Executes:每秒/每事务SQL执行次数,反应负载大小。

v Transactions:每秒事务数,反映数据库任务繁重与否。

v Blocks changed per Read:表示逻辑读用于修改数据块的比例,在每一次逻辑读中更改的块的百分比。

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

v Rollback per transaction:每个事务的回滚率。用来观察回滚率是不是很高,因为回滚很占用资源,如果回滚率过高,那么可能说明数据库有太多的无效操作,过多的回滚可能还会带来Undo Block的竞争。

v Rows per Sort:每次排序的行数。

(三)Instance Efficiency Percentages (Target 100%)

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

下表是该部分的一个示例表格:

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

该部分的各个指标解析如下所示:

v 缓冲区未等待率(Buffer Nowait %):表示在内存获得数据的未等待比率。Buffer Nowait的这个值一般需要大于99%。否则可能存在争用,可以在后面的等待事件中进一步确认。如果该值较低,那么可能要增大BUFFER CACHE,期望值是100%,不应该低于99%。

v 缓冲区命中率(Buffer Hit %):表示进程从内存中找到数据块的比率,即数据块在数据缓冲区中的命中率,通常应在95%以上。监视这个值是否发生重大变化比仅仅观察这个值本身更重要。如果小于95%,那么就需要调整重要的参数,如果小于90%,那么就可能需要加DB_CACHE_SIZE。对于一般的OLTP系统,如果此值低于80%,那么应该给数据库分配更多的内存。命中率的突变,往往是一个不好的信息。如果命中率突然增大,那么可以检查top buffer get SQL,查看导致大量逻辑读的语句和索引;如果命中率突然减小,那么可以检查top physical reads SQL,检查产生大量物理读的语句,主要是那些没有使用索引或者索引被删除的SQL语句。

v Redo缓冲区未等待率(Redo Nowait %):表示在LOG缓冲区(Redo Log Buffer)获得BUFFER的未等待比率,该指标的值应接近100%。如果该值较低,那么有两种可能的情况:1)联机Redo日志文件没有足够的空间;2)LOG切换速度较慢。如果太低(可参考90%阀值),那么考虑增加LOG_BUFFER。

v 库缓存命中率(Library Hit%):表示Oracle从Library Cache中检索到一个解析过的SQL或PL/SQL语句的比率,当应用程序调用SQL或存储过程时,Oracle检查Library Cache确定是否存在解析过的版本,如果存在,那么Oracle立即执行语句;如果不存在,那么Oracle解析此语句,并在Library Cache中为它分配共享SQL区。该值过低说明有过多的解析,CPU消耗增加,性能降低。如果该值低于90%,那么可能需要调大Shared Pool区。

v 闩锁命中率(Latch Hit %):Latch是一种保护内存结构的锁,可以认为是SERVER进程获取访问内存数据结构的许可。要确保Latch Hit大于99%,否则意味着Shared Pool latch争用,可能由于未共享的SQL或Library Cache太小,可使用绑定变更或调大Shared Pool解决。当该值出现问题的时候,可以借助后面的等待事件和Latch来分析查找解决问题。

v CPU时间占整个解析时间比率(Parse CPU to Parse Elapsd %):表示在解析SQL语句过程中,CPU占整个的解析时间比例,期望值是100%,说明解析没有产生等待,计算公式为:解析实际运行时间/(解析实际运行时间+解析中等待资源时间),该值越大越好。如果该值为100%,那么意味着CPU等待时间为0,没有任何等待。

v CPU非解析时间百分比(Non-Parse CPU %):即SQL实际运行时间/(SQL实际运行时间+SQL解析时间)。该值太小表示解析消耗CPU时间过多,该值越大越好,说明计算机执行的大部分工作是执行查询的工作,而不是分析查询的工作。

v 解析与执行的比率(Execute to Parse %):指的是SQL语句解析与执行的比例,如果SQL重用率高,那么这个比例会很高。该值越高表示一次解析后被重复执行的次数越多。该值越大越好,说明一次解析,到处执行。计算公式为:Execute to Parse=100*(1-PARSES/EXECUTIONS)。如果系统PARSES大于EXECUTIONS,那么就可能出现该比率小于0的情况。若该值小于0,则通常说明Shared Pool设置或者语句效率存在问题,造成反复解析,REPARSE可能较严重。

v 内存排序率(In-memory Sort %):表示在内存中排序的比率,如果过低,那么说明有大量的排序在临时表空间中进行,此时可以考虑调大PGA。该指标的值应接近100%,如果低于95%,那么可以通过适当调大初始化参数PGA_AGGREGATE_TARGET或者SORT_AREA_SIZE来解决,注意,这两个参数设置作用的范围是不同的,SORT_AREA_SIZE是针对每个SESSION设置的,PGA_AGGREGATE_TARGET则是针对所有的SESSION的。

v 软解析的百分比(Soft Parse %):表示软解析的百分比,近似当作SQL在共享区的命中率。若该值小于95%,则需要考虑绑定变量,如果低于80%,那么就可以认为SQL基本没有被重用。该指标的值通常应在95%以上,期望值是100%,有一点要说明的是,不要单方面的追求软解析的高比例,而去绑定变量,要看性能的瓶颈在哪里。

(四)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 waits”是较严重的等待事件,那么应当继续研究报告中Buffer Wait和File/Tablespace I/O区的内容,识别哪些文件导致了问题。如果最严重的等待事件是I/O事件,那么应当研究按物理读排序的SQL语句区以识别哪些语句在执行大量I/O,并研究Tablespace和I/O区观察较慢响应时间的文件。如果有较高的LATCH等待,那么就需要察看详细的LATCH统计识别哪些LATCH产生的问题。

一个性能良好的系统,CPU TIME应该在TOP 5的前面,否则说明系统大部分时间都用在等待上。

(五)SQL Statistics

SQL Statistics分别从执行时间、物理读、逻辑读、子游标个数、执行次数等方面罗列出TOP语句,从该部分可以迅速获取有性能问题的SQL语句,如下所示:

l SQL ordered by Elapsed Time

l SQL ordered by CPU Time

l SQL ordered by Gets

l SQL ordered by Reads

l SQL ordered by Executions

l SQL ordered by Parse Calls

l SQL ordered by Version Count

以“lSQL ordered by Elapsed Time”为例,该部分记录了执行总时间的SQL语句,记录的是监控范围内该SQL的执行时间总和,需要综合分析CPU时间(CPU Time)和执行次数(Executions)才能得到单个SQL的代价。单次执行开销较大的SQL属于重点优化之列。

该部分的一个示例表如下所示:

Elapsed Time (s)

Executions

Elapsed Time per Exec (s)

%Total

%CPU

%IO

SQL Id

SQL Module

SQL Text

12,418,953.35

2,376,222

5.23

99.49

0.03

0.00

1cmnjddakrqbv

JDBC Thin Client

update orgtion o set o.qu...

3,791.90

2,129,113

0.00

0.03

24.33

2.73

26ad9zvt5xgb3

JDBC Thin Client

insert into tran_success...

2,882.78

3,267,011

0.00

0.02

16.76

13.88

an08dyryjns25

JDBC Thin Client

select t.trans_id, t.id_type, ...

1,100.69

2,129,218

0.00

0.01

18.21

0.01

g8mxscbjf4t8f

JDBC Thin Client

select count(0) from tra_boo...

861.17

541,558

0.00

0.01

22.34

2.40

5ww8x9u15a90y

JDBC Thin Client

insert into transuccess...

675.32

19,773,101

0.00

0.01

55.62

0.00

dzmtc8dyfsv0v

JDBC Thin Client

select sysdate from dual

对于每个指标的解析如下:

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

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

v Executions:SQL语句在监控范围内的执行次数总和。若Executions为0,则说明语句没有正常执行完成,被中间停止,需要关注。

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

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

v SQL Id:SQL语句的ID编号,点击之后就能导航到下面的SQL详细列表中,点击浏览器的返回按钮可以回到当前SQL Id的地方。

v SQL Module:显示该SQL是用什么方式连接到数据库的。

v SQL Text:简单的SQL文本。

(六)Segment Statistics

该部分从段(表段、索引段)的角度描述了数据库的繁忙程度,包含了逻辑读、物理读、ITL等方面。若等待事件“enq: TX - row lock contention”发生次数比较多,则可以查看“Segments by Row Lock Waits”部分内容,找到发生行锁的表。若等待事件“enq: TX - allocate ITL entry”发生次数比较多,则可以查看“Segments by ITL Waits”部分内容,找到发生ITL等待的表。若等待事件“Buffer Busy Waits”发生次数比较多,则可以查看“Segments by Buffer Busy Waits”部分内容,找到那些对象访问频繁,从而导致热块的产生。

还有其它的一些需要关注的内容,这里就不详细介绍了。

& 说明:

有关每一种等待事件的解释,可以关注作者微信公众号(参考附录部分)。

有关如何阅读AWR报告可以参考我的BLOG:http://blog.itpub.net/26736162/viewspace-2121787/

本文选自《Oracle程序员面试笔试宝典》,作者:小麦苗

本文分享自微信公众号 - DB宝(lhrdba),作者:小麦苗best

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-06-15

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 【DB笔试面试464】动态SQL是什么?

    在PL/SQL开发过程中,使用SQL或PL/SQL可以实现大部分的需求,但是,在某些特殊的情况下,在PL/SQL中使用标准的SQL语句或DML语句不能实现自己的...

    小麦苗DBA宝典
  • 【DB笔试面试603】在Oracle中,固定SQL执行计划的方法有哪些?

    在实际项目中,通常在开发环境下,一些SQL执行没有任何功能问题,而当到了生产环境或生产环境的数据量发生较大的变量时,其SQL的执行效率非常低。此时如果更改SQL...

    小麦苗DBA宝典
  • 【DB笔试面试670】在Oracle中,什么是SQL实时监控?

    在Oracle 11g中,V$SESSION视图增加了一些新的字段,这其中包括SQL_EXEC_START和SQL_EXEC_ID,这两个字段实际上代表了Ora...

    小麦苗DBA宝典
  • SQL Server入门

    SQL Server是由Microsoft开发和推广的以客户/服务器(c/s)模式访问、使用Transact-SQL语言的关系数据库管理系统(DBMS),它最初...

    fireWang
  • 7张图学会SQL

    很多朋友平时处理数据可能更熟悉Excel,提到SQL就想逃避,殊不知SQL在处理大量数据时有Excel无法比拟的优势。而且根据二八定律,其实只需掌握20%SQL...

    猴子聊数据分析
  • 7张图学会SQL

    很多朋友平时处理数据可能更熟悉Excel,提到SQL就想逃避,殊不知SQL在处理大量数据时有Excel无法比拟的优势。而且根据二八定律,其实只需掌握20%SQL...

    猴子聊数据分析
  • 一条SQL生命历程

    1.一条SQL的诞生首先需要通过某种方式传递给数据库。数据库会有一个客户端用来与外界交流,而作为提交SQL的一方,可以通过ODBC或者是JDBC协议直接将SQL...

    哒呵呵
  • 零基础 SQL 数据库小白,从入门到精通的学习路线与书单

    我观察了 865 个 SQL 入门者,发现大家在学习 SQL 的时候,最大的问题不是 SQL 语法,而是对 SQL 原理的不熟悉。

    Lenis
  • 【DB笔试面试603】在Oracle中,固定SQL执行计划的方法有哪些?

    在实际项目中,通常在开发环境下,一些SQL执行没有任何功能问题,而当到了生产环境或生产环境的数据量发生较大的变量时,其SQL的执行效率非常低。此时如果更改SQL...

    小麦苗DBA宝典
  • 【SQL Performance】实时SQL监控功能(Real-Time SQL Monitoring)

    实时SQL监控功能(Real-Time SQL Monitoring)是Oracle11g推出的功能,通过这个功能可以实时地监视执行中的SQL性能。

    TeacherWhat

扫码关注云+社区

领取腾讯云代金券