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个逻辑CPU(4个物理CPU),平均每个CPU耗时1.4分钟,CPU利用率只有大约2%(1.4/79)。说明系统压力非常小。
可是对于批量系统,数据库的工作负载总是集中在一段时间内。如果快照周期不在这一段时间内,或者快照周期跨度太长而包含了大量的数据库空闲时间,所得出的分析结果是没有意义的。这也说明选择分析时间段很关键,要选择能够代表性能问题的时间段。
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 cache和dictionary cache。library cache用来存储最近解析(或编译)后SQL、PL/SQL和Java classes等。library cache用来存储最近引用的数据字典。发生在library cache或dictionary cache的cache 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次数
Parses:SQL解析的次数
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),就不能不说一下Oracle对sql的处理过程。当你发出一条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算法来取得该sql的hash值,然后在library cache里查找是否存在该hash值;
假设存在,则将此sql与cache中的进行比较;
假设“相同”,就将利用已有的解析树与执行计划,而省略了优化器的相关工作。这也就是软解析的过程。
诚然,如果上面的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 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 hit表示进程从内存中找到数据块的比率,监视这个值是否发生重大变化比这个值本身更重要。对于一般的OLTP系统,如果此值低于80%,应该给数据库分配更多的内存。
Redo NoWait表示在LOG缓冲区获得BUFFER的未等待比例。如果太低(可参考90%阀值),考虑增加LOG 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区。
Latch Hit:Latch是一种保护内存结构的锁,可以认为是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在共享区的命中率,太低则需要调整应用使用绑定变量。
参数说明:
Buffer Nowait %:在缓冲区中获取Buffer的未等待比率,Buffer Nowait<99%说明,有可能是有热块(查找x$bh的 tch和v$latch_children的cache buffers chains)。
Redo NoWait %:在Redo缓冲区获取Buffer的未等待比率。
Buffer Hit %:数据块在数据缓冲区中的命中率,通常应在950%以上, 如果小于95%,需要调整重要的参数,小于95%可能是要加db_cache_size,但是大量的非选择的索引也会造成该值很高(大量的db file sequential read)。如果一个经常访问的列上的索引被删除或者是统计信息太陈旧,可能会导致错误的执行计划,造成buffer hit 显著下降。如果增加了索引,但是它影响了ORACLE正确的选择表连接时的驱动顺序,那么可能会导致buffer hit 显著增高。如果命中率变化幅度很大,说明需要改变SQL模式。
In-memory Sort %:在内存中的排序率。
Library Hit %:主要代表sql在共享区的命中率,通常在95%以上,否则需要要考虑加大共享池,绑定变量,修改cursor_sharing等参数。
Soft Parse %:近似看作sql在共享区的命中率,小于<95%,需要考虑到绑定,如果低于80%,那么就可能sql基本没有被重用。
Execute to Parse %:一个语句执行和分析了多少次的度量。在一个分析,然后执行语句,且再也不在同一个会话中执行它的系统中,这个比值为0。
计算公式为:Execute to Parse =100 * (1 - Parses/Executions)。所以如果系统Parses > Executions,就可能出现该比率小于0的情况。该值<0通常说明shared pool设置或效率存在问题,造成反复解析,reparse可能较严重,或者可是同snapshot有关,如果该值为负值或者极低,通常说明数据库性能存在问题。
Latch Hit %:要确保>99%,否则存在严重的性能问题,比如绑定等会影响该参数。
Parse CPU to Parse Elapsd %:计算公式为:Parse CPU to Parse Elapsd %= 100*(parse time cpu / parse time elapsed)。即:解析实际运行时间/(解析实际运行时间+解析中等待资源时间),如果非常低,用于解析花费的每个CPU秒花费了大量的wall clock时间,这说明花了很多时间等待一个资源。如果该比率为100%,意味着CPU时间等于经过的时间,没有任何等待。
% Non-Parse CPU:计算公式为:% Non-Parse CPU =round(100*1-PARSE_CPU/TOT_CPU),2)。太低表示解析消耗时间过多。与PARSE_CPU相比,如果TOT_CPU很高,这个比值将接近100%,这是很好的,说明计算机执行的大部分工作是执行查询的工作,而不是分析查询的工作。
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:执行次数大于1的sql比率,如果此值太小,说明需要在应用中更多使用绑定变量,避免过多SQL解析。
Memory for SQL w/exec>1:执行次数大于1的SQL消耗内存的占比。
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 Wait和File/Tablespace IO区的内容,识别哪些文件导致了问题。如果最严重的等待事件是I/O事件,我们应当研究按物理读排序的SQL语句区以识别哪些语句在执行大量I/O,并研究Tablespace和I/O区观察较慢响应时间的文件。如果有较高的LATCH等待,就需要察看详细的LATCH统计识别哪些LATCH产生的问题。
在这里,log file parallel write是相对比较多的等待,占用了7%的CPU时间。
通常,在没有问题的数据库中,CPU time总是列在第一个。
看一下系统统计部分:
从这部分信息可以看出我们系统的CPU情况与内存等信息。
SYS_TIME与USER_TIME之和 1776 +4834 = 6610 正好等于BUSY_TIME,再者(6610 + 719387)/100/60/2 = 60.49975 ,这不是我们的采样时间吗?2代表两个cpu(NUM_CPUS)。上面提到DB CPU不包括后台进程花费的时间(比如PMON),而在Time Model Statistics中显示出了后台进程CPU时间(backgroundcpu time)。所以数据库的整体 cpu 使用情况可以这样计算吧,cpu使用率 = ( DB CPU + background cpu time ) / (Elapsed *NUM_CPUS) * 100% = ( 19.45 + 7.08 ) / ( 60.45mins * 2 )* 100% = 0.00366 % 。看来我们的系统很闲呀,我应该在系统稍微忙点的时候取一份report来分析。
V$OSSTAT
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2010.htm#REFRN30321
Awr report 分析-IO stats
性能指标说明:
Reads: 发生了多少次物理读。
Av Reads/s : 每秒钟物理读的次数。
Av Rd(ms): 平均一次物理读的时间(毫秒),有一种说法是如果这个值大于7说明系统有严重的I/O问题,也有人说这个值不应该超过30,否则IO也可能有问题。硬件不同对IO瓶颈的判断也会相应的改变。
Av Blks/Rd:每次读多少个数据块。
Writes:发生了多少次写。
Av writes/s:每秒写的次数。
Buffer Waits:获取内存数据块等待的次数。
Av Buf Wt(ms):获取内存数据块平均等待时间。
V$FILESTAT
http://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_1107.htm#REFRN30087
IOPS (Input/Output Operations Per Second),即每秒进行读写(I/O)操作的次数。
MBPS也就是带宽,每秒传输的比特(数据量),一个字节8比特(bit)。比如:4Mbps=每秒钟传输4M比特。
IOPS计算:
IOPS = Av Reads/s + Av Writes/s
MBPS计算:
MBPS=(Physical reads +Physical writes)* block_size =(0.12 + 0.28 )*块大小
MBPS的的指标在 Load Profile 或 Instance ActivityStatistics 部分分别对应Physical reads 与Physical writes 或 physical writes 与 physicalreads。
相关描述说明:
Statistics Descriptions
http://docs.oracle.com/cd/B19306_01/server.102/b14237/stats002.htm#i375475
同样一个系统,我们可以设置一个基线作为依据,也可以判断出IO性能是否出现了问题,比如,在系统运行性能良好的时候做一个基线,当系统IO性能很差时,我们可以取一份report,然后对比两个report的性能指标,这样就可以准确诊断当前IO对性能的影响。
Awr report 分析-oracle 内存组件大小
在awr report中显示了oracle对各个内存组件大小的性能估算,包括Buffer Pool Advisory,PGA Memory Advisory,Shared Pool Advisory,SGATarget Advisory。
先看一下Report Summary里这方面的信息:
指标说明:
Memory Usage %:表示共享池内存使用率,应保持在75到90之间,如果太小说明分配的共享池过大,如果>90说明共享池中有争用,共享池内存不足。
% SQL with executions>1:执行次数大于1的SQL语句的比率,太小的话要结合Parse,看看是不是有硬编码现象,避免过多的sql硬解析。
% Memory for SQL w/exec>1:执行次数大于1的SQL语句所消耗的内存,占所有SQL语句消耗内存的比率。
指标说明:
Size for Est (M):oracle 估算buffer pool的大小。
Size Factor:估算值与实际值的一个比例,比如0.9,代表估算值是实际值大小的90%,1.0代表buffer pool的实际大小。
Buffers for Estimate:估算的buffer pool的大小(数量)。
Est Phys Read Factor:估算的物理读的影响因子,是估算物理读和实际物理读的一个比例,1.0代表实际的物理读。
Estimated Physical Reads:估算的物理读的次数。
可以看出系统的实际bufferpool的大小是380M(Size Factor=1.0)。我们应该找到SizeFactor的改变对物理读影响最大的点,即Size Factor=0.66的点。从Size Factor=0.57的物理读次数391999降到Size Factor=0.66的物理读次数414222,以后物理读的变化很小,或基本没有变化。即buffer pool为252M时,效率是最高的。
指标说明:
PGA Target Est (MB):估算PGA 的大小。
Size Factr:影响因子。
W/A MB Processed:oracle为了产生估算处理的数据量。
Estd Extra W/A MB Read/ Written to Disk:处理数据中需要物理读写的数据量。
Estd PGA Cache Hit %:估算的PAG命中率。
Estd PGA Overalloc Count:需要在估算的PGA大小下额外分配内存的次数。
这份数据没有任何代表性!这里需要掌握两个点:1)oracle估算的PGA大小不会导致额外分配内存的点。2)物理读写值(Estd Extra W/A MB Read/ Written to Disk)不再增加的点。不过还要考虑当前内存是否充足。
指标说明:
Shared Pool Size(M):估算的共享池大小。
SP Size Factr:影响因子。
Est LC Size (M) :估算的库高速缓存占用的大小。
Est LC Mem Obj:高速缓存中的对象数。
Est LC Time Saved (s) :需要额外将对象读入共享池的时间。
Est LC Time Saved Factr:额外将对象读入共享池的时间影响因子。
Est LC Load Time (s) :分析所花费的时间。
Est LC Load Time Factr:分析花费事件的影响因子。
Est LC Mem Obj Hits:内存中对象被发现的次数。
这里主要看Est LC Time Saved Factr这一列,它表示将对象读入共享池的影响情况,当这个值变化很小或不变的时候,增加shared pool的值就没有太大意义了。这样看来,这里应该将shared pool 设置为80M。共享池设置大了,这正好验证了Shared Pool Statistics中Memory Usage %列说明。
指标说明:
SGA Target Size (M):估算的SGA大小。
SGA Size Factor:影响因子。
Est DB Time (s):估算的SGA大小计算出的DB TIME。
Est Physical Reads:估算的物理读次数。
同样找到影响最大的点,即Size Factor=0.75的点。
Awr report 分析-其它
OLTP系统中必须关注的两个性能指标是LibraryHit与Buffer Hit。Library Hit指共享池中sql解析的命中率; Buffer Hit指内存数据块命中率。
关于这两项性能指标可以查看:
oracle SharedPool优化思路 http://www.linuxidc.com/Linux/2011-07/38912.htm
oracle Buffer Cache优化思路 http://www.linuxidc.com/Linux/2011-11/48052.htm
SQL ordered by Elapsed Time
image
这部分以sql消耗时间降序排列。
指标说明:
Elapsed Time (s):该条sql消耗的总时间。
CPU Time (s):该条sql花费的总cpu时间。
Executions:该条sql执行次数。
Elap per Exec (s):该条sql执行一次消耗的平均时间。
% Total DB Time:该条sql消耗总时间(ElapsedTime)占数据库时间(DB Time)的百分比。
SQL ordered by CPU Time
% Total DB Time is the Elapsed Time of theSQL statement divided into the Total Database Time multiplied by 100
这部分以sql消耗的cpu时间降序排列。
指标说明:略
SQL ordered by Gets
image
这部分以sql获取内存数据块的数量降序排列。
指标说明:
Buffer Gets:sql获取内存数据块总数量。
Executions:sql执行次数。
Gets per Exec:sql执行一次获取内存数据块数量。
%Total:占内存数据块的百分比。
CPU Time (s) :sql花费的总cpu时间。
Elapsed Time (s):sql消耗的总时间。
SQL ordered by Reads
这部分以sql物理读降序排列。
SQL ordered by Executions
这部分列出sql执行次数信息。
指标说明:
Rows Processed:sql处理的总记录数。
Rows per Exec CPU per Exec (s):sql每次执行处理的记录数。
OLTP可以关注,OLAP无须关注(因为sql重复执行的频率很低)。
SQL ordered by Parse Calls
这部分列出sql被分析次数(不区分硬分析与软分析)的信息。
指标说明:
Parse Calls:sql分析的次数。
% Total Parses:占总分析次数的百分比。
SQL ordered by Sharable Memory
这部分列出sql占用library cache大小的信息。
指标说明:
Sharable Mem (b):占用librarycache的大小。
SQL ordered by Version Count
这部分列出sql版本数信息。
指标说明:
Version Count:sql版本数。
OLTP可以关注。
SQL ordered by Cluster Wait Time
这部分列出集群等待时间信息。
指标说明:
Cluster Wait Time(s):集群等待时长。
CWT% of Elapsd Time:集群操作等待时长占总时长(ElapsdTime(s))的百分比。