AWR报告（二）--读懂AWR--10G

教学链接：http://www.tudou.com/programs/view/Dp_YYjvX-KM/    --感谢maclean大师

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)

 

 

这里是一些基本信息。跨度为3个快照。一共经过了79分钟左右，在79分钟里（其间收集了3次快照数据），数据库耗时11分钟，RDA数据中显示系统有8个逻辑CPU（4个物理CPU），平均每个CPU耗时11/8=1.4分钟，CPU利用率只有大约2%（1.4/79）。说明系统压力非常小。

BUSY_TIME + IDLE_TIME = ELAPSED_TIME * CPU_COUNT

比如：服务器是AIX的系统，4个双核cpu,共8个核:

/sbin> bindprocessor -q
The available processors are: 0 1 2 3 4 5 6 7

CPU就总共花费了80*1=80分钟，11/80=13%,负载很低了

 DB TIME= 所有前台session花费在database调用上的总和时间。不包括Oracle后台进程消耗的时间。如果DB Time远远小于Elapsed时间，说明数据库比较空闲。但还要注意的是不同的系统，不同的时间段系统繁忙的程度不同，不然AWR报告分析结果没有意义。

补充：AVERAGE ACTIVE SESSION平均活跃会话

AVERAGE ACTIVE SESSION=db time/elapsed time，AAS 接近1说明此采样时间段数据库负载一般，ASS小于1说明此采样时间段数据负载小，AAS远远大于1 说明此采样时间段数据库负载高，最上面的awr报告中，11/78<1，代表负载很小

elapsed time：自然流失时间，等于两个快照之间的时间

 

DB time = DB CPU time + all of nonidle wait event time+WAIT ON CPU QUEUE TIME（不包含空闲等待）
   说白了就是db time就是记录的服务器花在数据库运算(非后台进程)和等待(非空闲等待)上的时间

举例：

  如果仅有2个逻辑CPU，而2个session在60分钟都没等待事件，一直跑在CPU上，那么：

  DB CPU= 2 * 60 mins， DB Time = 2* 60 + 0 + 0 =120，   AAS = 120/60=2  正好等于OS load 2。

  如果有3个session都100%仅消耗CPU，那么总有一个要wait on queue

  DB CPU = 2* 60 mins  ，wait on CPU queue= 60 mins

  AAS= (120+ 60)/60=3 è 主机load 亦为3，此时vmstat 看waiting for run time

  真实世界中？

   DB Cpu = xx mins，Non-Idle Wait= enq:TX + cursor pin S on X + latch : xxx + db file sequential read + ……

注意：DB TIME不等于响应时间，DB TIME高了未必响应慢，DB TIME低了未必响应快,DB Time描绘了数据库总体负载，但要和elapsed time逝去时间结合其他来。

 

结合：Time Model Statistics  来理解

Total time in database user-calls(DB Time): 663s

Statistics including the word "background" measure background process time, and so do not contribute to the DB time statistic

Ordered by % or DB time desc, Statistic namex

Statistic Name

Time (s)

% of DB Time

DB CPU

514.50

77.61

sql execute elapsed time

482.27

72.74

parse time elapsed

3.76

0.57

PL/SQL execution elapsed time

0.50

0.08

hard parse elapsed time

0.34

0.05

connection management call elapsed time

0.08

0.01

hard parse (sharing criteria) elapsed time

0.00

0.00

repeated bind elapsed time

0.00

0.00

PL/SQL compilation elapsed time

0.00

0.00

failed parse elapsed time

0.00

0.00

DB time

662.97

 

background elapsed time

185.19

 

background cpu time

67.48

 

此节显示了各种类型的数据库处理任务所占用的CPU时间。

 

补充：时间模型

至于DB对CPU的利用情况，这就涉及到10g新引入的一个关于时间统计的视图V$SYS_TIME_MODEL，

简单而言，Oracle采用了一个统一的时间模型对一些重要的时间指标进行了记录，具体而言，这些指标包括：

1) Background elapsed time

    2) Background CPU time

                    3) RMAN CPU time (backup/restore)

1) DB time

    2) DB CPU

    2) Connection management call elapsed time

    2) Sequence load elapsed time

    2) SQL execute elapsed time

    2) Parse time elapsed

                    3) Hard parse elapsed time

                                4) Hard parse (sharing criteria) elapsed time

                                       5) Hard parse (bind mismatch) elapsed time

                    3) Failed parse elapsed time

                                4) Failed parse (out of shared memory) elapsed time

    2) PL/SQL execution elapsed time

    2) Inbound PL/SQL RPC elapsed time

    2) PL/SQL compilation elapsed time

    2) Java execution elapsed time

    2) Repeated bind elapsed time

这里我们关注的只有和CPU相关的两个： Background CPU time 和 DB CPU。

 

ReportSummary

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改变它们之后），可用来与初始参数值比较。内存管理方式：MSMM、ASMM(sga_target)、AMM(memory_target) ,小内存有小内存的问题， 大内存有大内存的麻烦！小内存有时候报ORA-04031 4030。大内存会存在页管理、page table..等问题.

shared pool主要包括library cache和dictionary cache。library cache用来存储最近解析（或编译）后SQL、PL/SQL和Java classes等。library cache用来存储最近引用的数据字典。发生在library cache或dictionary cache的cache miss代价要比发生在buffer cache的代价高得多。因此shared pool的设置要确保最近使用的数据都能被cache。

Buffer cache和shared pool size的 begin/end值在ASMM、AMM和11gR2 MSMM下可是会动的哦！

这里说 shared pool一直收缩，则在shrink过程中一些row cache对象被lock住可能导致前台row cache lock等解析等待，最好别让shared pool shrink。如果这里shared pool一直在grow，那说明shared pool原有大小不足以满足需求(可能是大量硬解析)，结合下文的解析信息和SGA breakdown来一起诊断问题。

这里硬解析超级大，Hard parses大于每秒100、全部parses超过每秒300表明可能有争用问题

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

 

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

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

 

Redo size：每秒产生的日志大小(单位字节)，可标志数据变更频率, 大的redo size往往对lgwr写日志，和arch归档造成I/O压力， 也有可能造成log buffer 堵塞（注意，这里不是进程慢，而是由于i/o导致）从而产生相关的等待事件。很繁忙的系统中日志生成量可能达到几百k，甚至几M。如果等待事件中有LOG方面的等待事件，说明REDO生成的频率太高。等待就要考虑增加log buffer。如果真的出现LOG BUFFER小了，大家记住算法，redo size * 600 > LOG BUFFER，10分钟内生成的REDO大于LOG BUFFER。这里的918,805.72*600/1024/1024=525M>23M,已经很大了，所以需要提高LGWR的效率了，那么这里还需要结合TOP 5 TIMED EVENTS处理。但后面的TOP 5没有IO等待，那么这里没有问题。

Per Transaction可以用来分辨是 大量小事务， 还是少量大事务。如上例每秒redo 约900多K，每个事务7k，符合OLTP特征（很多小事务）.

补充：logbuffer 理论上不超过32m，增大不益，等于浪费。

 

Logical reads：每秒/每事务逻辑读的块数.平决每秒产生的逻辑读的block数。Logical Reads= Consistent Gets + DB Block Gets 当前读+一致性读 这里的逻辑读为3521*8K=28000K,  大量OLTP系统(例如siebel)可以高达几十乃至上百Gbytes。逻辑读耗CPU，主频和CPU核数都很重要，逻辑读高则DB CPU往往高，也往往可以看到latch: cache buffer chains

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

Physical reads：每秒/每事务物理读的块数，物理读消耗IO读，体现在IOPS和吞吐量等不同纬度上；但减少物理读可能意味着消耗更多CPU。好的存储 每秒物理读能力达到几GB，例如Exadata

Physical writes：每秒/每事务物理写的块数。主要是DBWR写datafile，也有direct path write。 dbwr长期写出慢会导致定期log file switch(checkpoint no complete) 检查点无法完成的前台等待。

User calls：每秒/每事务用户call次数，通常执行一次需要多次用户调用，最优的情况是用户调用跟执行数接近，但只是指导，没有特别的意义。比如：一个存储过程中出现调用一次，执行两次的情况。差太多，有可能是存储过程多。

Parses:包括软解析+硬解析，不包括软软解析（fast parse），软解析优化得不好，则夸张地说几乎等于每秒SQL执行次数。 即执行解析比1:1，而我们希望的是解析一次 到处运行哦！

SQL解析的次数.每秒解析次数，软解析每秒超过300次意味着你的"应用程序"效率不高,因为要重复的打开私有CURSOR。要实现软软解析，优化程序设计需要调整session_cursor_cache。在这里，fast parse指的是直接在PGA中命中的情况（设置了session_cached_cursors=n）；soft parse是指在shared pool中命中的情形；hard parse则是指都不命中的情况。

Hard parses:Cursor pin s on X， library cache: mutex X ， latch: row cache objects /shared pool……………..。 硬解析最好少于每秒20次

  每秒产生的硬解析次数, 每秒超过100次，就可能说明你绑定使用的不好，也可能是共享池设置不合理。这时候可以启用参数cursor_sharing=similar|force，该参数默认值为exact。但该参数设置为similar时，存在bug，可能导致执行计划的不优

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

Logons：每秒/每事务登录的次数，如果系统应用采用长连接，这里基本没有问题。如果采用大量短连接，这里的登录次数非常高，会产生登录风暴（短时间内大量用户尝试登录）。 weblogic等中间件分配的连接就是长连接  

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

Transactions：每秒事务数，简称TPS，可以看做压力测试或比对性能是的一个指标，孤立看无意义。 还有一个QPS(query per sec)

每秒的事务数，这是一个OLTP数据库事务规模的重要指标。通常为个位，十位的单位,大的事务数据库，可能有TPS到百，千

 

% Blocks changed per Read:

51.62

Recursive Call %:

51.72

Rollback per transaction %:

85.49

Rows per Sort:

########

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

Recursive Call：递归调用占所有操作的比率，如果有很多PL/SQL，那么这个值就会比较高。

Rollback per transaction：每事务的回滚率

每事务的回滚率.看回滚率是不是很高，因为回滚很耗资源 ,如果回滚率过高,可能说明你的数据库经历了太多的无效操作 ,过多的回滚可能还会带来Undo Block的竞争 该参数计算公式如下: Round(User rollbacks / (user commits + user rollbacks) ,4)* 100% 。但是这个指标不太精确，参考而已，

Rows per Sort：每次排序的行数

 

Oracle的硬解析和软解析

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

　　1、语法检查(syntax check)

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

　　2、语义检查(semantic check)

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

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

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

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

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

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

　　假设存在，则将此sql与cache中的进行比较；

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

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

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

 

硬解析案例：

硬解析数和  hard parse elapsed time对应， 看一眼Time Model Statistics，即可知该系统是否 是 解析敏感的，然后根据TOP5 进一步观察。

 

 

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 Ratio，Library Hit ratio也称Library Cache Hit ratio。同Load Profile一节相同，这一节也没有所谓“正确”的值，而只能根据应用的特点判断是否合适。在一个使用直接读执行大型并行查询的DSS环境，20%的Buffer Hit Ratio是可以接受的，而这个值对于一个OLTP系统是完全不能接受的。根据Oracle的经验，对于OLTPT系统，Buffer Hit Ratio理想应该在90%以上。

 

Buffer Nowait :表示在内存获得数据的未等待比例。在缓冲区中获取Buffer的未等待比率。Buffer Nowait的这个值一般需要大于99%。否则可能存在争用，可以在后面的等待事件中进一步确认。

 

buffer hit:表示进程从内存中找到数据块的比率，监视这个值是否发生重大变化比这个值本身更重要。对于一般的OLTP系统，如果此值低于80%，应该给数据库分配更多的内存。数据块在数据缓冲区中的命中率，通常应在95%以上。否则，小于95%，需要调整重要的参数，小于90%可能是要加db_cache_size。一个高的命中率，不一定代表这个系统的性能是最优的，比如大量的非选择性的索引被频繁访问，就会造成命中率很高的假相（大量的db file sequential read），但是一个比较低的命中率，一般就会对这个系统的性能产生影响，需要调整。命中率的突变，往往是一个不好的信息。如果命中率突然增大，可以检查top buffer get SQL，查看导致大量逻辑读的语句和索引，如果命中率突然减小，可以检查top physical reads SQL，检查产生大量物理读的语句，主要是那些没有使用索引或者索引被删除的。

在OLAP主要是IO操作，低的命中率影响不大，因为大的数据访问需要BUFFER块的回收。

注意有时不合理的使用大的索引，会频繁命中索引块，造成很高的缓冲区命中率。

有时候指标会出现负数，BUFFER 命中率变负数是由于buffer cache太小经常被换出，而又经常被重新读入。没有命中，而且没命中的BUFFER还经常的被挤掉，就会产生负数

例子：100个 A  一致性读块，没一个人去要 A块，没有1个命中，而且还有55个 A 块还被挤掉了，那命中率是多少？-45%

 

Redo NoWait:表示在LOG缓冲区获得BUFFER的未等待比例。如果太低（可参考90%阀值），考虑增加LOG BUFFER。当redo buffer达到1M时，就需要写到redo log文件，所以一般当redo buffer设置超过1M，不太可能存在等待buffer空间分配的情况。当前，一般设置为2M的redo buffer，对于内存总量来说，应该不是一个太大的值。

 

library hit:表示Oracle从Library Cache中检索到一个解析过的SQL或PL/SQL语句的比率，当应用程序调用SQL或存储过程时，Oracle检查Library Cache确定是否存在解析过的版本，如果存在，Oracle立即执行语句；如果不存在，Oracle解析此语句，并在Library Cache中为它分配共享SQL区。低的library hit ratio会导致过多的解析，增加CPU消耗，降低性能。如果library hit ratio低于90%，可能需要调大shared pool区。STATEMENT在共享区的命中率，通常应该保持在95%以上，否则需要要考虑：加大共享池；使用绑定变量；修改cursor_sharing等参数。

 

Latch Hit：Latch是一种保护内存结构的锁，可以认为是SERVER进程获取访问内存数据结构的许可。要确保Latch Hit>99%，否则意味着Shared Pool latch争用，可能由于未共享的SQL，或者Library Cache太小，可使用绑定变更或调大Shared Pool解决。要确保>99%，否则存在严重的性能问题。当该值出现问题的时候，我们可以借助后面的等待时间和latch分析来查找解决问题。

 

Parse CPU to Parse Elapsd：（CPU解析时间和CPU等待时间比）解析实际运行时间/(解析实际运行时间+解析中等待资源时间)，越高越好。如果该比率为100%，意味着CPU等待时间为0，没有任何等待。

 

Non-Parse CPU ：SQL实际运行时间/(SQL实际运行时间+SQL解析时间)，太低表示解析消耗时间过多。计算公式为：% Non-Parse CPU =round(100*1-PARSE_CPU/TOT_CPU),2)。如果这个值比较小，表示解析消耗的CPU时间过多。与PARSE_CPU相比，如果TOT_CPU很高，这个比值将接近100%，这是很好的，说明计算机执行的大部分工作是执行查询的工作，而不是分析查询的工作。

 

Execute to Parse：是语句执行与分析的比例，如果要SQL重用率高，则这个比例会很高。该值越高表示一次解析后被重复执行的次数越多。计算公式为：Execute to Parse =100 * (1 - Parses/Executions)。本例中，差不多每execution 5次需要一次parse。所以如果系统Parses > Executions，就可能出现该比率小于0的情况，语句只分析不执行。该值<0通常说明shared pool设置或者语句效率存在问题，造成反复解析，reparse可能较严重,或者是可能同snapshot有关，通常说明数据库性能存在问题。

 

In-memory Sort：在内存中排序的比率，如果过低说明有大量的排序在临时表空间中进行。考虑调大PGA。如果低于95%，可以通过适当调大初始化参数PGA_AGGREGATE_TARGET或者SORT_AREA_SIZE来解决，注意这两个参数设置作用的范围时不同的，SORT_AREA_SIZE是针对每个session设置的，PGA_AGGREGATE_TARGET则时针对所有的sesion的。

 

Soft Parse：软解析的百分比（softs/softs+hards），近似当作sql在共享区的命中率，太低则需要调整应用使用绑定变量。sql在共享区的命中率，小于<95%,需要考虑绑定，如果低于80%，那么就可以认为sql基本没有被重用

  虽然软解析比硬解析效率高，但是过度的软解析，会对LATCH有些影响，因为毕竟不是软软解析，如果你发现LATCH，特别是LIBRARY CACHE LATCH很高，而软解析率虽然很高，比如60%以上，说明你软解析率可能还是过度了

 

补充：软解析，需要通过HASH定位到LIBRARY CACHE链，需要LIBRARY CACHE PIN，以及要获取LIBRARY CACHE 链的LATCH，如果多个会话同时软解析同一条SQL，要获取同一个LIBRARY CACHE 链的LATCH，就有冲突了。

 

Shared Pool Statistics （这个不是整个共享池而言，而是针对SQL占用共享池而言）

 

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语句被再次执行，这将使得SQL语句被硬解析。在一个大小合适的系统中，共享池的使用率将处于75%到略低于90%的范围内.

SQL with executions>1：执行次数大于1的sql比率，如果此值太小，说明需要在应用中更多使用绑定变量，避免过多SQL解析，或者是因为SHARED POOL太小，被挤出去了，所以可以用这个参数值来判断执行计划是不是经常被挤出。

在一个趋向于循环运行的系统中，必须认真考虑这个数字。在这个循环系统中，在一天中相对于另一部分时间的部分时间里执行了一组不同的SQL语句。在共享池中，在观察期间将有一组未被执行过的SQL语句，这仅仅是因为要执行它们的语句在观察期间没有运行。只有系统连续运行相同的SQL语句组，这个数字才会接近100%。

如果该环节中% SQL with executions>1的 比例 小于%90 ， 考虑用下面链接的SQL去抓 硬编码的非绑定变量SQL语句。

http://www.askmaclean.com/archives/%E5%88%A9%E7%94%A8force_matching_signature%E6%8D%95%E8%8E%B7%E9%9D%9E%E7%BB%91%E5%AE%9A%E5%8F%98%E9%87%8Fsql.html

 

Memory for SQL w/exec>1：执行次数大于1的SQL消耗内存的占比。这是与不频繁使用的SQL语句相比，频繁使用的SQL语句消耗内存多少的一个度量。这个数字将在总体上与% SQL with executions>1非常接近，除非有某些查询任务消耗的内存没有规律。在稳定状态下，总体上会看见随着时间的推移大约有75%～85%的共享池被使用。如果Statspack报表的时间窗口足够大到覆盖所有的周期，执行次数大于一次的SQL语句的百分率应该接近于100%。这是一个受观察之间持续时间影响的统计数字。可以期望它随观察之间的时间长度增大而增大。

ASMM中shared pool的大小会产生变化，稳定的系统此项指标应该维持在75-90%左右

 

小结：通过ORACLE的实例有效性统计数据，我们可以获得大概的一个整体印象，然而我们并不能由此来确定数据运行的性能。当前性能问题的确定，我们主要还是依靠下面的等待事件来确认。我们可以这样理解两部分的内容，hit统计帮助我们发现和预测一些系统将要产生的性能问题，由此我们可以做到未雨绸缪。而wait事件，就是表明当前数据库已经出现了性能问题需要解决，所以是亡羊补牢的性质。

 

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

基于Wait Interface的调优是目前的主流！每个指标都重要！ 基于命中比例的调优，好比是统计局的报告，张财主家财产100万，李木匠家财

产1万， 平均财产50.5万。

基于等待事件的调优，好比马路上100辆汽车的行驶记录表，上车用了几分钟， 红灯等了几分钟，拥堵塞了几分钟。。。

 

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

但DB CPU/CPU time是Top 1 是好事情吗？  未必！ 注意DB CPU不包含 wait on cpu queue！

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

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

 

更多的等待事件，参见本报告 的Wait Events一节。

 

评估io的经验

  计算下平均等待时间：总等待时间/等待次数 IO相关的事件的平均等待时间如果大于20msec毫秒，那IO可能需要优化

    1 秒=1000 毫秒(ms) 1 秒=1,000,000 微秒(μs)  47/27319=0.0085  8.5毫秒  后面平均为9毫秒，IO没有问题。

 

补充：LGWR太慢，我们就需要看是不是LOG FILE 写的操作平均写超过20毫秒（这里没有），如果超过了，那么就需要优化磁盘介质

Cause Identified: Logwriter is writing too slow

If the size of the log buffer is already large (more than 3 MB), speed up the LGWR background process write operations by ensuring that the I/O devices where the redolog files are stored are not suffering from I/O contention.

Cause Justification

AWR / Statspack:

                  l log buffer space waits

             l The average time for log file parallel write is MORE than20mSec

 

 

TOP 5 timed events 要结合Host CPU、Instance CPU、 SQL ordered by CPU Time一起看哦！

 

 

    Wait Class 等待事件的类型

s - second

cs - centisecond - 100th of a second

ms - millisecond - 1000th of a second

us - microsecond - 1000000th of a second

ordered by wait time desc, waits desc

查询Oracle 10gR1提供的12个等待事件类：

select wait_class#, wait_class_id, wait_class from v$event_name group by wait_class#, wait_class_id, wait_class order by wait_class#;

Wait Class

Waits

%Time -outs

Total Wait Time (s)

Avg wait (ms)

Waits /txn

User I/O

66,837

0.00

120

2

11.94

System I/O

28,295

0.00

93

3

5.05

Network

1,571,450

0.00

66

0

280.72

Cluster

210,548

0.00

29

0

37.61

Other

81,783

71.82

28

0

14.61

Application

333,155

0.00

16

0

59.51

Concurrency

5,182

0.04

5

1

0.93

Commit

919

0.00

4

4

0.16

Configuration

25,427

99.46

1

0

4.54

 

Wait Events 现实非空闲等待事件后面是空闲等待事件

s - second

cs - centisecond - 100th of a second

ms - millisecond - 1000th of a second

us - microsecond - 1000000th of a second

ordered by wait time desc, waits desc (idle events last)

 

（1）查询所有等待事件及其属性：

select event#, name, parameter1, parameter2, parameter3 from v$event_name order by name;

 

（2）查询Oracle 10gR1提供的12个等待事件类：

select wait_class#, wait_class_id, wait_class from v$event_name group by wait_class#, wait_class_id, wait_class order by wait_class#;

 

wait_event.doc

下面显示的内容可能来自下面几个视图）

V$EVENT_NAME视图包含所有为数据库实例定义的等待事件。

V$SYSTEM_EVENT视图显示自从实例启动后，所有Oracle会话遇到的所有等待事件的总计统计。

V$SESSION_EVENT视图包含当前连接到实例的所有会话的总计等待事件统计。该视图包含了V$SYSTEM_EVENT视图中出现的所有列。它记录会话中每一个等待事件的总等待次数、已等待时间和最大等待时间。SID列标识出独立的会话。每个会话中每个事件的最大等待时间在MAX_WAIT列中追踪。通过用SID列将V$SESSION_EVENT视图和V$SESSION视图结合起来，可得到有关会话和用户的更多信息。

V$SESSION_WAIT视图提供关于每个会话正在等待的事件或资源的详细信息。该视图在任何给定时间，只包含每个会话的一行活动的或不活动的信息。

自从OWI在Oracle 7.0.12中引入后，就具有下来4个V$视图：

V$EVENT_NAME

V$SESSION_WAIT

V$SESSION_EVENT

V$SYSTEM_EVENT

除了这些等待事件视图之外，Oracle 10gR1中引入了下列新视图以从多个角度显示等待信息：

V$SYSTEM_WAIT_CLASS

V$SESSION_WAIT_CLASS

V$SESSION_WAIT_HISTORY

V$EVENT_HISTOGRAM

V$ACTIVE_SESSION_HISTORY

然而，V$SESSION_WAIT、V$SESSION_WAIT和V$SESSION_WAIT仍然是3个重要的视图，它们提供了不同粒度级的等待事件统计和计时信息。三者的关系如下：

V$SESSION_WAIT Ì V$SESSION_EVENT ÌV$SYSTEM_EVENT

 

Event

Waits

%Time -outs

Total Wait Time (s)

Avg wait (ms)

Waits /txn

SQL*Net more data from client

27,319

0.00

64

2

4.88

log file parallel write

5,497

0.00

47

9

0.98

db file sequential read

7,900

0.00

35

4

1.41

db file parallel write

4,806

0.00

34

7

0.86

db file scattered read

10,310

0.00

31

3

1.84

direct path write

42,724

0.00

30

1

7.63

.......

........

...........

.........

.......

......

  

 

 

 

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

本节按各种资源分别列出对资源消耗最严重的SQL语句，并显示它们所占统计期内全部资源的比例，这给出我们调优指南。例如在一个系统中，CPU资源是系统性能瓶颈所在，那么优化buffer gets最多的SQL语句将获得最大效果。在一个I/O等待是最严重事件的系统中，调优的目标应该是physical IOs最多的SQL语句。

在STATSPACK报告中，没有完整的SQL语句，可使用报告中的Hash Value通过下面语句从数据库中查到：

select sql_text

from stats$sqltext

where hash_value = &hash_value

order by piece;

 

 

Segments by Row Lock Waits

当一个进程予在正被其它进程锁住的数据行上获得排它锁时发生这种等待。这种等待经常是由于在一个有主键索引的表上做大量INSERT操作。

 

Operating System Statistics

Statistic

Total

NUM_LCPUS

0

NUM_VCPUS

0

AVG_BUSY_TIME

101,442

AVG_IDLE_TIME

371,241

AVG_IOWAIT_TIME

5,460

AVG_SYS_TIME

25,795

AVG_USER_TIME

75,510

BUSY_TIME

812,644

IDLE_TIME

2,971,077

IOWAIT_TIME

44,794

SYS_TIME

207,429

USER_TIME

605,215

LOAD

0

OS_CPU_WAIT_TIME

854,100

RSRC_MGR_CPU_WAIT_TIME

0

PHYSICAL_MEMORY_BYTES

8,589,934,592

NUM_CPUS

8

NUM_CPU_CORES

4

NUM_LCPUS：                         如果显示0，是因为没有设置LPARS

NUM_VCPUS：                           同上。

AVG_BUSY_TIME：                   BUSY_TIME / NUM_CPUS

AVG_IDLE_TIME：           IDLE_TIME / NUM_CPUS

AVG_IOWAIT_TIME：               IOWAIT_TIME / NUM_CPUS

AVG_SYS_TIME：                      SYS_TIME / NUM_CPUS

AVG_USER_TIME：          USER_TIME / NUM_CPUSar o

BUSY_TIME：                              time equiv of %usr+%sys in sar output

IDLE_TIME：                                time equiv of %idle in sar

IOWAIT_TIME：                          time equiv of %wio in sar

SYS_TIME：                                 time equiv of %sys in sar

USER_TIME：                              time equiv of %usr in sar

LOAD：                                         未知

OS_CPU_WAIT_TIME：    supposedly time waiting on run queues

RSRC_MGR_CPU_WAIT_TIME：      time waited coz of resource manager

PHYSICAL_MEMORY_BYTES：       total memory in use supposedly

NUM_CPUS：                     number of CPUs reported by OS 操作系统CPU数

NUM_CPU_CORES：                  number of CPU sockets on motherboard 主板上CPU插槽数

总的elapsed time也可以用以公式计算：

BUSY_TIME + IDLE_TIME + IOWAIT TIME

或：SYS_TIME + USER_TIME + IDLE_TIME + IOWAIT_TIME

 （因为BUSY_TIME = SYS_TIME+USER_TIME）

 

 

W/A MB processed

W/A MB processed :单位MB W/A workarea  workarea中处理的数据数量  结合 In-memory Sort%， sorts (disk) PGA Aggr一起看 

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

 

 

 

总结：性能优化的多维度理论

• 增加了cpuè更大的并发量，更多的并发争用

 

• 调整了Io存储è  更少的IO，更多的CPU计算，更高的cpu使用率

 

• Redo写得慢è影响commit，造成enq:tx和gc buffer busy等待等

 

• Datafile写得慢è检查点完不成，日志无法切换，前台DML hang

 

• Sequence nocacheè INSERT index很容易造成enq:index contention，和row cache lock和 enq:SQ

 

• 通过数据库手段优化了性能è 应用本身设计的瓶颈越来越凸显