详细解读 STATSPACK 报告.docx

详细解读 STATSPACK 报告.docx

《详细解读 STATSPACK 报告.docx》由会员分享，可在线阅读，更多相关《详细解读 STATSPACK 报告.docx（60页珍藏版）》请在冰点文库上搜索。

详细解读STATSPACK报告

详细解读STATSPACK报告

详细解读STATSPACK报告1

1、报表头信息2

2、实例负载档信息3

3、实例有效性信息4

4、TOP5及其他等待事件信息5

5、SQL统计信息11

5.1SQL统计信息－逻辑读12

5.2SQL统计信息－物理读13

5.3SQL统计信息－执行次数13

5.4SQL统计信息－调用、解析次数14

5.5SQL统计信息－共享内存占用14

5.6SQL统计信息－多版本缓存14

6、实例的活动信息16

7、I/O统计信息20

8、BufferPool统计信息22

9、实例的恢复情况统计信息22

10、BufferPool调整的Advisory22

11、BufferPool等待情况统计23

12、PGA统计信息24

13、PGA调整的Advisory25

14、队列的统计信息25

16、闩锁统计信息27

17、共享池统计信息32

18、SGA内存分配34

19、资源限制统计信息35

20、初始化统计信息35

createtablespaceperf_tbsdatafile''size50m;

@?

/rdbms/admin/spcreate.sql

executestatspack.snap

variablejobnonumber;

dbms_job.submit（:

jobno,'ststspack.snap;',

trunc（sysdate+1/48,'MI'）,'trunc（sysdate+1/48,''MI''）'）;

@?

/rdbms/admin/spreport.sql

说在前面，很容易被忽略的几个点：

在读报告的时候，我们首先需要看清楚，留意3个内容，这份报告所对应的数据库版本，cluster方式，以及报告的时间段。

尤其需要注意的就是时间段，脱离了时间段的statspck将是毫无意义的，甚至会得出错误的结果。

YAPP方法：

传统的优化数据库的指标是各种命中率的统计，并以命中率作为优化的目标。

随着系统和业务模式的发展，这种优化的方法已经过时，YAPP方法由此诞生。

YAPP方法的最终目标就是缩短responsetime。

ResponseTime=ServiceTime+WaitTime

ServiceTime=CPUParse+CPURecursive+CPUOther

这里的CPUOther可以理解为是SQL语句的执行时间（包括逻辑读等）。

CPUother=CPUusedbythissession-parsetimecpu-recursivecpuusage

所以优化的最终目标定位在ServiceTime和WaitTime上：

1）当性能问题在ServiceTime上时，由上面的公式可以看到，解决问题的方向就是SQL语句的优化上面。

2）如果性能问题在WaitTime上，则解决的方向就需要找到具体的等待事件了。

如果等待时间在整个响应时间中占较大的比例，并且主要是块读取相关的等待时，下一步就是找出哪些SQL造成了过多的物理读，可以查看statspack报告中的SQLorderedbyReads部分。

对于ServiceTime的问题，可以具体分析其中几种时间的比重：

CPUother=CPUusedbythissession-parsetimecpu-recursivecpuusage

1）如果CPUOther（执行时间）在整个响应时间中占较大的比例，那么下一步就是找出那些造成了最多逻辑IO的SQL语句，可以从statspack报告的SQLorderedbyGets部分找到。

2）如果parsetimecpu（分析时间）在整个响应时间中占较大的比例，那么下一步就是查找哪些SQL分析过多，这在statspack报告中在SQLorderedbyParseCalls中列出。

STATSPACKreportfor

1、报表头信息

/*报表头信息，数据库实例相关信息，包括数据库名称、ID、版本号及主机明等信息。

另外，重点还需要关注一下报告产生的时间跨度（在这里是14分钟），以及并发数（在这里是272）。

DBNameDBIdInstanceInstNumReleaseClusterHost

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

9.2.0.6.0NOjsdxh_db02

SnapIdSnapTimeSessionsCurs/SessComment

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

BeginSnap:

1314-Jul-0700:

18:

5227455,345.0

EndSnap:

1414-Jul-0700:

32:

5527255,823.8

Elapsed:

14.05（mins）

CacheSizes（end）

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

BufferCache:

5,120MStdBlockSize:

8K

SharedPoolSize:

400MLogBuffer:

2,048K

2、实例负载档信息

LoadProfile

~~~~~~~~~~~~PerSecondPerTransaction

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

Redosize:

422,086.464,706.23

Logicalreads:

23,200.54258.68

Blockchanges:

3,080.5934.35

Physicalreads:

31.460.35

Physicalwrites:

104.381.16

Usercalls:

409.324.56

Parses:

227.202.53

Hardparses:

7.220.08

Sorts:

213.872.38

Logons:

0.850.01

Executes:

1,191.3213.28

Transactions:

89.69

/*下面详细说明LoadProfile各项含义

Redosize:

每秒产生的日志大小（单位字节），可标志数据变更频率,数据库任务的繁重与否。

Logicalreads:

平决每秒产生的逻辑读的block数。

LogicalReads=ConsistentGets+DBBlockGets

Blockchanges:

每秒block变化数量，数据库事物带来改变的块数量。

Physicalreads:

平均每秒数据库从磁盘读取的block数。

Physicalwrites:

平均每秒数据库写磁盘的block数。

Physicalreads+Physicalwrites可以近似表征这个系统的IOPS。

Usercalls:

每秒用户调用次数。

Parses:

每秒解析次数，包括softparse和hardparse两种数量的综合。

Hardparses:

每秒产生的硬解析次数，硬解析过高，就可能说明你绑定使用的不好，也可能是共享池设置不合理。

这时候可以启用参数cursor_sharing=similar|force，该参数默认值为exact。

但该参数设置为similar时，存在bug，可能导致执行计划的不优。

Sorts:

每秒产生的排序次数。

Logons:

每秒登陆的次数。

Executes:

每秒执行次数。

如果只看这个参数没有太大的意义，可以结合前面其他的参数一起来考虑。

结合Parses/Executes来考虑，我们就可以知道解析的频繁程度。

Hardparses/Executes就可以看出硬解析的情况是否过度。

Transactions:

每秒产生的事务数，反映数据库任务繁重与否。

%BlockschangedperRead:

13.28RecursiveCall%:

80.21

Rollbackpertransaction%:

0.03RowsperSort:

2.84

/*LoadProfile续

1）%BlockschangedperRead：

在每一次逻辑读中更改的块的百分比。

2）Rollbackpertransaction%:

看回滚率是不是很高，因为回滚很耗资源,如果回滚率过高,可能说明你的数据库经历了太多的无效操作,过多的回滚可能还会带来UndoBlock的竞争该参数计算公式如下:

Round（Userrollbacks/（usercommits+userrollbacks）,4）*100%。

3）RecursiveCall%:

递归调用的百分比，如果有很多PL/SQL，那么这个值就会比较高。

4）RowsperSort:

平均每次排序操作的行数。

3、实例有效性信息

InstanceEfficiencyPercentages（Target100%）

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

BufferNowait%:

99.98RedoNoWait%:

100.00

BufferHit%:

99.87In-memorySort%:

100.00

LibraryHit%:

99.67SoftParse%:

96.82

ExecutetoParse%:

80.93LatchHit%:

96.10

ParseCPUtoParseElapsd%:

6.93%Non-ParseCPU:

99.88

/*实例的有效性，这部分值越接近100越好，分项内容详细说明如下：

1）BufferNowait%：

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

BufferNowait的这个值一般需要大于99%。

否则可能存在争用，可以在后面的等待事件中进一步确认。

2）RedoNoWait%：

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

当redobuffer达到1M时，就需要写到redolog文件，所以一般当redobuffer设置超过1M，不太可能存在等待buffer空间分配的情况。

当前，一般设置为2M的redobuffer，对于内存总量来说，应该不是一个太大的值。

3）BufferHit%：

数据块在数据缓冲区中的命中率，通常应在95%以上。

否则，小于95%，需要调整重要的参数，小于90%可能是要加db_cache_size。

一个高的命中率，不一定代表这个系统的性能是最优的，比如大量的非选择性的索引被频繁访问，就会造成命中率很高的假相（大量的dbfilesequentialread），但是一个比较低的命中率，一般就会对这个系统的性能产生影响，需要调整。

命中率的突变，往往是一个不好的信息。

如果命中率突然增大，可以检查topbuffergetSQL，查看导致大量逻辑读的语句和索引，如果命中率突然减小，可以检查topphysicalreadsSQL，检查产生大量物理读的语句，主要是那些没有使用索引或者索引被删除的。

4）In-memorySort%：

在内存中的排序率。

如果低于95%，可以通过适当调大初始化参数PGA_AGGREGATE_TARGET或者SORT_AREA_SIZE来解决，注意这两个参数设置作用的范围时不同的，SORT_AREA_SIZE是针对每个session设置的，PGA_AGGREGATE_TARGET则时针对所有的sesion的。

5）LibraryHit%：

STATEMENT在共享区的命中率，通常应该保持在95%以上，否则需要要考虑：

加大共享池；使用绑定变量；修改cursor_sharing等参数。

6）SoftParse%：

sql在共享区的命中率，小于<95%,需要考虑绑定，如果低于80%，那么就可以认为sql基本没有被重用。

7）ExecutetoParse%：

一个语句执行和分析了多少次的度量。

计算公式为：

ExecutetoParse=100*（1-Parses/Executions）。

本例中，差不多每execution5次需要一次parse。

所以如果系统Parses>Executions，就可能出现该比率小于0的情况。

该值<0通常说明sharedpool设置或者语句效率存在问题，造成反复解析，reparse可能较严重,或者是可能同snapshot有关，通常说明数据库性能存在问题。

8）LatchHit%：

要确保>99%，否则存在严重的性能问题。

当该值出现问题的时候，我们可以借助后面的等待时间和latch分析来查找解决问题。

9）ParseCPUtoParseElapsd%：

计算公式为：

ParseCPUtoParseElapsd%=100*（parsetimecpu/parsetimeelapsed）。

即：

解析实际运行时间/（解析实际运行时间+解析中等待资源时间）。

如果该比率为100%，意味着CPU等待时间为0，没有任何等待。

10）%Non-ParseCPU：

计算公式为：

%Non-ParseCPU=round（100*1-PARSE_CPU/TOT_CPU）,2）。

如果这个值比较小，表示解析消耗的CPU时间过多。

与PARSE_CPU相比，如果TOT_CPU很高，这个比值将接近100%，这是很好的，说明计算机执行的大部分工作是执行查询的工作，而不是分析查询的工作。

SharedPoolStatisticsBeginEnd

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

MemoryUsage%:

32.8733.12

%SQLwithexecutions>1:

80.0082.69

%MemoryforSQLw/exec>1:

77.6280.70

1）MemoryUsage%：

正在使用的共享池的百分率。

这个数字应该长时间稳定在75%～90%。

如果这个百分比太低，表明共享池设置过大，带来额外的管理上的负担，从而在某些条件下会导致性能的下降。

如果这个百分率太高，会使共享池外部的组件老化，如果SQL语句被再次执行，这将使得SQL语句被硬解析。

在一个大小合适的系统中，共享池的使用率将处于75%到略低于90%的范围内。

2）%SQLwithexecutions>1：

这是在共享池中有多少个执行次数大于一次的SQL语句的度量。

在一个趋向于循环运行的系统中，必须认真考虑这个数字。

在这个循环系统中，在一天中相对于另一部分时间的部分时间里执行了一组不同的SQL语句。

在共享池中，在观察期间将有一组未被执行过的SQL语句，这仅仅是因为要执行它们的语句在观察期间没有运行。

只有系统连续运行相同的SQL语句组，这个数字才会接近100%。

这里显示，在这个共享池中几乎有80%的SQL语句在14分钟的观察窗口中运行次数多于一次。

剩下的20%的语句可能已经在那里了--系统只是没有去执行。

3）%MemoryforSQLw/exec>1：

这是与不频繁使用的SQL语句相比，频繁使用的SQL语句消耗内存多少的一个度量。

这个数字将在总体上与%SQLwithexecutions>1非常接近，除非有某些查询任务消耗的内存没有规律。

在稳定状态下，总体上会看见随着时间的推移大约有75%～85%的共享池被使用。

如果Statspack报表的时间窗口足够大到覆盖所有的周期，执行次数大于一次的SQL语句的百分率应该接近于100%。

这是一个受观察之间持续时间影响的统计数字。

可以期望它随观察之间的时间长度增大而增大。

小结：

通过ORACLE的实例有效性统计数据，我们可以获得大概的一个整体印象，然而我们并不能由此来确定数据运行的性能。

当前性能问题的确定，我们主要还是依靠下面的等待事件来确认。

我们可以这样理解两部分的内容，hit统计帮助我们发现和预测一些系统将要产生的性能问题，由此我们可以做到未雨绸缪。

而wait事件，就是表明当前数据库已经出现了性能问题需要解决，所以是亡羊补牢的性质。

接下来，开始查看wait事件。

4、TOP5及其他等待事件信息

/*oracle等待事件是衡量oracle运行状况的重要依据及指示，等待事件分为两类：

空闲等待事件和非空闲等待事件,TIMED_STATISTICS=TRUE那么等待事件按等待的时间排序，=FALSE那么事件按等待的数量排序。

运行statspack期间必须session上设置TIMED_STATISTICS=TRUE，否则统计的数据将失真。

空闲等待事件是oracle正等待某种工作，在诊断和优化数据库时候，不用过多注意这部分事件，非空闲等待事件专门针对oracle的活动，指数据库任务或应用程序运行过程中发生的等待，这些等待事件是我们在调整数据库应该关注的。

对于常见的等待事件，说明如下：

1）dbfilescatteredread

2）该事件通常与全表扫描或者fastfullindexscan有关。

因为全表扫描是被放入内存中进行的进行的，通常情况下基于性能的考虑，有时候也可能是分配不到足够长的连续内存空间，所以会将数据块分散（scattered）读入BufferCache中。

该等待过大可能是缺少索引或者没有合适的索引（可以调整optimizer_index_cost_adj）。

这种情况也可能是正常的，因为执行全表扫描可能比索引扫描效率更高。

当系统存在这些等待时，需要通过检查来确定全表扫描是否必需的来调整。

因为全表扫描被置于LRU（LeastRecentlyUsed，最近最少适用）列表的冷端（coldend），对于频繁访问的较小的数据表，可以选择把他们Cache到内存中，以避免反复读取。

当这个等待事件比较显著时，可以结合v$session_longops动态性能视图来进行诊断，该视图中记录了长时间（运行时间超过6秒的）运行的事物，可能很多是全表扫描操作（不管怎样，这部分信息都是值得我们注意的）。

3）关于参数OPTIMIZER_INDEX_COST_ADJ＝n：

该参数是一个百分比值，缺省值为100，可以理解为FULLSCANCOST/INDEXSCANCOST。

当n%*INDEXSCANCOST

在具体设置的时候，我们可以根据具体的语句来调整该值。

如果我们希望某个statement使用索引，而实际它确走全表扫描，可以对比这两种情况的执行计划不同的COST，从而设置一个更合适的值。

4）dbfilesequentialread：

该事件说明在单个数据块上大量等待，该值过高通常是由于表间连接顺序很糟糕（没有正确选择驱动行源），或者使用了非选择性索引。

通过将这种等待与statspack报表中已知其它问题联系起来（如效率不高的sql）,通过检查确保索引扫描是必须的，并确保多表连接的连接顺序来调整。

5）bufferbusywait：

当缓冲区以一种非共享方式或者如正在被读入到缓冲时,就会出现该等待。

该值不应该大于1%。

当出现等待问题时，可以检查缓冲等待统计部分（或V$WAITSTAT），确定该等待发生在什么位置：

a）如果等待是否位于段头（SegmentHeader）。

这种情况表明段中的空闲列表（freelist）的块比较少。

可以考虑增加空闲列表（freelist，对于Oracle8iDMT）或者增加freelistgroups（在很多时候这个调整是立竿见影的（altertabletablenamestrorage（freelists2）），在8.1.6PCTUSED与PCTFREE之间距离（PCTUSED-to-pctfreegap），其实就是说降低PCTUSED的值，尽快使块返回freelist列表被重用。

如果支持自动段空间管理（ASSM），也可以使用ASSM模式，这是在ORALCE920以后的版本中新增的特性。

b）如果这一等待位于undoheader，可以通过增加回滚段（rollbacksegment）来解决缓冲区的问题。

c）如果等待位于undoblock上，我们需要增加提交的频率，使block可以尽快被重用；使用更大的回滚段；降低一致读所选择的表中数据的密度；增大DB_CACHE_SIZE。

d）如果等待处于datablock，表明出现了hotblock，可以考虑如下方法解决：

①将频繁并发访问的表或数据移到另一数据块或者进行更大范围的分布（可以增大pctfree值，扩大数据分布，减少竞争），以避开这个"热点"数据块。

②也可以减小数据块的大小，从而减少一个数据块中的数据行数，降低数据块的热度，减小竞争；③检查对这些热块操作的SQL语句，优化语句。

④增加hotblock上的initrans值。

但注意不要把initrans值设置的过于高了，通常设置为5就足够了。

因为增加事务意味着要增加ITL事务槽，而每个ITL事务槽将占用数据块中24个字节长度。

默认情况下，每个数据块或者索引块中是ITL槽是2个，在增加initrans的时候，可以考虑增大数据块所在的表的PCTFREE值，这样Oracle会利用PCTFREE部分的空间增加ITLslot数量，最大达到maxtrans指定。

e）如果等待处于indexblock，应该考虑重建索引、分割索引或使用反向键索引。

为了防止与数据块相关的缓冲忙等待，也可以使用较小的块，在这种情况下，单个块中的记录就较少，所以这个块就不是那么"繁忙"。

或者可以设置更大的PCTFREE,使数据扩大物理分布，减少记录间的热点竞争。

在执行DML（insert/update/delete）时，Oracle向数据块中写入信息，对于多事务并发访问的数据表，关于ITL的竞争和等待可能出现，为了减少这个等待，可以增加initrans，使用多个ITL槽。

在Oracle9i中，可以使用ASSM这个新特性Oracle使用位图来管理空间使用，减小争用。

6）latchfree：

当闩锁丢失率高于0.5%时，需要调整这个问题。

详细的我们在后面的LatchActivityforDB部分说明。

7）Enqueue队列是一种锁，保护一些共享资源，防止并发的DML操作。

队列采用FIFO策略，注意latch并不是采用的FIFO机制。

比较常见的有3种类型的队列：

ST队列，HW队列，TX4队列。

8）STEnqueue的等待主要是在字典管理的表空间中进行空间管理和分配时产生的。

解决方法：

1）将字典管理的表空间改为本地管理模式2）预先分配分区或者将有问题的字典管理的表空间的nextextent设置大一些。

9）HWEnqueue是用于segment的HWM的。

当出现这种等待的时候，可以通过手工分配etents来解决。

10）TX4Enqueue等待是最常见的等待情况。

通常有3种情况会造成这种类型的等待：

1）