Statspack学习(四).Report分析
Statspack的Report文档其实是将Oracle系统的各项系统个指标分项统计成各个报表，之后放入到Report文档中，所以可以根据自己的需要截取其中的一部分进行分析。里面的数据非常之多，要一次分析完全是不太现实的。
下面截取一下常见的学习一下：
1、报表头信息
报表头显示了数据库实例的相关信息，包括数据库名称、ID、版本号、主机等信息；以及起止snap的信息等：
STATSPACK report for
Database DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
486494833 dodo 1 21-2月 -09 16:2 10.2.0.1.0 NO
7
Host Name: HHZ-0099 Num CPUs: 2 Phys Memory (MB): 2,046
~~~~
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- -------------------
Begin Snap: 1 21-2月 -09 20:27:36 18 3.4
End Snap: 4 21-2月 -09 21:05:50 19 3.1
Elapsed: 38.23 (mins)
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 404M Std Block Size: 8K
Shared Pool Size: 164M Log Buffer: 6,860K
2、负载间档
该部分提供每秒和每个事物的统计信息，是监控系统吞吐量和负载变化的重要部分。
Load Profile Per Second Per Transaction
~~~~~~~~~~~~ --------------- ---------------
Redo size: 1,888.85 22,805.41
Logical reads: 51.72 624.40
Block changes: 5.49 66.27
Physical reads: 0.01 0.13
Physical writes: 1.52 18.33
User calls: 0.51 6.13
Parses: 2.21 26.72
Hard parses: 0.25 3.00
Sorts: 0.79 9.59
Logons: 0.03 0.39
Executes: 4.12 49.78
Transactions: 0.08
% Blocks changed per Read: 10.61 Recursive Call %: 97.58
Rollback per transaction %: 0.00 Rows per Sort: 51.29
说明：
⑴ Redo size：每秒产生的日志大小(单位字节)，可标志数据变更频率, 数据库任务的繁重与否
⑵ Logical reads：平决每秒产生的逻辑读，单位是block
⑶ Block changes：每秒block变化数量，数据库事物带来改变的块数量
⑷ Physical reads：平均每秒数据库从磁盘读取的block数
⑸ Physical writes：平均每秒数据库写磁盘的block数
⑹ User calls：每秒用户call次数
⑺ Parses: 每秒解析次数，近似反应每秒语句的执行次数, 软解析每秒超过300次意味着你的“应用程序”效率不高，调整session_cursor_cache
⑻ Hard parses：每秒产生的硬解析次数, 每秒超过100次，就可能说明你绑定使用的不好
⑼ Sorts：每秒产生的排序次数
⑽ Logons：每秒的登陆次数
⑾ Executes：每秒的执行次数
⑿ Transactions：每秒产生的事务数，反映数据库任务繁重与否
⒀ Recursive Call %: 如果有很多PLSQL，那么他就会比较高
⒁ Rollback per transaction %：看回滚率是不是很高，因为回滚很耗资源
注：回滚率=Round(User rollbacks / (user commits + user rollbacks) ,4)* 100% ，
如果回滚率过高，可能说明你的数据库经历了太多的无效操作，
过多的回滚可能还会带来Undo Block的竞争。
3、实例命中率
该部分可以提前找出ORACLE潜在将要发生的性能问题，很重要
Instance Efficiency Percentages
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.99 Redo NoWait %: 100.00
Buffer Hit %: 99.98 In-memory Sort %: 100.00
Library Hit %: 94.65 Soft Parse %: 88.77
Execute to Parse %: 46.34 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 97.83 % Non-Parse CPU: 95.37
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 60.34 71.99
% SQL with executions>1: 63.66 66.00
% Memory for SQL w/exec>1: 82.19 73.37
说明:
⑴ Buffer Nowait %：在缓冲区中获取Buffer的未等待比率, Buffer Nowait<99%说明，有可能是有热块(查找x$bh的tch和v$latch_children的cache buffers chains)
⑵ Redo NoWait %：在Redo缓冲区获取Buffer的未等待比率
⑶ Buffer Hit %：数据块在数据缓冲区中得命中率，通常应在90%以上，否则需要调整，小于95%，重要的参数,小于90%可能是要加db_cache_size，但是大量的非选择的索引也会造成该值很高（大量的db file sequential read）
⑷ In-memory Sort %：在内存中的排序率
⑸ Library Hit %：主要代表sql在共享区的命中率，通常在95%以上，否则需要要考虑加大共享池，绑定变量，修改cursor_sharing等参数。
⑹ Soft Parse %：近似看作sql在共享区的命中率，小于95%需要考虑到绑定，如果低于80%，那么就可能sql基本没有被重用
⑺ Execute to Parse %：sql语句解析后被重复执行的次数，如果过低，可以考虑设置session_cached_cursors参数, 公式为100 * (1 - Parses/Executions) = Execute to Parse 所以如果系统Parses > Executions,就可能出现该比率小于0的情况, 该值<0通常说明shared pool设置或效率存在问题造成反复解析，reparse可能较严重,或者可是同snapshot有关如果该值为负值或者极低,通常说明数据库性能存在问题。
⑻ Latch Hit %: 要确保>99%，否则存在严重的性能问题，比如绑定等会影响该参数
⑼
Parse CPU to Parse Elapsd %：解析实际运行事件/(解析实际运行时间+解析中等待资源时间)越高越好
⑽ % Non-Parse CPU：查询实际运行时间/(查询实际运行时间+sql解析时间)，太低表示解析消耗时间过多。100*（parse time cpu / parse time elapsed）= Parse CPU to Parse Elapsd %
注：
如果一个经常访问的列上的索引被删除，可能会造成Buffer Hit显著的下降
如果增加了索引，但是他影响了Oracle正确的选择表连接时的驱动顺序，那么可能会导致Buffer Hit显著增高
如果你的命中率变化幅度很大，说明你要改变SQL模式
Shared Pool相关统计数据
⑴ Memory Usage %：共享池内存使用率，应该稳定在70%-90%间，太小浪费内存，太大则内存不足。
⑵ % SQL with executions>1：执行次数大于1的sql比率，若太小可能是没有使用bind variables。
⑶ % Memory for SQL w/exec>1：也即是memory for sql with execution>1:执行次数大于1的sql消耗内存/所有sql消耗的内存
4、首要等待事件(Top 5)
Oracle等待事件是衡量Oracle运行状况的重要依据及指示，主要有空闲等待事件和非空闲等待事件。
TIMED_STATISTICS = TRUE 等待事件按等待的时间排序
TIMED_STATISTICS = FALSE 等待事件按等待的数量排序
Statspack中必须在Session上设置TIMED_STATISTICS = TRUE
空闲等待事件是Oracle正等待某种工作，在诊断和优化数据库时候，不用过多注意这部分事件，非空闲等待事件专门针对Oracle的活动，指数据库任务或应用程序运行过程中发生的等待，这些等待事件是我们在调整数据库应该关注的。
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
db file parallel write 1,575 20 13 53.8
CPU time 10 26.0
control file sequential read 2,001 3 2 8.7
control file parallel write 805 3 4 7.7
log file parallel write 676 1 1 1.8
-------------------------------------------------------------
比较影响性能常见等待事件有：
① db file scattered read
该事件通常与全表扫描有关。因为全表扫描是被放入内存中进行的，通常情况下它不可能被放入连续的缓冲区中，所以就散布在缓冲区的缓存中。该指数的数量过大说明缺少索引或者限制了索引的使用(也可以调整optimizer_index_cost_adj) 。这种情况也可能是正常的，因为执行全表扫描可能比索引扫描效率更高。当系统存在这些等待时，需要通过检查来确定全表扫描是否必需的来调整。如果经常必须进行全表扫描，而且表比较小, 把该表存人keep池。如果是大表经常进行全表扫描,那么应该是OLAP系统，而不是OLTP的.
② db file sequential read
该事件说明在单个数据块上大量等待，该值过高通常是由于表间连接顺序很糟糕，或者使用了非选择性索引。通过将这种等待与statspack报表中已知其它问题联系起来(如效率不高的sql),通过检查确保索引扫描是必须的，并确保多表连接的连接顺序来调整, DB_CACHE_SIZE可以决定该事件出现的频率
③ buffer busy wait
当缓冲区以一种非共享方式或者如正在被读入到缓冲时，就会出现该等待。该值不应该大于1%，确认是不是由于热点块造成(如果是可以用反转索引,或者用更小块大小)
④ latch free
常跟应用没有很好的应用绑定有关。闩锁是底层的队列机制(更加准确的名称应该是互斥机制)，用于保护系统全局区(SGA)共享内存结构闩锁用于防止对内存结构的并行访问。如果闩锁不可用，就会记录一次闩锁丢失。绝大多数得闩锁问题都与使用绑定变量失败(库缓存闩锁)、生成重作问题(重执行分配闩锁)、缓存的争用问题(缓存LRU链) 以及缓存的热数据宽块(缓存链)有关。当闩锁丢失率高于0.5%时，需要调整这个问题。
⑤ log buffer space
日志缓冲区写的速度快于LGWR写REDOFILE的速度,可以增大日志文件大小，增加日志缓冲区的大小，或者使用更快的磁盘来写数据。
⑥ logfile switch
通常是因为归档速度不够快，需要增大重做日志
⑦ log file sync
当一个用户提交或回滚数据时，LGWR将会话得重做操作从日志缓冲区填充到日志文件中，用户的进程必须等待这个填充工作完成。在每次提交时都出现，如果这个等待事件影响到数据库性能，那么就需要修改应用程序的提交频率, 为减少这个等待事件，须一次提交更多记录，或者将重做日志REDO LOG 文件访在不同的物理磁盘上。
更加具体的说明可以参见eygle大师的《Statspack-v3.0》
5、实例的CPU及内存信息
Host CPU (CPUs: 2)
~~~~~~~~ Load Average
Begin End User System Idle WIO WCPU
------- ------- ------- ------- ------- ------- --------
1.62 1.99 96.39
Instance CPU
~~~~~~~~~~~~
% of total CPU for Instance: 0.27
% of busy CPU for Instance: 7.54
%DB time waiting for CPU - Resource Mgr:
Memory Statistics Begin End
~~~~~~~~~~~~~~~~~ ------------ ------------
Host Mem (MB): 2,046.4 2,046.4
SGA use (MB): 584.0 584.0
PGA use (MB): 40.3 40.5
% Host Mem used for SGA+PGA: 30.5 30.5
-------------------------------------------------------------
Time Model System Stats DB/Inst: DODO/dodo Snaps: 1-4
-> Ordered by % of DB time desc, Statistic name
Statistic Time (s) % of DB time
----------------------------------- -------------------- ------------
sql execute elapsed time 9.4 100.9
DB CPU 8.2 87.5
PL/SQL execution elapsed time 2.3 24.2
parse time elapsed 1.7 17.8
hard parse elapsed time 1.5 15.5
PL/SQL compilation elapsed time 0.1 1.6
failed parse elapsed time 0.0 .3
hard parse (sharing criteria) elaps 0.0 .2
connection management call elapsed 0.0 .1
repeated bind elapsed time 0.0 .1
hard parse (bind mismatch) elapsed 0.0 .0
sequence load elapsed time 0.0 .0
DB time 9.4
background elapsed time 32.4
background cpu time 4.3
-------------------------------------------------------------
6、数据库用户程序发生的所有等待事件
Wait Events DB/Inst: DODO/dodo Snaps: 1-4
-> s - second, cs - centisecond, ms - millisecond, us - microsecond
-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
--------------------------------- ------------ ------ ---------- ------ --------
db file parallel write 1,575 0 20 13 8.3
control file sequential read 2,001 0 3 2 10.5
control file parallel write 805 0 3 4 4.2
log file parallel write 676 0 1 1 3.6
os thread startup 35 0 0 10 0.2
db file sequential read 21 0 0 13 0.1
log file sync 43 0 0 2 0.2
direct path write 35 0 0 2 0.2
rdbms ipc reply 28 0 0 0 0.1
SQL*Net more data to client 45 0 0 0 0.2
Streams AQ: qmn slave idle wait 82 0 2,297 28012 0.4
Streams AQ: qmn coordinator idle 163 52 2,297 14092 0.9
virtual circuit status 76 100 2,274 29921 0.4
jobq slave wait 712 99 2,127 2988 3.7
Streams AQ: waiting for time mana 1 100 1,109 ###### 0.0
SQL*Net message from client 682 0 745 1092 3.6
class slave wait 2 100 10 4999 0.0
SQL*Net message to client 683 0 0 0 3.6
-------------------------------------------------------------
7、数据库后台进程发生的等待事件
Background Wait Events DB/Inst: DODO/dodo Snaps: 1-4
-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
--------------------------------- ------------ ------ ---------- ------ --------
db file parallel write 1,575 0 20 13 8.3
control file parallel write 805 0 3 4 4.2
control file sequential read 729 0 2 3 3.8
log file parallel write 677 0 1 1 3.6
os thread startup 35 0 0 10 0.2
direct path write 5 0 0 13 0.0
db file sequential read 5 0 0 10 0.0
events in waitclass Other 54 0 0 0 0.3
rdbms ipc message 8,090 92 23,167 2864 42.6
Streams AQ: qmn slave idle wait 82 0 2,297 28012 0.4
Streams AQ: qmn coordinator idle 163 52 2,297 14092 0.9
pmon timer 789 100 2,292 2906 4.2
smon timer 14 43 2,267 ###### 0.1
Streams AQ: waiting for time mana 1 100 1,109 ###### 0.0
-------------------------------------------------------------
8、等待事件柱状图
Wait Event Histogram DB/Inst: DODO/dodo Snaps: 1-4
-> Total Waits - units: K is 1000, M is 1000000, G is 1000000000
-> % of Waits - column heading: <=1s is truly <1024ms, >1s is truly >=1024ms
-> % of Waits - value: .0 indicates value was <.05%, null is truly 0
-> Ordered by Event (idle events last)
Total ----------------- % of Waits ------------------
Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
LGWR wait for redo copy 17 100.0
SQL*Net break/reset to cli 2 100.0
SQL*Net more data to clien 45 100.0
buffer busy waits 5 100.0
control file parallel writ 805 22.1 69.6 .5 1.0 1.5 2.6 2.7
control file sequential re 2001 91.9 .4 .6 3.1 1.8 .8 1.3
db file parallel write 1575 47.3 3.5 8.1 6.4 12.8 7.3 14.6
db file scattered read 1 100.0
db file sequential read 21 23.8 19.0 33.3 9.5 14.3
direct path read 5 100.0
direct path write 35 97.1 2.9
latch free 5 100.0
latch: library cache 2 100.0
latch: shared pool 1 100.0
log file parallel write 676 96.2 .7 .6 .3 .4 1.0 .7
log file sync 43 86.0 2.3 2.3 2.3 4.7 2.3
os thread startup 35 5.7 91.4 2.9
rdbms ipc reply 28 100.0
undo segment extension 3 100.0
SQL*Net message from clien 682 67.9 7.6 14.1 5.1 2.1 .9 1.0 1.3
SQL*Net message to client 683 100.0
SQL*Net more data from cli 61 100.0
Streams AQ: qmn coordinato 163 48.5 51.5
Streams AQ: qmn slave idle 82 100.0
Streams AQ: waiting for ti 1 100.0
class slave wait 2 100.0
dispatcher timer 38 100.0
jobq slave wait 712 .3 99.7
pmon timer 789 1.1 .1 .3 98.5
rdbms ipc message 8083 5.2 .6 .4 .6 .5 .1 29.3 63.2
smon timer 13 7.7 7.7 7.7 15.4 61.5
virtual circuit status 76 100.0
-------------------------------------------------------------
9、TOP SQL
调整首要的25个缓冲区读操作和首要的25个磁盘读操作做的查询，将可对系统性能产生5%到5000%的增益。
内容太多不再截取...
10、实例活动
Instance Activity Stats DB/Inst: DODO/dodo Snaps: 1-4
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
redo synch time 11 0.0 0.1
redo synch writes 614 0.3 3.2
redo wastage 174,084 75.9 916.2
redo write time 61 0.0 0.3
redo writer latching time 2 0.0 0.0
redo writes 677 0.3 3.6
rollback changes - undo records a 10 0.0 0.1
rollbacks only - consistent read 8 0.0 0.0
rows fetched via callback 14,999 6.5 78.9
session connect time 2,312 1.0 12.2
session cursor cache hits 3,352 1.5 17.6
session logical reads 118,636 51.7 624.4
session pga memory 34,734,200 15,141.3 182,811.6
session pga memory max 32,964,728 14,370.0 173,498.6
session uga memory 158,915,954,392 69,274,609.6 ############
session uga memory max 44,277,484 19,301.4 233,039.4
shared hash latch upgrades - no w 33,011 14.4 173.7
sorts (memory) 1,823 0.8 9.6
sorts (rows) 93,505 40.8 492.1
sql area purged 2 0.0 0.0
switch current to new buffer 2 0.0 0.0
table fetch by rowid 23,047 10.1 121.3
table fetch continued row 61 0.0 0.3
table scan blocks gotten 9,066 4.0 47.7
table scan rows gotten 400,817 174.7 2,109.6
table scans (short tables) 760 0.3 4.0
total number of times SMON posted 8 0.0 0.0
transaction rollbacks 5 0.0 0.0
undo change vector size 1,329,952 579.8 6,999.8
user I/O wait time 34 0.0 0.2
user calls 1,165 0.5 6.1
user commits 190 0.1 1.0
workarea executions - optimal 884 0.4 4.7
write clones created in backgroun 0 0.0 0.0
-------------------------------------------------------------
11、IO情况统计
Tablespace IO Stats DB/Inst: DODO/dodo Snaps: 1-4
->ordered by IOs (Reads + Writes) desc
File IO Stats DB/Inst: DODO/dodo Snaps: 1-4
->Mx Rd Bkt: Max bucket time for single block read
->ordered by Tablespace, File
File Read Histogram Stats DB/Inst: DODO/dodo Snaps: 1-4
->Number of single block reads in each time range
->ordered by Tablespace, File
12、缓冲区
Buffer Pool Statistics DB/Inst: DODO/dodo Snaps: 1-4
-> Standard block size Pools D: default, K: keep, R: recycle
-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
-> Buffers: the number of buffers. Units of K, M, G are divided by 1000
Instance Recovery Stats DB/Inst: DODO/dodo Snaps: 1-4
-> B: Begin snapshot, E: End snapshot
Buffer Pool Advisory DB/Inst: DODO/dodo End Snap: 4
-> Only rows with estimated physical reads >0 are displayed
-> ordered by Pool, Block Size, Buffers For Estimate
Buffer wait Statistics DB/Inst: DODO/dodo Snaps: 1-4
-> ordered by wait time desc, waits desc
13、PGA
PGA Aggr Target Stats DB/Inst: DODO/dodo Snaps: 1-4
-> B: Begin snap E: End snap (rows identified with B or E contain data
which is absolute i.e. not diffed over the interval)
-> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory
-> Auto PGA Target - actual workarea memory target
-> W/A PGA Used - amount of memory used for all Workareas (manual + auto)
-> %PGA W/A Mem - percentage of PGA memory allocated to workareas
-> %Auto W/A Mem - percentage of workarea memory controlled by Auto Mem Mgmt
-> %Man W/A Mem - percentage of workarea memory under manual control
14、进程该要
Process Memory Summary Stats DB/Inst: DODO/dodo Snaps: 1-4
-> B: Begin snap E: End snap
-> All rows below contain absolute values (i.e. not diffed over the interval)
-> Max Alloc is Maximum PGA Allocation size at snapshot time
Hist Max Alloc is the Historical Max Allocation for still-connected processes
-> Num Procs or Allocs: For Begin/End snapshot lines, it is the number of
processes. For Category lines, it is the number of allocations
-> ordered by Begin/End snapshot, Alloc (MB) desc
15、回滚段
Undo Segment Summary DB/Inst: DODO/dodo Snaps: 1-4
-> Min/Max TR (mins) - Min and Max Tuned Retention (minutes)
-> STO - Snapshot Too Old count, OOS - Out Of Space count
-> Undo segment block stats:
uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed
eS - expired Stolen, eR - expired Released, eU - expired reUsed
16、锁
...