statspack,先抄来，慢慢再看

虽然不完整,但看看还是很有好处的,关键是中文滴:em11:

(1) 调整的先后次序

1. Tune the design. -- Application designers

2. Tune the application. -- Application developers

3. Tune memory.

4. Tune I/O.

5. Tune contention.

6. Tune the operating system.



Statspack分析报告详解：

statspack 输出结果中必须查看的十项内容

　　1、负载间档(Load profile)
　　2、实例效率点击率(Instance efficiency hit ratios)
　　3、首要的5个等待事件(Top 5 wait events)
　　4、等待事件(Wait events)
　　5、闩锁等待
　　6、首要的SQL(Top sql)
　　7、实例活动(Instance activity)
　　8、文件I/O(File I/O)
　　9、内存分配(Memory allocation)
　　10、缓冲区等待(Buffer waits



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

STATSPACK report for
DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
BLISSDB 4196236801 blissdb 1 9.2.0.4.0 NO BLISS
Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- --------- -------------------
Begin Snap: 4 23-6月 -05 17:43:32 10 3.3
End Snap: 5 23-6月 -05 18:01:32 12 6.1
Elapsed: 18.00 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 24M Std Block Size: 8K
Shared Pool Size: 48M Log Buffer: 512K

2.负载间档
该部分提供每秒和每个事物的统计信息，是监控系统吞吐量和负载变化的重要部分。
Load Profile
~~~~~~~~~~~~
Per Second Per Transaction
--------------- ---------------
Redo size: 431,200.16 18,627,847.04z
Logical reads: 4,150.76 179,312.72
Block changes: 2,252.52 97,309.00
Physical reads: 23.93 1,033.56
Physical writes: 68.08 2,941.04
User calls: 0.96 41.36
Parses: 1.12 48.44
Hard parses: 0.04 1.92
Sorts: 0.77 33.28
Logons: 0.00 0.20
Executes: 2.36 102.12
Transactions: 0.02

Redo size：每秒产生的重做日志大小(单位字节)，可标志数据变更频率, 数据库任务的繁重与否。本例中平均每秒产生了430K左右的重做，每个事务品均产生了18M的重做。
Logical reads：平次每秒产生的逻辑读，单位是block。
block changes：每秒block变化数量，数据库事物带来改变的块数量。
Physical reads：平均每秒数据库从磁盘读取的block数。
Logical reads和Physical reads比较：大约有0.55%的逻辑读导致了物理I/O，平均每个事务执行了大约18万个逻辑读，在这个例子中，有一些大的事务被执行，因此很高的读取数目是可以接受的。
Physical writes：平均每秒数据库写磁盘的block数。
User calls：每秒用户call次数。
Parses和Hard parses：每秒大约1.12个解析，其中有4%为硬解析，系统每25秒分析一些SQL，都还不错。对于优化好的系统，运行了好几天后，这一列应该达到0，所有的sql在一段时间后都应该在共享池中。
Sorts：每秒产生的排序次数。
Executes：每秒执行次数。
Transactions：每秒产生的事务数，反映数据库任务繁重与否。
% Blocks changed per Read: 54.27 Recursive Call %: 86.94
Rollback per transaction %: 12.00 Rows per Sort: 32.59

% Blocks changed per Read：说明46%的逻辑读是用于那些只读的而不是可修改的块，该系统只更新54%的块。
Rollback per transaction %：事务回滚的百分比。计算公式为：Round(User rollbacks / (user commits + user rollbacks) ,4)* 100%。本例中每 8.33个事务导致一个回滚。如果回滚率过高，可能说明数据库经历了太多的无效操作。过多的回滚可能还会带来Undo Block的竞争。
3.实例命中率
该部分可以提前找出ORACLE潜在将要发生的性能问题，很重要。
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.42 In-memory Sort %: 100.00
Library Hit %: 98.11 Soft Parse %: 96.04
Execute to Parse %: 52.57 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 11.40 % Non-Parse CPU: 99.55
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）。如果一个经常访问的列上的索引被删除，可能会造成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的情况。本例中，对于每个分析来说大约执行了2.1次。该值<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)。即：解析实际运行时间/(解析实际运行时间+解析中等待资源时间)。此处为11.4%，非常低，用于解析花费的每个CPU秒花费了大约8.77秒的wall clock时间，这说明花了很多时间等待一个资源。如果该比率为 100%，意味着CPU时间等于经过的时间，没有任何等待。
% Non-Parse CPU：计算公式为：% Non-Parse CPU =round(100*1- PARSE_CPU/TOT_CPU),2)。太低表示解析消耗时间过多。与PARSE_CPU相比，如果TOT_CPU很高，这个比值将接近100%，这是很好的，说明计算机执行的大部分工作是执行查询的工作，而不是分析查询的工作。
4.Shared Pool相关统计数据
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 60.45 62.42
% SQL with executions>1: 81.38 78.64
% Memory for SQL w/exec>1: 70.36 68.02

Memory Usage %：正在使用的共享池的百分率。这个数字应该长时间稳定在75%～90%。如果这个百分率太低，就浪费内存。如果这个百分率太高，会使共享池外部的组件老化，如果SQL语句被再次执行，这将使得SQL语句被硬解析。在一个大小合适的系统中，共享池的使用率将处于75%到略低于90%的范围内。
% SQL with executions>1：这是在共享池中有多少个执行次数大于一次的SQL语句的度量。在一个趋向于循环运行的系统中，必须认真考虑这个数字。在这个循环系统中，在一天中相对于另一部分时间的部分时间里执行了一组不同的SQL语句。在共享池中，在观察期间将有一组未被执行过的SQL语句，这仅仅是因为要执行它们的语句在观察期间没有运行。只有系统连续运行相同的SQL语句组，这个数字才会接近100%。这里显示，在这个共享池中几乎有80%的SQL语句在18分钟的观察窗口中运行次数多于一次。剩下的20%的语句可能已经在那里了--系统只是没有理由去执行它。
% Memory for SQL w/exec>1：这是与不频繁使用的SQL语句相比，频繁使用的SQL语句消耗内存多少的一个度量。这个数字将在总体上与% SQL with executions>1非常接近，除非有某些查询任务消耗的内存没有规律。
在稳定状态下，总体上会看见随着时间的推移大约有75%～85%的共享池被使用。如果Statspack报表的时间窗口足够大到覆盖所有的周期，执行次数大于一次的SQL语句的百分率应该接近于100%。这是一个受观察之间持续时间影响的统计数字。可以期望它随观察之间的时间长度增大而增大。
5.首要等待事件
常见等待事件说明：
oracle等待事件是衡量oracle运行状况的重要依据及指示，主要有空闲等待事件和非空闲等待事件。
TIMED_STATISTICS：=TRUE，等待事件按等待的时间排序，= FALSE，等待事件按等待的数量排序。
运行statspack期间必须session上设置TIMED_STATISTICS = TRUE。
空闲等待事件是oracle正等待某种工作，在诊断和优化数据库时候，不用过多注意这部分事件，非空闲等待事件专门针对oracle的活动，指数据库任务或应用程序运行过程中发生的等待，这些等待事件是我们在调整数据库应该关注的。
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
db file sequential read 22,154 259 62.14
CPU time 49 11.67
log file parallel write 2,439 26 6.30
db file parallel write 400 22 5.32
SQL*Net message from dblink 4,575 15 3.71
-------------------------------------------------------------

这里是比其他任何事件都能使速度减慢的事件。比较影响性能的常见等待事件：
db file scattered read：该事件通常与全表扫描有关。因为全表扫描是被放入内存中进行的进行的，通常情况下它不可能被放入连续的缓冲区中，所以就散布在缓冲区的缓存中。该指数的数量过大说明缺少索引或者限制了索引的使用(也可以调整 optimizer_index_cost_adj)。这种情况也可能是正常的，因为执行全表扫描可能比索引扫描效率更高。当系统存在这些等待时，需要通过检查来确定全表扫描是否必需的来调整。如果经常必须进行全表扫描，而且表比较小，把该表存人keep池。如果是大表经常进行全表扫描，那么应该是 OLAP系统，而不是OLTP的。
db file sequential read：该事件说明在单个数据块上大量等待，该值过高通常是由于表间连接顺序很糟糕，或者使用了非选择性索引。通过将这种等待与statspack报表中已知其它问题联系起来(如效率不高的sql),通过检查确保索引扫描是必须的，并确保多表连接的连接顺序来调整, DB_CACHE_SIZE可以决定该事件出现的频率。
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文件访在不同的物理磁盘上。
Wait time: 等待时间包括日志缓冲的写入和发送操作。
6.数据库用户程序发生的所有等待事件
Wait Events for DB: BLISSDB Instance: blissdb Snaps: 4 -5
-> 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)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file sequential read 22,154 0 259 12 886.2
log file parallel write 2,439 2,012 26 11 97.6
db file parallel write 400 0 22 55 16.0
SQL*Net message from dblink 4,575 0 15 3 183.0
SQL*Net more data from dblin 64,490 0 13 0 2,579.6
control file parallel write 416 0 5 13 16.6
db file scattered read 456 0 5 11 18.2
write complete waits 9 0 5 568 0.4
control file sequential read 370 0 5 13 14.8
log buffer space 126 0 4 34 5.0
free buffer waits 11 1 3 313 0.4
log file switch completion 13 0 2 188 0.5
log file sync 90 0 1 8 3.6
log file sequential read 10 0 0 16 0.4
latch free 17 6 0 8 0.7
direct path read 56 0 0 1 2.2
direct path write 56 0 0 1 2.2
SQL*Net more data to client 173 0 0 0 6.9
SQL*Net message to dblink 4,575 0 0 0 183.0
LGWR wait for redo copy 8 0 0 1 0.3
log file single write 10 0 0 1 0.4
db file single write 5 0 0 0 0.2
SQL*Net break/reset to clien 5 0 0 0 0.2
async disk IO 15 0 0 0 0.6
SQL*Net message from client 789 0 3,290 4170 31.6
virtual circuit status 36 36 1,082 30069 1.4
wakeup time manager 34 34 1,034 30403 1.4
SQL*Net message to client 791 0 0 0 31.6
SQL*Net more data from clien 30 0 0 0 1.2
-------------------------------------------------------------

7.数据库后台进程发生的等待事件
Background Wait Events for DB: BLISSDB Instance: blissdb Snaps: 4 -5
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
log file parallel write 2,439 2,012 26 11 97.6
db file parallel write 400 0 22 55 16.0
control file parallel write 406 0 5 13 16.2
control file sequential read 258 0 4 16 10.3
db file sequential read 19 0 1 51 0.8
log buffer space 24 0 0 9 1.0
log file sequential read 10 0 0 16 0.4
latch free 14 6 0 9 0.6
db file scattered read 6 0 0 14 0.2
direct path read 56 0 0 1 2.2
direct path write 56 0 0 1 2.2
LGWR wait for redo copy 8 0 0 1 0.3
log file single write 10 0 0 1 0.4
rdbms ipc message 7,339 3,337 3,172 432 293.6
pmon timer 373 373 1,083 2903 14.9
smon timer 3 3 924 ###### 0.1
-------------------------------------------------------------

8.TOP SQL
调整首要的25个缓冲区读操作和首要的25个磁盘读操作做的查询，将可对系统性能产生5%到5000%的增益。
SQL ordered by Gets for DB: BLISSDB Instance: blissdb Snaps: 4 -5
-> End Buffer Gets Threshold: 10000
-> Note that resources reported for PL/SQL includes the resources used by
all SQL statements called within the PL/SQL code. As individual SQL
statements are also reported, it is possible and valid for the summed
total % to exceed 100
CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
1,230,745 1 1,230,745.0 27.5 16.39 60.69 1574310682
Module: PL/SQL Developer
insert into city_day_cal select * from rptuser.city_day_cal@db15
1
143,702 1 143,702.0 3.2 1.75 18.66 3978122706
Module: PL/SQL Developer
insert into city_day_cal select * from rptuser.city_day_cal@db15
1 where curtime between to_date('200501','yyyymm') and to_date('
200502','yyyymm')-1

在报表的这一部分，通过Buffer Gets对SQL语句进行排序，即通过它执行了多少个逻辑I/O来排序。顶端的注释表明一个PL/SQL单元的缓存获得(Buffer Gets)包括被这个代码块执行的所有SQL语句的Buffer Gets。因此将经常在这个列表的顶端看到PL/SQL过程，因为存储过程执行的单独的语句的数目被总计出来。
SQL ordered by Reads for DB: BLISSDB Instance: blissdb Snaps: 4 -5
-> End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
3,587 1 3,587.0 13.9 0.17 5.13 3342983569
Module: PL/SQL Developer
select min(curtime),max(curtime) from city_day_cal
1,575 1 1,575.0 6.1 1.75 18.66 3978122706
Module: PL/SQL Developer
insert into city_day_cal select * from rptuser.city_day_cal@db15
1 where curtime between to_date('200501','yyyymm') and to_date('
200502','yyyymm')-1

这部分通过物理读对SQL语句进行排序。这显示引起大部分对这个系统进行读取活动的SQL，即物理I/O。
SQL ordered by Executions for DB: BLISSDB Instance: blissdb Snaps: 4 -5
-> End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
748 748 1.0 0.00 0.00 3371479671
select t.name, (select owner_instance from sys.aq$_queue_table_
affinities where table_objno = t.objno) from system.aq$_queue
_tables t where t.name = :1 and t.schema = :2 for update skip lo
cked
442 1,142 2.6 0.00 0.00 1749333492
select position#,sequence#,level#,argument,type#,charsetid,chars
etform,properties,nvl(length, 0), nvl(precision#, 0),nvl(scale,
0),nvl(radix, 0), type_owner,type_name,type_subname,type_linknam
e,pls_type from argument$ where obj#=:1 and procedure#=:2 order
by sequence# desc

这部分告诉我们在这段时间中执行最多的SQL语句。为了隔离某些频繁执行的查询，以观察是否有某些更改逻辑的方法以避免必须如此频繁的执行这些查询，这可能是很有用的。或许一个查询正在一个循环的内部执行，而且它可能在循环的外部执行一次，可以设计简单的算法更改以减少必须执行这个查询的次数。即使它运行的飞快，任何被执行几百万次的操作都将开始耗尽大量的时间。
9.实例活动
Instance Activity Stats for DB: BLISSDB Instance: blissdb Snaps: 4 -5
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
CPU used by this session 4,870 4.5 194.8
CPU used when call started 4,870 4.5 194.8
CR blocks created 45 0.0 1.8
DBWR buffers scanned 24,589 22.8 983.6
DBWR checkpoint buffers written 14,013 13.0 560.5
DBWR checkpoints 5 0.0 0.2
……
dirty buffers inspected 38,834 36.0 1,553.4 --脏缓冲的个数
free buffer inspected 40,463 37.5 1,618.5 --如果数量很大,说明缓冲区过小
……

10.I/O
下面两个报表是面向I/O的。通常，在这里期望在各设备上的读取和写入操作是均匀分布的。要找出什么文件可能非常“热”。一旦DBA了解了如何读取和写入这些数据，他们也许能够通过磁盘间更均匀的分配I/O而得到某些性能提升。
Tablespace IO Stats for DB: BLISSDB Instance: blissdb Snaps: 4 -5
->ordered by IOs (Reads + Writes) desc
Tablespace
------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
BLISS_DATA
17,649 16 12.3 1.2 44,134 41 0 0.0
UNDOTBS1
4,484 4 9.6 1.0 29,228 27 0 0.0
SYSTEM
340 0 31.0 1.1 36 0 0 0.0

File IO Stats for DB: BLISSDB Instance: blissdb Snaps: 4 -5
->ordered by Tablespace, File
Tablespace Filename
------------------------ ----------------------------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
BLISS_DATA D:ORACLEORADATABLISSDBBLISS01.DBF
5,779 5 12.0 1.2 14,454 13 0
D:ORACLEORADATABLISSDBBLISS02.DBF
5,889 5 12.1 1.2 14,772 14 0
D:ORACLEORADATABLISSDBBLISS03.DBF
5,981 6 12.6 1.2 14,908 14 0

11.缓冲池
Buffer Pool Statistics for DB: BLISSDB Instance: blissdb Snaps: 4 -5
-> Standard block size Pools D: default, K: keep, R: recycle
-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
Free Write Buffer
Number of Cache Buffer Physical Physical Buffer Complete Busy
P Buffers Hit % Gets Reads Writes Waits Waits Waits
--- ---------- ----- ----------- ----------- ---------- ------- -------- ------
D 3,000 99.4 4,482,816 25,756 73,470 11 9 0
-------------------------------------------------------------

如果我们使用多缓冲池的功能，上面的报表会告诉我们缓冲池引起的使用故障。实际上这只是我们在报表的开头看到的信息的重复。
12．回滚段活动
Instance Recovery Stats for DB: BLISSDB Instance: blissdb Snaps: 4 -5
-> B: Begin snapshot, E: End snapshot
Targt Estd Log File Log Ckpt Log Ckpt
MTTR MTTR Recovery Actual Target Size Timeout Interval
(s) (s) Estd IOs Redo Blks Redo Blks Redo Blks Redo Blks Redo Blks
- ----- ----- ---------- ---------- ---------- ---------- ---------- ----------
B 37 17 169 4012 3453 184320 3453
E 37 32 1385 57132 184320 184320 436361
-------------------------------------------------------------
一般期望活动在各回滚段间(除了SYSTEM回滚段外)均匀分布。在检查报表的这一部分时，报表标题也具有需要记住的最有用信息。尤其是，如果完全使用最佳设置时关于Optmal比Avg Active更大的建议。因为这是与DBA最有关的活动(I/O和回滚段信息)。

[ 本帖最后由 joelau 于 2007-3-27 10:50 编辑 ]


joelau 回复于：2007-03-27 10:41:30

有耐心的就再看一些吧


数据库的等待事件，发现前几名是：
log file parallel write

db file scattered read

log file sync

db file sequential read

SQL*Net more data to client

发现前面的4项都是影响到数据库性能的问题：

log file sync：

这个等待时间是指等待oracle的前台的commit和rollback操作进程完成，有时候这个等待时间也会包括等待LGWR进程把一个会话事务的日志记录信息从日志缓冲区中写到磁盘上的重做日志文件中。因此当前台进程在等待这个事件的时候，LGWR进程同时也在等待事件 log file parallel write。

理解什么造成这个等待事件的关键在于：对比这个等待事件和log file parallel write等待事件的平均等待时间：

l 如果他们的等待时间差不多，那么就是重做日志文件的I/O引起了这个等待事件，则需要调整重做日志文件的I/O，

l 如果log file parallel write等待事件的平均等待时间明显小于log file sync等待事件的等待时间，那么就是一些其他写日志的机制在commit和rollback操作时引起的等待，而不是I/O引起的等待。例如重做日志文件的latch竞争，会伴随出现 latch free或者LGWR wait for redo copy等待事件

在V$SESSION_WAIT中，这个等待事件有3个参数：

P1
代表在日志缓冲区中需要被写入到重做日志文件中的缓存数量，写入的同时会确认事务是否已经被提交，并且保留提交信息到实例意外中断前，因此必须等待LGWR将P1数量的缓存写入重做日志文件为止。

P2
无用

P3
无用


如果这个等待事件在整个等待事件中占了比较大的比重，可以从3个方面来进行调整

1. 调整LGWR进程时期具有更好的磁盘I/O吞吐量，例如不要将日志文件放在RAID5的磁盘上

2. 如果存在很多执行时间很短的事务，可以考虑将这些事务合并成一个批量事务以减少提交的次数，因为每次提交都需要确认相关的日志写入重做日志文件，因此使用批量事务来减少提交的次数是一种非常行之有效的减少I/O的方法

3. 产看是否有一些操作可以安全的使用NOLOGGING或者UNRECOVERABLE选项，这样可以减少日志文件的产生

Log file parallel write

这个等待事件出现在党LGWR后台进程从日志缓冲区写日志信息到磁盘上的重做日志文件的时候。只有启用了异步I/O的时候，LGWR进程才会并行写当前日志组内的充作日志文件，否则LGWR指挥循环顺序逐个的写当前日志组重做日志文件。LGWR进程不得不等待当前日志组所有的重做日志文件成员全部写完，因此，决定这个等待事件的等待时间长短的主要因素是重做日志文件所在磁盘的I/O读写速度。

如果是当前LGWR进程写的速度不够快导致这个等待事件，可以通过查看一些和重做日志相关的统计值来判定当前的LGWR进程是否效率低下，具体的可以看 redo writes, redo blocks written, redo write time, rdo wastage, redo size 等统计值，这些都是和LGWR进程性能直接相关的一些统计值。

在V$SESSION_WAIT中，这个等待事件的3个参数：

P1
代表正在被写入的重做日志文件组中的重做日志文件号

P2
代表需要写入重做日志组中每个重做日志文件的重做日志block数量

P3
代表I/O请求次数，需要被写入的block会被分成多次分别请求


如果这个等待事件占用比较多的时间，可以做如下调整

1． 采用UNRECOVERABLE/NOLOGGING操作尽量减少重做日志的产生

2． 在保证不会同时对市重做日志文件的前提下，尽量减少重做日志组中的成员个数，减少每次写重做日志文件的时间

3． 除非在备份情况下，否则不要在江表空间置于热备份的模式下，因为在表空间处于热备的模式下会产生更多的重做日志文件

4． 对于使用LogMiner、Logical Standby或者Streams，在能够满足要求功能的前提下，尽量使用最低级别的追加日志以减少重做日志的产生

5． 尽量将同一个日志组内的重做日志文件分散到不同的硬盘上，减少并行写重做日志文件时产生的I/O竞争

6． 不要将重做日志文件置于RAID5的磁盘上，最好放在裸设备上。

7． 如果设置了归档模式，不要将归档日志的目的地设置为存放重做日志的磁盘上，避免引起I/O竞争

关于Log的这2个问题的总结

通过上述对Log这2个问题的描述，以及产生的原因，除了Log file sync可能有其他方面的因素引起的（Latch），主要还是磁盘和使用习惯

1． 磁盘由于这些都是写磁盘所引起的，所以只有从减少写磁盘（指数据库本身的角度，和下列提到的用户操作习惯不一样）和加快写磁盘来减少这些等待时间

a) 尽量不要在RAID5的磁盘上保存重做日志文件，RAID5写的速度属于比较慢的

b) 在安全性保证的基础上，减少重做日志组成员的个数

c) 同一个日志组中的不同成员放在不同的磁盘上，加速写的速度。

d) 对可以采用NOLOGGING/UNRECOVERABLE的操作，使用这些选项减少log的产生

e) 有归档的，不要将归档的和在线重做日志放在一个磁盘上

2． 使用习惯如果用户不断的进行commit或者rollback，这样必定引起一次log日志的写操作。因此可以通过一些统计信息判断是否每次的日志的写操作数据量很小，这样通过调节用户的操作，将大量的数据更新合并到一个事务中来，这样增加每次日志的操作量，减少对日志的不断调用，提高 LGWR的写的效率。

db file scattered read

这是一个非常常见的等待时间。当oracle从磁盘上读取多个block到不连续的高速缓存区的缓存中就会发生这个等待事件，Oracle一次最多能够读入的block数量由初始化参数DB_FILE_MULTIBLOCK_READ_COUND决定，这个时间一般伴随着全表扫描或者 Fast Full Index 扫描一起出现。

在V$SESSION_WAIT中，这个等待事件的几个参数：

P1
代表oracle的文件号

P2
代表从这个文件中开始读取的block号

P3
代表从这个block开始需要读取的block数量


一般从这个3个参数，就可以回头查询到是在读取数据库的哪个对象，然后分析对这个对象的操作来进行优化Sql语句。

如果这个等待事件占的比重比较厉害，可以通过以下方法来调整

方法一

找出执行全表扫描或者Fast Full index扫描的Sql语句，判断这些扫描是否是必要的，是否导致了比较差的执行计划，进行调整。

从oracle9i开始，提供了一个视图V$SQL_PLAN，可以通过它帮助我们找到那些全表扫描或者Fast Full Index扫描的Sql语句：

查找全表扫描的SQL语句

Select sql_text from v$sqltext t, v$sql_plan p

Where t.hashvalue=p.hash_value

And p.operation=’TABLE ACCESS’

And p.option=’FULL’

Order by p.hash-value, t.piece;


查找Fast Full index 扫描的Sql语句可以这样；

Select sql_text from v$sqltext t, v$sql_plan p

Where t.hashvalue=p.hash_value

And p.operation=’INDEX’

And p.option=’FULL SCAN’

Order by p.hash-value, t.piece;


如果是Oracle8i的数据库，可以通过v$session_event视图中找到关于这个等待事件的进程sid，然后根据这个sid来跟踪相应会话的SQL

Select sid, event from v$session_event

Where event=’db file sequential read’


或者可以通过查看物理读取最多的SQL语句的执行计划，看是否里面包含了全表扫描和Fast Full Index扫描，可以通过以下语句获取物理读取最多的SQL语句

Select sql_text from

Select *from v$sqlarea

Order by disk_reads)

Where rownum<10


方法二：

有时候执行计划很好也会出现多block扫描的情况，这个时候可以通过调整Oracle数据库的多block的I/O，来设置一个合理的DB_FILE_MULTIBLOCK_READ_COUNT,使得尽量满足；

Db_block_size * DB_FILE_MULTIBLOCK_READ_COUNT = max io size of system

这个参数也不是设置的越大越好，设置这个参数之前需要了解一下应用的类型，如果是OLTP类型的，一般来说全表扫描较少，这个时候如果设置了比较大反而会降低数据库的性能，因为CBO在某些情况下会因为多block读取导致COST比较低从而错误的选用了全表扫描。

其他方法

还可以采用对表和索引使用分区、将缓存区的LRU末端的全表扫描和FastFullIndex扫描的block放入到Keep缓存池等方法来进行调节。

 

 

 

 

在statspack的报告分析中呢，我们首先要关注的就是基于elapse time的收集时间间隔，任何的统计数据在statspack中都是要通过时间的这个纬度里度量的，离开了时间的纬度，那么任何其他的数据也就失去了本身的意义。

我截取了在做bulk update时候的一个statspack，性能上来讲瓶颈不在数据库 这端，只是想针对statspack来说明报告的不同部分。

Snapshot       Snap Id     Snap Time      Sessions Curs/Sess Comment
~~~~~~~~    ---------- ------------------ -------- --------- -------------------
Begin Snap:          6 21-Jan-08 11:02:25       26       9.7
  End Snap:          7 21-Jan-08 12:14:03       26      11.2
   Elapsed:               71.63 (mins)

这里列出了我的测试 经历的时间：71.63 mins

Cache Sizes                       Begin        End
~~~~~~~~~~~                  ---------- ----------
               Buffer Cache:       608M             Std Block Size:         8K
           Shared Pool Size:       256M                 Log Buffer:    13,920K
同时也列出了我目前各个SGA关键组件的大小。

在分析statspack报告之前呢，Load profile（负载概要信息），这一部分是一定要读的。包括了Per Second和Per Transaction这两个指标的状态。那么所有的这些都是通过收集v$sysstat 视图的信息来获得的。
Load Profile                            Per Second       Per Transaction
~~~~~~~~~~~~                       ---------------       ---------------
                  Redo size:              5,466.50              1,623.37
     (redo size这部分的单位是bytes, 表示在71分钟左右的时间里，每秒生成的redo的大小，通过还标识了每个事务产生的redo大小)      
              Logical reads:              8,682.95              2,578.55
     (这里logical reads=db block gets+consistent gets)
              Block changes:                 23.17                  6.88
             Physical reads:                  0.52                  0.16
            Physical writes:                  1.76                  0.52
                 User calls:                 43.10                 12.80
                     Parses:                 14.23                  4.23
                Hard parses:                  0.01                  0.00
                      Sorts:                105.55                 31.34
                     Logons:                  0.02                  0.01
                   Executes:                139.63                 41.47
               Transactions:                  3.37
     那么这里Transactions=(user commits + user rollbacks)/(71.63*60)
      (其实这里用transactions rollbacks来代替user rollbacks更恰当)

  % Blocks changed per Read(这里主要表示的是Block changes/Logical reads的比率):    0.27    Recursive Call %:    80.50
 Rollback per transaction %:    0.00       Rows per Sort:     1.23
 (这里也表示我们transaction的平均回滚率,user rollbacks/(user commits+user rollbacks))
主要注意一个细节：
transactions rollbacks和user rollbacks的区别，通常用户如果执行了一个rollback,即使没有事务需要回滚，user rollbacks也还是要+1,但是transaction rollbacks只有在确实有事务回滚的时候才+1

以上诸如user commits信息都可以从Statspack报告中的Instance Activity Stats部分来观察到：

那么来看下一个部分
Instance Efficiency Percentages
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:  100.00       Redo NoWait %:  100.00
            Buffer  Hit   %:   99.99    In-memory Sort %:  100.00
            Library Hit   %:   99.98        Soft Parse %:   99.90
         Execute to Parse %:   89.81         Latch Hit %:  100.00
Parse CPU to Parse Elapsd %:   13.77     % Non-Parse CPU:   99.99

这里列出了数据库的一些性能指标。需要注意的是Buffer hit的计算公式在10g中有了改变
Buffer hit% = 1 - (physical reads-physical reads direct-physical reads direct(lob))/session logical reads-(physical reads direct+physical reads direct(lob))
其中我们的session logical reads=db block gets+consistent gets
在很多情况下buffer hit越高不全都反映，我数据库的状态越好，在有的情况下，buffer hit%高会往往饶过我逻辑读比较高这个问题。

In-memory Sort = sort memory / (sort memory + sort disk) * 100%
通过这个指标我们可以观察一下我们的内存排序量

Soft Parse % = 1 - parse count(hard)/parse count(total)
除了这里对于软解析我们还要关注Load profile中的Hard parses的per seconds单位来联合观察

Execute to Parse % = 1-parses/executions
这里主要介绍的是解析和执行之间的比率。那么也就是说有多少次执行我们不需要解析。这个指标越高越好

Parse CPU to Parse Elapsd % = （parse time cpu / parse time elapsed）* 100%
parse time cpu表示的是oracle 在进行SQL 解析的时候耗用的CPU时间,而parse time elapsed指在进行sql解析的时候总耗费CPU的时间

那么所有这些参与计算的指标信息我们都是可以从Instance Activity Stats中的total列得到的

最后呢，来总结一下statspack报告中比较重要的一块:
响应时间的判断，response time是我们衡量数据库响应时间的一个重要指标，
Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
CPU time                                                     3,088          94.4
log file parallel write                         14,363          69      5    2.1
log file sync                                   14,263          68      5    2.1
control file parallel write                      1,466          32     22    1.0
db file parallel write                           1,734          11      6     .3

这里我们看到了top 5的等待事件：
其中的CPU TIME是Oracle 9i以后引入的，这里其实需要指出的是，调优的第一手资料其实还是从statspack中的这个部分来分析，那么结合我们的top 5的等待事件，我们可以来衡量不同等级的top sql:

1.消耗最多CPU的(逻辑IO比较多的)

2.导致过多物理I/O的(物理IO比较多的)

3.执行次数较频繁的(Execution次数比较多的)

4.执行时间较长的(Elapse time比较长的)

其实在Oracle世界里有一套关于resposne time的分析方法：

Response time=Service time + Wait time

因 此在上面的top 5 timed events表格里面我们看见了有Waits和Time (s)两列，分别罗列了我们response time的两大部分，一部分是服务时间由time (s)表示，另外一个部分就是等待时间了由Waits表示。而Time (s)是指花在CPU上的时间，而Waits是花 在等待事件上的总的时间

那么什么是Service time呢？
其实Service time就是CPU为执行 该语句花费的时间。
Service Time=CPU Parse + CPU Recursive + CPU Other 三部分组成
CPU Parse是CPU用于分析语句的时间，CPU Recursive是CPU用于语句的递归SQL的时间，CPU Other则就是CPU用于执行语句的真正时间了。

在Statpacks中Instance Activity Stats中就有部分的时间统计信息：

Service time=CPU used by this session

CPU Parse=parse time cpu

CPU Resursive=recursive cpu usage

所以CPU ther=CPU used by this session - parse time cpu - recursive cpu usage

如果执行时间(CPU Other)在整个Service time中占较大的比例，那么下一步就是找出那些造成了最多逻辑IO的SQL语句 ，可以从statspack报告的SQL ordered by Gets部分找到。

如果分析时间(CPU Parse=parse time cpu)在整个响应时间中占较大的比例，那么下一步就是查找哪些SQL分析过多 ，这在statspack报告中在SQL ordered by Parse Calls中列出。

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

比如我们现在从Instance Activity Stats查出一些数据：
Instance Activity Stats  DB/Inst: IRMDB/irmdb  Snaps: 6-7

Statistic                                      Total     per Second    per Trans
--------------------------------- ------------------ -------------- ------------
CPU used by this session                     308,760           71.8         21.3
......
parse time cpu                                    23            0.0          0.0
parse time elapsed                               167            0.0          0.0
......
recursive cpu usage                          307,328           71.5         21.2
......

我们知道CPU Time的total call time%是94.4%
我们就可以看一下：
CPU Time=94%
CPU ther=308,760 - 23 - 307,328 = 1409
CPU ther=1409 / 308,760 * 94% = 0.43%
CPU Parse=23 / 308,760 * 94% = 忽略不计
CPU Recursive = 307,328 / 308,760 * 94% = 93.56%

可以看到原来我们的Recursive call占用了我们大量的CPU时间

除 了上面的SQL ordered by Gets(逻辑IO最多)，SQL ordered by Parse Calls(软解析过多)，SQL ordered by Reads(物理IO过多)，statspack还按照其他的一些方式列出了Top SQL，这些Top SQL在某些情况下都是需要给予特别关注的。比如：

　　SQL ordered by Executions 执行次数超过100的

　　SQL ordered by Sharable Memory 占用library cache超过1M的

　　SQL ordered by Version Count 子cursor超过20的

　　如果没有statspack，那么根据v$sysstat/v$sesstat中的统计信息，结合v$sql/v$sqlarea，一样可以得到相关的SQL。

　 　v$sql对于每一个子cursor都有一行统计记录，而v$sqlarea则对同一个父cursor只有一行统计记录，也就是v$sqlarea是对 v$sql按照父cursor进行group by后的一个结果。这两个视图中都有诸如 buffer_gets,parse_calls,disk_reads,,executions,sharable_mem等列，和上面提到的 statspack中列出Top SQL的条件对应。