详细解读 STATSPACK 报告

BTxigua

在进入正文以前，我想说明一点。
之前已经有不少这样的关于statspack的说明文章，中问，英文的都有，当然这中间很多我都是参考了的，希望不要来追讨我的版权问题。

这篇文章是以我自己的一个statspack报告作为格式样本来解释报告的中各个部分内容的，但是我并没有真正去分析我自己的报告。
所以不要根据我的报告来提我的系统的改进意见。

对于我的解释，希望大家都提改正的意见。

文档已经基本写完，我已经整理成word格式文档，作好了目录。
大家可随意下载修改。不过大家发现错误的时候能同时告知我们，发扬共享精神，谢谢。

word文档附件可以在第7页下载。


详细解读 STATSPACK 报告

详细解读 STATSPACK 报告 1
1、报表头信息 2
2、实例负载档信息 2
3、实例有效性信息 3
4、TOP 5及其他等待事件信息 5
5、SQL统计信息 10
5.1 SQL统计信息－逻辑读 11
5.2 SQL统计信息－物理读 11
5.3 SQL统计信息－执行次数 12
5.4 SQL统计信息－调用、解析次数 12
5.5 SQL统计信息－共享内存占用 13
5.6 SQL统计信息－多版本缓存 13
6、实例的活动信息 14
7、I/O统计信息 18
8、Buffer Pool统计信息 20
9、实例的恢复情况统计信息 21
10、Buffer Pool调整的Advisory 21
11、Buffer Pool等待情况统计 22
12、PGA统计信息 22
13、PGA调整的Advisory 23
14、队列的统计信息 24
15、回滚段统计信息 24
16、闩锁统计信息 26
17、共享池统计信息 31
18、SGA内存分配 32
19、资源限制统计信息 33
20、初始化统计信息 34

- Kamus
将rar文件加到首贴了 2009-01-07

[ 本帖最后由 Kamus 于 2009-1-7 19:40 编辑 ]

BTxigua

说在前面，很容易被忽略的几个点：在读报告的时候，我们首先需要看清楚，留意3个内容，这份报告所对应的数据库版本，cluster方式，以及报告的时间段。尤其需要注意的就是时间段，脱离了时间段的statspck将是毫无意义的，甚至会得出错误的结果。

STATSPACK report for
/* 报表头信息，数据库实例相关信息，包括数据库名称、ID、版本号及主机明等信息
DB Name         DB Id    Instance     Inst Num Release     Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
ORA92         1924035339 ora92               1 9.2.0.6.0   NO      jsdxh_db02

              Snap Id     Snap Time      Sessions Curs/Sess Comment
            --------- ------------------ -------- --------- -------------------
Begin Snap:        13 14-Jul-07 00:18:52      274  55,345.0
  End Snap:        14 14-Jul-07 00:32:55      272  55,823.8
   Elapsed:               14.05 (mins)

Cache Sizes (end)
~~~~~~~~~~~~~~~~~
               Buffer Cache:     5,120M      Std Block Size:          8K
           Shared Pool Size:       400M          Log Buffer:      2,048K

Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:            422,086.46              4,706.23
              Logical reads:             23,200.54                258.68
              Block changes:              3,080.59                 34.35
             Physical reads:                 31.46                  0.35
            Physical writes:                104.38                  1.16
                 User calls:                409.32                  4.56
                     Parses:                227.20                  2.53
                Hard parses:                  7.22                  0.08
                      Sorts:                213.87                  2.38
                     Logons:                  0.85                  0.01
                   Executes:              1,191.32                 13.28
               Transactions:                 89.69

/* 下面详细说明Load Profile各项含义
1)        Redo size:每秒产生的日志大小(单位字节)，可标志数据变更频率, 数据库任务的繁重与否。
2)        Logical reads:平决每秒产生的逻辑读的block数。Logical Reads= Consistent Gets + DB Block Gets
3)        Block changes:每秒block变化数量，数据库事物带来改变的块数量。
4)        Physical reads:平均每秒数据库从磁盘读取的block数。
5)        Physical writes:平均每秒数据库写磁盘的block数。
6)        User calls:每秒用户调用次数。
7)        Parses:每秒解析次数，包括fast parse，soft parse和hard parse三种数量的综合。 软解析每秒超过300次意味着你的"应用程序"效率不高，没有使用绑定变量，调整session_cursor_cache。
8)        Hard parses:每秒产生的硬解析次数, 每秒超过100次，就可能说明你绑定使用的不好，也可能是共享池设置不合理。这时候可以启用参数cursor_sharing=similar|force，该参数默认值为exact。但该参数设置为similar时，存在bug，可能导致执行计划的不优。
9)        Sorts:每秒产生的排序次数。
10)        Logons:每秒登陆的次数。
11)        Executes:每秒执行次数。
12)        Transactions:每秒产生的事务数，反映数据库任务繁重与否。

  % Blocks changed per Read:   13.28    Recursive Call %:     80.21
Rollback per transaction %:    0.03       Rows per Sort:      2.84

/* Load Profile 续
1)        % Blocks changed per Read：在每一次逻辑读中更改的块的百分比。
2)        Rollback per transaction %:看回滚率是不是很高，因为回滚很耗资源 ,如果回滚率过高,可能说明你的数据库经历了太多的无效操作 ,过多的回滚可能还会带来Undo Block的竞争 该参数计算公式如下: Round(User rollbacks / (user commits + user rollbacks) ,4)* 100% 。
3)        Recursive Call %:递归调用的百分比，如果有很多PL/SQL，那么这个值就会比较高。
4)        Rows per Sort:平均每次排序操作的行数。

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:   99.98       Redo NoWait %:    100.00
            Buffer  Hit   %:   99.87    In-memory Sort %:    100.00
            Library Hit   %:   99.67        Soft Parse %:     96.82
         Execute to Parse %:   80.93         Latch Hit %:     96.10
Parse CPU to Parse Elapsd %:    6.93     % Non-Parse CPU:     99.88

/* 实例的有效性，这部分值越接近100越好，分项内容详细说明如下：
1)        Buffer Nowait %：在缓冲区中获取Buffer的未等待比率。Buffer Nowait的这个值一般需要大于99%。否则可能存在争用，可以在后面的等待事件中进一步确认。
2)        Redo NoWait %：在Redo缓冲区获取Buffer空间的未等待比率。当redo buffer达到1M时，就需要写到redo log文件，所以一般当redo buffer设置超过1M，不太可能存在等待buffer空间分配的情况。当前，一般设置为2M的redo buffer，对于内存总量来说，应该不是一个太大的值。
3)        Buffer Hit %：数据块在数据缓冲区中的命中率，通常应在95%以上。否则，小于95%，需要调整重要的参数，小于90%可能是要加db_cache_size。一个高的命中率，不一定代表这个系统的性能是最优的，比如大量的非选择性的索引被频繁访问，就会造成命中率很高的假相（大量的db file sequential read），但是一个比较低的命中率，一般就会对这个系统的性能产生影响，需要调整。命中率的突变，往往是一个不好的信息。如果命中率突然增大，可以检查top buffer get SQL，查看导致大量逻辑读的语句和索引，如果命中率突然减小，可以检查top physical reads SQL，检查产生大量物理读的语句，主要是那些没有使用索引或者索引被删除的。
4)        In-memory Sort %：在内存中的排序率。如果低于95%，可以通过适当调大初始化参数PGA_AGGREGATE_TARGET或者SORT_AREA_SIZE来解决，注意这两个参数设置作用的范围时不同的，SORT_AREA_SIZE是针对每个session设置的，PGA_AGGREGATE_TARGET则时针对所有的sesion的。
5)        Library Hit %：STATEMENT在共享区的命中率，通常应该保持在95%以上，否则需要要考虑：加大共享池；使用绑定变量；修改cursor_sharing等参数。
6)        Soft Parse %：sql在共享区的命中率，小于<95%,需要考虑绑定，如果低于80%，那么就可以认为sql基本没有被重用。
7)        Execute to Parse %：一个语句执行和分析了多少次的度量。计算公式为：Execute to Parse =100 * (1 - Parses/Executions)。本例中，差不多每execution 5次需要一次parse。所以如果系统Parses > Executions，就可能出现该比率小于0的情况。该值<0通常说明shared pool设置或者语句效率存在问题，造成反复解析，reparse可能较严重,或者是可能同snapshot有关，通常说明数据库性能存在问题。
8)        Latch Hit %：要确保>99%，否则存在严重的性能问题。当该值出现问题的时候，我们可以借助后面的等待时间和latch分析来查找解决问题。
9)        Parse CPU to Parse Elapsd %：计算公式为：Parse CPU to Parse Elapsd %= 100*(parse time cpu / parse time elapsed)。即：解析实际运行时间/(解析实际运行时间+解析中等待资源时间)。如果该比率为100%，意味着CPU等待时间为0，没有任何等待。
10)        % Non-Parse CPU：计算公式为：% Non-Parse CPU =round(100*1-PARSE_CPU/TOT_CPU),2)。如果这个值比较小，表示解析消耗的CPU时间过多。与PARSE_CPU相比，如果TOT_CPU很高，这个比值将接近100%，这是很好的，说明计算机执行的大部分工作是执行查询的工作，而不是分析查询的工作。

Shared Pool Statistics        Begin   End
                               ------  ------
             Memory Usage %:   32.87   33.12
    % SQL with executions>1:   80.00   82.69
  % Memory for SQL w/exec>1:   77.62   80.70

1)        Memory Usage %：正在使用的共享池的百分率。这个数字应该长时间稳定在75%～90%。如果这个百分比太低，表明共享池设置过大，带来额外的管理上的负担，从而在某些条件下会导致性能的下降。如果这个百分率太高，会使共享池外部的组件老化，如果SQL语句被再次执行，这将使得SQL语句被硬解析。在一个大小合适的系统中，共享池的使用率将处于75%到略低于90%的范围内。
2)        % SQL with executions>1：这是在共享池中有多少个执行次数大于一次的SQL语句的度量。在一个趋向于循环运行的系统中，必须认真考虑这个数字。在这个循环系统中，在一天中相对于另一部分时间的部分时间里执行了一组不同的SQL语句。在共享池中，在观察期间将有一组未被执行过的SQL语句，这仅仅是因为要执行它们的语句在观察期间没有运行。只有系统连续运行相同的SQL语句组，这个数字才会接近100%。这里显示，在这个共享池中几乎有80%的SQL语句在14分钟的观察窗口中运行次数多于一次。剩下的20%的语句可能已经在那里了--系统只是没有去执行。
3)        % Memory for SQL w/exec>1：这是与不频繁使用的SQL语句相比，频繁使用的SQL语句消耗内存多少的一个度量。这个数字将在总体上与% SQL with executions>1非常接近，除非有某些查询任务消耗的内存没有规律。在稳定状态下，总体上会看见随着时间的推移大约有75%～85%的共享池被使用。如果Statspack报表的时间窗口足够大到覆盖所有的周期，执行次数大于一次的SQL语句的百分率应该接近于100%。这是一个受观察之间持续时间影响的统计数字。可以期望它随观察之间的时间长度增大而增大。

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

fwei_fengwei

太强了，希望楼主整理成pdf格式的文档。

BTxigua

水平有限，肯定中间有不少不当，甚至错误的地方，恳请大家指正。

BTxigua

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time                                                          361    54.14
log file sync                                      74,324         101    15.22
enqueue                                               729          88    13.28
db file sequential read                             7,303          65     9.76
SQL*Net message from dblink                           482          20     3.05

/* oracle等待事件是衡量oracle运行状况的重要依据及指示，等待事件分为两类：空闲等待事件和非空闲等待事件, TIMED_STATISTICS = TRUE 那么等待事件按等待的时间排序，= FALSE那么事件按等待的数量排序。运行statspack期间必须session上设置TIMED_STATISTICS = TRUE，否则统计的数据将失真。空闲等待事件是oracle正等待某种工作，在诊断和优化数据库时候，不用过多注意这部分事件，非空闲等待事件专门针对oracle的活动，指数据库任务或应用程序运行过程中发生的等待，这些等待事件是我们在调整数据库应该关注的。
    对于常见的等待事件，说明如下：
1)        db file scattered read
该事件通常与全表扫描或者fast full index scan有关。因为全表扫描是被放入内存中进行的进行的，通常情况下基于性能的考虑，有时候也可能是分配不到足够长的连续内存空间，所以会将数据块分散(scattered)读入Buffer Cache中。该等待过大可能是缺少索引或者没有合适的索引(可以调整optimizer_index_cost_adj) 。这种情况也可能是正常的，因为执行全表扫描可能比索引扫描效率更高。当系统存在这些等待时，需要通过检查来确定全表扫描是否必需的来调整。因为全表扫描被置于LRU(Least Recently Used，最近最少适用)列表的冷端(cold end)，对于频繁访问的较小的数据表，可以选择把他们Cache 到内存中，以避免反复读取。当这个等待事件比较显著时，可以结合v$session_longops 动态性能视图来进行诊断，该视图中记录了长时间(运行时间超过6 秒的)运行的事物，可能很多是全表扫描操作(不管怎样，这部分信息都是值得我们注意的)。
关于参数OPTIMIZER_INDEX_COST_ADJ＝n：该参数是一个百分比值，缺省值为100，可以理解为FULL SCAN COST/INDEX SCAN COST。当n%* INDEX SCAN COST  小于 FULL SCAN COST时，Oracle会选择使用索引。在具体设置的时候，我们可以根据具体的语句来调整该值。如果我们希望某个statement使用索引，而实际它确走全表扫描，可以对比这两种情况的执行计划不同的COST，从而设置一个更合适的值。

2)        db file sequential read：该事件说明在单个数据块上大量等待，该值过高通常是由于表间连接顺序很糟糕（没有正确选择驱动行源），或者使用了非选择性索引。通过将这种等待与statspack报表中已知其它问题联系起来(如效率不高的sql),通过检查确保索引扫描是必须的，并确保多表连接的连接顺序来调整。

carcase

lz继续  讲的很好
虽然我大部分都看过，但没有这么系统
lz的笔记很不错哦  
支持lz

shengang34

很精彩！

BTxigua

发现一个问题，在这里不能用数学符号小于，否则后面的内容显示不出来了。

fwei_fengw

最初由 BTxigua 发布
[B]发现一个问题，在这里不能用数学符号小于，否则后面的内容显示不出来了。 [/B]

你可以用标签PHP包含起来，但还是有一些符号不支持，强烈建议楼主上传pdf格式的文档。

infobilling

支持一下，楼主好人啊
传个word文档也可以