详解statspack 报告

5.6 SQL统计信息－多版本缓存
在这一部分，主要是针对SQL语句的多版本进行排序。相同的SQL文本，但是不同属性，比如对象owner不同，会话优化模式不同、类型不同、长度不同和绑定变量不同等等的语句，他们是不能共享的，所以再缓存中会存在多个不同的版本。这当然就造成了资源上的更多的消耗。
每个语句的执行会产生两个cursor，一个parent cursor，一个child cursor。
parent cursor中包含三部分内容：
1）SQL语句本身
2）handle，通过这个handle与library cache的hash table能够根据hash value的值关联
3）object，指向所有child cursor的指针。
child cursor中包含4部分内容：
1）handle，通过这个handle与parent的pointer关联
2）object，这里具体的作用不是很明白
3）heap0：存放该SQL语句中的对象标识，环境标识等，正是这些特征确保了该SQL的唯一性。这里的对象标识就是表示执行的用户信息和schema信息等。环境标识就是例如optimizer_mode设置等。
4）heap6：存放该语句的执行计划
所以，不同的用户执行同一个语句，访问同一张表，执行计划可能是不同的。同一个用户执行同一个语句，可能执行计划也会不同。如不同的用户访问其架构里面同样的对象,此时SQL的哈希散列值是一样,但具体的v$sql中的信息不一样,还有绑定变量时开始系统估计的变量长度是30,而后传进来的变量长度跳跃到80,则此时会生成另外一个body,再则根据绑定变量的传值,发现这个数据在表中占了大部分的行,走索引已经不大合适,要换成全表扫描,此时新的查询方案将生成,body自然会增加.

SQL ordered by Version Count for DB: ORA92  Instance: ora92  Snaps: 13 -14
-> End Version Count Threshold:        20

Version
   Count  Executions   Hash Value
-------- ------------ ------------
      30       15,112   3531895589
Module: Das.exe
INSERT INTO T_DXH_DXHRECLOG (CALLERNO, CALLEENO, NOTIFYFLAG, SMS
TYPE, AREAID, LOGDATE) VALUES (:B4 , :B3 , 1, :B2 , :B1 , TO_CHA
R(SYSDATE, 'MMDD'))

小结：
对于出现在上面的可疑的sql语句，我们可以查看语句相关的执行计划，然后分析相关索引等是否合理。
通过语句查看执行计划的方法:
SELECT id,parent_id,LPAD(' ',4*(LEVEL-1))||operation||' '||options||' '||object_name "Execution plan" ,cost,cardinality,bytes
FROM (
SELECT p.* FROM v$sql_plan p,v$sql s WHERE p.address = s.ADDRESS
AND p.hash_value = s.HASH_VALUE
and p.hash_value = '&hash_value'
)
CONNECT BY PRIOR id = parent_id
START WITH id = 0;

 

5.2 SQL统计信息－物理读
这部分通过物理读对SQL语句进行排序。这显示引起大部分对这个系统进行读取活动的SQL，即物理I/O。当我们的系统如果存在I/O瓶颈时，需要关注这里I/O操作比较多的语句。
SQL ordered by Reads for DB: ORA92  Instance: ora92  Snaps: 13 -14
-> End Disk Reads Threshold:      1000
                                                     CPU      Elapsd
Physical Reads  Executions  Reads per Exec %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
         4,187           24          174.5   15.8     0.79     52.99 1895519470
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN p_dxh_tmp_importUserInfo2(500); :
mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END I
F; END;
            538       21,504            0.0    2.0     5.92    241.61 1725988165
Module: Das.exe
begin P_DXH_AddSms(I_CALLERNO=>:V001,I_CALLEENO=>:V002,I_CALLTY
PE=>:V003,I_DXHHFLAG=>:V004,O_RET=>:V005);end;
……
5.3 SQL统计信息－执行次数
这部分告诉我们在这段时间中执行次数最多的SQL语句。为了隔离某些频繁执行的查询，以观察是否有某些更改逻辑的方法以避免必须如此频繁的执行这些查询，这可能是很有用的。或许一个查询正在一个循环的内部执行，而且它可能在循环的外部执行一次，可以设计简单的算法更改以减少必须执行这个查询的次数。即使它运行的飞快，任何被执行几百万次的操作都将开始耗尽大量的时间。
SQL ordered by Executions for DB: ORA92  Instance: ora92  Snaps: 13 -14
-> End Executions Threshold:       100
                                                CPU per    Elap per
Executions   Rows Processed   Rows per Exec    Exec (s)   Exec (s)  Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
     102,491               0              0.0       0.00        0.00 1053795750
Module: Das.exe
COMMIT

      48,861          38,275              0.8       0.00        0.00  947217968
Module: Das.exe
SELECT T.AREAID FROM T_DXH_MOBILE S, T_DXH_AREA T WHERE S.MOBILE
SEGMENT = SUBSTR(:B1 ,1,7) AND T.AREACODE = S.AREACODE AND ROWNU
M = 1

5.4 SQL统计信息－调用、解析次数
在这一部分，主要显示PARSE与EXECUTIONS的对比情况。如果PARSE/EXECUTIONS>1，往往说明这个语句可能存在问题：没有使用绑定变量，共享池设置太小，cursor_sharing被设置为exact，没有设置session_cached_cursors等等问题。
SQL ordered by Parse Calls for DB: ORA92  Instance: ora92  Snaps: 13 -14
-> End Parse Calls Threshold:      1000

                           % Total
Parse Calls  Executions   Parses  Hash Value
------------ ------------ -------- ----------
      61,404       30,650    32.06 3303409220
Module: SvcProcessor.exe
begin P_DXH_UPDATESUBMITSTATUS(:V00001,:V00002,:V00003,:V00004);
end;
       1,661        1,661     0.87  140223014
Module: SvcProcessor.exe
SELECT SERIALNO, PID, SERVICEID, SMSCONTENT, REPORTFLAG, ORGADDR
, DESTADDR, FEEADDR, FEETYPE, FEEUSERTYPE, FEECODE, SPID FROM T_
DXH_OPENDETECT WHERE LOCKFLAG = :B1

5.5 SQL统计信息－共享内存占用
在这一部分，主要是针对shared memory占用的情况进行排序。
SQL ordered by Sharable Memory for DB: ORA92  Instance: ora92  Snaps: 13 -14
-> End Sharable Memory Threshold:   1048576

Sharable Mem (b)  Executions  % Total  Hash Value
---------------- ------------ ------- ------------
       1,115,384       15,112     0.2   3531895589
Module: Das.exe
INSERT INTO T_DXH_DXHRECLOG (CALLERNO, CALLEENO, NOTIFYFLAG, SMS
TYPE, AREAID, LOGDATE) VALUES (:B4 , :B3 , 1, :B2 , :B1 , TO_CHA
R(SYSDATE, 'MMDD'))

  5、SQL统计信息
接下来的部分，是关于SQL的统计信息，分为6块来统计排序：
ordered by buffer gets
ordered by Physical reads
ordered by Executions
ordered by Parse Calls
ordered by Sharable Memory
ordered by Version Count


5.1 SQL统计信息－逻辑读
这一部分，通过Buffer Gets对SQL语句进行排序，即通过它执行了多少个逻辑I/O来排序。顶端的注释表明一个PL/SQL单元的缓存获得(Buffer Gets)包括被这个代码块执行的所有SQL语句的Buffer Gets。因此将经常在这个列表的顶端看到PL/SQL过程，因为存储过程执行的单独的语句的数目被总计出来。
在这里的Buffer Gets是一个累积值，所以这个值大并不一定意味着这条语句的性能存在问题。通常我们可以通过对比该条语句的Buffer Gets和physical reads值，如果这两个比较接近，肯定这条语句是存在问题的，我们可以通过执行计划来分析，为什么physical reads的值如此之高。另外，我们在这里也可以关注gets per exec的值，这个值如果太大，表明这条语句可能使用了一个比较差的索引或者使用了不当的表连接。
另外说明一点：大量的逻辑读往往伴随着较高的CPU消耗。所以很多时候我们看到的系统CPU将近100％的时候，很多时候就是SQL语句造成的，这时候我们可以分析一下这里逻辑读大的SQL。
SQL ordered by Gets for DB: ORA92  Instance: ora92  Snaps: 13 -14
-> 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
--------------- ------------ -------------- ------ -------- --------- ----------
13,367,435          171       78,172.1   68.3   259.36    353.19 3790040751
这里CPU Timev(s)就是该sql语句消耗的总的cpu时间，%Total则是代表该语句所占用的cpu时间在所有所有cpu time中的比例。
这里的CPU time(s)可以表示Service Time，Elapsd Time(s)可以表示Wait Time。如果Elapsd Time(s)太大，我们可以认为sql在执行的过程中等待的时间过长，从而造成%Total值过大，这种情况下我们需要查找等待的具体原因。
如果Elapsd Time(s)很小，基本可以忽略等待的情况，然而%Total的值同样过大，表明这条语句在整个报告的时间段中占用的cpu时间片的比较大，在cpu存在性能瓶颈的时候，我们就需要优化这条sql，该sql可能存在过度的解析或者过多的逻辑读。
当然这也跟调用的频度有关系。

DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN P_DXH_DEALOVERTIMEDXHREC; :mydate
:= next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END
……   4、TOP 5及其他等待事件信息（续4）

Wait Events for DB: ORA92  Instance: ora92  Snaps: 13 -14
-> 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 scattered read              2,415          0          6      3      0.0
db file sequential read             7,303          0         65      9      0.1
如果这两个Average Wait time明显低于7 ms，表明磁盘子系统并不存在瓶颈，我们不需要把焦点集中在磁盘子系统上。相反，如果这个指标明显高于10ms，那么我们就需要关注我们的sotrage是否存在瓶颈。
10ms是个经验值，一个random IO的理论时间是：
7ms = 4-5ms(磁盘平均寻道时间）+ 2ms (传输时间) + 一些其它的消耗
如果不考虑file system cache hit(raw device/direct IO)  以及storage cache hit , 同时没有磁盘竞争，那么，db file sequntial read的时间就会是 7ms左右。
而由于file system cache hit和storage cache hit的情况下，没有磁盘竞争的系统db file sequntial read 会小于 7ms。
如果有磁盘竞争，而且竞争产生的延迟> file system cache hit和storage cache hit的好处，就会大于7ms .
10ms 可以说是一个经验值，就是磁盘竞争产生的延迟比较高了。
log file sync                      74,324          0        101      1      1.0
enqueue                               729          0         88    121      0.0
SQL*Net message from dblink           482          0         20     42      0.0
db file parallel write                725          0         14     19      0.0
process startup                         8          0          4    440      0.0
latch free                          1,307      1,300          2      1      0.0
log file parallel write            67,042          0          2      0      0.9
control file sequential read          269          0          1      3      0.0
single-task message                    24          0          1     33      0.0
control file parallel write           325          0          1      2      0.0
buffer busy waits                   3,368          0          1      0      0.0
log file switch completion             19          0          0     20      0.0
direct path read                      288          0          0      0      0.0
LGWR wait for redo copy             1,032          0          0      0      0.0
SQL*Net more data to client         1,390          0          0      0      0.0
kksfbc child completion                 1          1          0     10      0.0
log file sequential read                2          0          0      5      0.0
direct path write                     128          0          0      0      0.0
library cache pin                      14          0          0      0      0.0
SQL*Net more data from dblin            4          0          0      0      0.0
log file single write                   2          0          0      1      0.0
SQL*Net message to dblink             482          0          0      0      0.0
buffer deadlock                        30         30          0      0      0.0
SQL*Net message from client       436,773          0    143,221    328      5.8
jobq slave wait                     2,688      1,664      6,688   2488      0.0
wakeup time manager                    27         27        791  29297      0.0
SQL*Net message to client         436,772          0          0      0      5.8   4、TOP 5及其他等待事件信息（续3）
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time                                                          361    54.14
这个CPU time实际上就是Service time，放在这里，我们也可以看到wait time所占用的总体比例，在一定的程度上，可以很直接的反映系统的等待是否严重。当然这个值本身当然是越小越好，小表明系统的cpu占用小，但后面的百分比越高越好，越高就表明用于等待的时间越小。YAPP的方法就可以在这里方便的套用，有时候可以基本上确定需要调优的方向。
Top 5里有 CPU Time，一方面确实表明等待的情况相对来说比较少。但是，这里的高CPU消耗，有可能是用于过度的hard parse或者过高的逻辑读的。所以并不是说一定是好事。这时候，可以再结合当时的os层面的cpu消耗情况来判断，如果当时的cpu比较空，那基本就没问题。如果当时cpu已经成为瓶颈，那就需要去调sql。Cpu使用的程度，我们可以根据这里的值来大概计算。比如，这一份报告的是的时间是14.05分钟，我们可以计算总的cpu时间为：14.05*60*2(cpu个数)=1686秒，所以cpu的占用为361/1686,大概为21％，在采集报告的这段时间，cpu是比较空闲的，也不足以成为性能的瓶颈。
Top 5里有 CPU Time 不好说有没有问题。但是，这个事件排第一，总比其他事件排第一要好。
Top 5里没有 CPU Time 肯定有问题。
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   4、TOP 5及其他等待事件信息（续2）
12)        DB File Parallel Write：文件被DBWR并行写时发生。解决办法：改善IO性能。
13)        DB File Single Write：当文件头或别的单独块被写入时发生，这一等待直到所有的I/O调用完成。解决办法：改善IO性能。
14)        DB FILE Scattered Read：当扫描整个段来根据初始化参数db_file_multiblock_read_count读取多个块时发生，因为数据可能分散在不同的部分，这与分条或分段）相关，因此通常需要多个分散的读来读取所有的数据。等待时间是完成所有I/O调用的时间。解决办法：改善IO性能。
15)        DB FILE Sequential Read：当前台进程对数据文件进行常规读时发生，包括索引查找和别的非整段扫描以及数据文件块丢弃等待。等待时间是完成所有I/O调用的时间。解决办法：改善IO性能。
16)        Direct Path Read：一般直接路径读取是指将数据块直接读入PGA中。一般用于排序、并行查询和read ahead操作。这个等待可能是由于I/O造成的。使用异步I/O模式或者限制排序在磁盘上，可能会降低这里的等待时间。
17)        direct path write：直接路径写该等待发生在，系统等待确认所有未完成的异步I/O 都已写入磁盘。对于这一写入等待，我们应该找到I/O 操作最为频繁的数据文件(如果有过多的排序操作，很有可能就是临时文件)，分散负载，加快其写入操作。如果系统存在过多的磁盘排序，会导致临时表空间操作频繁，对于这种情况，可以考虑使用Local管理表空间，分成多个小文件，写入不同磁盘或者裸设备。
18)        control file parallel write：当server 进程更新所有控制文件时，这个事件可能出现。如果等待很短，可以不用考虑。如果等待时间较长，检查存放控制文件的物理磁盘I/O 是否存在瓶颈。
多个控制文件是完全相同的拷贝，用于镜像以提高安全性。对于业务系统，多个控制文件应该存放在不同的磁盘上，一般来说三个是足够的，如果只有两个物理硬盘，那么两个控制文件也是可以接受的。在同一个磁盘上保存多个控制文件是不具备实际意义的。减少这个等待，可以考虑如下方法:①减少控制文件的个数(在确保安全的前提下)。②如果系统支持，使用异步IO。③转移控制文件到IO 负担轻的物理磁盘。
19)        control file sequential read
control file single write ：控制文件连续读/控制文件单个写对单个控制文件I/O 存在问题时，这两个事件会出现。如果等待比较明显，检查单个控制文件，看存放位置是否存在I/O 瓶颈。

对于常见的一些IDLE wait事件举例：
dispatcher timer                  
lock element cleanup               
Null event                        
parallel query dequeue wait        
parallel query idle wait - Slaves  
pipe get                           
PL/SQL lock timer                  
pmon timer- pmon                  
rdbms ipc message                  
slave wait                        
smon timer                        
SQL*Net break/reset to client      
SQL*Net message from client        
SQL*Net message to client         
SQL*Net more data to client        
virtual circuit status            
client message                     
SQL*Net message from client   
下面是关于这里的常见的等待事件和解决方法的一个快速预览
等待事件        一般解决方法
Sequential Read        调整相关的索引和选择合适的驱动行源
Scattered Read         表明出现很多全表扫描。优化code，cache小表到内存中。
Free Buffer         增大DB_CACHE_SIZE，增大checkpoint的频率，优化代码
Buffer Busy Segment header        增加freelist或者freelistgroups
Buffer Busy Data block        隔离热块；使用反转索引；使用更小的块；增大表的initrans
Buffer Busy Undo header        增加回滚段的数量或者大小
Buffer Busy Undo block        Commit more；增加回滚段的数量或者大小
Latch Free         检查具体的等待latch类型，解决方法参考后面介绍
Enqueue–ST         使用本地管理的表空间或者增加预分配的盘区大小
Enqueue–HW         在HWM之上预先分配盘区
Enqueue–TX4         在表或者索引上增大initrans的值或者使用更小的块
Log Buffer Space         增大LOG_BUFFER，改善I/O
Log File Switch         增加或者增大日志文件
Log file sync         减小提交的频率；使用更快的I/O；或者使用裸设备
Write complete waits        增加DBWR；提高CKPT的频率；   4、TOP 5及其他等待事件信息（续）

4)        latch free：当闩锁丢失率高于0.5%时，需要调整这个问题。详细的我们在后面的Latch Activity for DB部分说明。
5)        Enqueue 队列是一种锁，保护一些共享资源，防止并发的DML操作。队列采用FIFO策略，注意latch并不是采用的FIFO机制。比较常见的有3种类型的队列：ST队列，HW队列，TX4队列。
ST Enqueue的等待主要是在字典管理的表空间中进行空间管理和分配时产生的。解决方法：1）将字典管理的表空间改为本地管理模式 2）预先分配分区或者将有问题的字典管理的表空间的next extent设置大一些。
HW Enqueue是用于segment的HWM的。当出现这种等待的时候，可以通过手工分配etents来解决。
TX4 Enqueue等待是最常见的等待情况。通常有3种情况会造成这种类型的等待：1）唯一索引中的重复索引。解决方法：commit或者rollback以释放队列。 2）对同一个位图索引段（bitmap index fragment）有多个update，因为一个bitmap index fragment可能包含了多个rowid，所以当多个用户更新时，可能一个用户会锁定该段，从而造成等待。解决方法同上。3）有多个用户同时对一个数据块作update，当然这些DML操作可能是针对这个数据块的不同的行，如果此时没有空闲的ITL槽，就会产生一个block-level锁。解决方法：增大表的initrans值使创建更多的ITL槽；或者增大表的pctfree值，这样oracle可以根据需要在pctfree的空间创建更多的ITL槽；使用smaller block size，这样每个块中包含行就比较少，可以减小冲突发生的机会。
6)        Free Buffer：这个等待事件表明系统正在等待内存中的可用空间，这说明当前Buffer 中已经没有Free 的内存空间。如果应用设计良好，SQL 书写规范，充分绑定变量，那这种等待可能说明Buffer Cache 设置的偏小，你可能需要增大DB_CACHE_SIZE。该等待也可能说明DBWR 的写出速度不够，或者磁盘存在严重的竞争，可以需要考虑增加检查点、使用更多的DBWR 进程，或者增加物理磁盘的数量,分散负载，平衡IO。
7)        Log file single write：该事件仅与写日志文件头块相关，通常发生在增加新的组成员和增进序列号时。头块写单个进行，因为头块的部分信息是文件号，每个文件不同。更新日志文件头这个操作在后台完成，一般很少出现等待，无需太多关注。
8)        log file parallel write：从log buffer 写redo 记录到redo log 文件，主要指常规写操作(相对于log file sync)。如果你的Log group 存在多个组成员，当flush log buffer 时，写操作是并行的，这时候此等待事件可能出现。尽管这个写操作并行处理，直到所有I/O 操作完成该写操作才会完成(如果你的磁盘支持异步IO或者使用IO SLAVE，那么即使只有一个redo log file member,也有可能出现此等待)。这个参数和log file sync 时间相比较可以用来衡量log file 的写入成本。通常称为同步成本率。改善这个等待的方法是将redo logs放到I/O快的盘中，尽量不使用raid5，确保表空间不是处在热备模式下，确保redo log和data的数据文件位于不同的磁盘中。
9)        log file sync：当一个用户提交或回滚数据时，LGWR将会话的redo记录从日志缓冲区填充到日志文件中，用户的进程必须等待这个填充工作完成。在每次提交时都出现，如果这个等待事件影响到数据库性能，那么就需要修改应用程序的提交频率, 为减少这个等待事件，须一次提交更多记录，或者将重做日志REDO LOG 文件访在不同的物理磁盘上，提高I/O的性能。
10)        log buffer space：日志缓冲区写的速度快于LGWR写REDOFILE的速度,可以增大日志文件大小，增加日志缓冲区的大小，或者使用更快的磁盘来写数据。
11)        logfile switch：通常是因为归档速度不够快。表示所有的提交(commit)的请求都需要等待"日志文件切换"的完成。Log file Switch 主要包含两个子事件:
log file switch (archiving needed) 这个等待事件出现时通常是因为日志组循环写满以后，第一个日志归档尚未完成，出现该等待。出现该等待，可能表示io 存在问题。解决办法:①可以考虑增大日志文件和增加日志组；②移动归档文件到快速磁盘；③调整log_archive_max_processes。
log file switch (checkpoint incomplete) 当日志组都写完以后，LGWR 试图写第一个log file，如果这时数据库没有完成写出记录在第一个log file 中的dirty 块时(例如第一个检查点未完成)，该等待事件出现。该等待事件通常表示你的DBWR 写出速度太慢或者IO 存在问题。为解决该问题，你可能需要考虑增加额外的DBWR 或者增加你的日志组或日志文件大小，或者也可以考虑增加checkpoint的频率。   4、TOP 5及其他等待事件信息
/* 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 COST2)        db file sequential read：该事件说明在单个数据块上大量等待，该值过高通常是由于表间连接顺序很糟糕（没有正确选择驱动行源），或者使用了非选择性索引。通过将这种等待与statspack报表中已知其它问题联系起来(如效率不高的sql),通过检查确保索引扫描是必须的，并确保多表连接的连接顺序来调整。
3)        buffer busy wait：当缓冲区以一种非共享方式或者如正在被读入到缓冲时,就会出现该等待。该值不应该大于1%。当出现等待问题时，可以检查缓冲等待统计部分(或V$WAITSTAT)，确定该等待发生在什么位置：
a)        如果等待是否位于段头(Segment Header)。这种情况表明段中的空闲列表（freelist）的块比较少。可以考虑增加空闲列表(freelist，对于Oracle8i DMT)或者增加freelist groups(在很多时候这个调整是立竿见影的(alter table tablename strorage(freelists 2))，在8.1.6之前，这个freelists参数不能动态修改;在8.1.6及以后版本，动态修改feelists需要设置COMPATIBLE至少为8.1.6)。也可以增加PCTUSED与PCTFREE之间距离（PCTUSED-to-pctfree gap），其实就是说降低PCTUSED的值，尽快使块返回freelist列表被重用。如果支持自动段空间管理（ASSM），也可以使用ASSM模式，这是在ORALCE 920以后的版本中新增的特性。
b)        如果这一等待位于undo header，可以通过增加回滚段(rollback segment)来解决缓冲区的问题。
c)        如果等待位于undo block上，我们需要增加提交的频率，使block可以尽快被重用；使用更大的回滚段；降低一致读所选择的表中数据的密度；增大DB_CACHE_SIZE。
d)        如果等待处于data block，表明出现了hot block，可以考虑如下方法解决： ①将频繁并发访问的表或数据移到另一数据块或者进行更大范围的分布(可以增大pctfree值 ，扩大数据分布，减少竞争)，以避开这个"热点"数据块。②也可以减小数据块的大小，从而减少一个数据块中的数据行数，降低数据块的热度，减小竞争；③检查对这些热块操作的SQL语句，优化语句。④增加hot block上的initrans值。但注意不要把initrans值设置的过于高了，通常设置为5就足够了。因为增加事务意味着要增加ITL事务槽，而每个ITL事务槽将占用数据块中24个字节长度。默认情况下，每个数据块或者索引块中是ITL槽是2个，在增加initrans的时候，可以考虑增大数据块所在的表的PCTFREE值，这样Oracle会利用PCTFREE部分的空间增加ITL slot数量，最大达到maxtrans指定。
e)        如果等待处于index block，应该考虑重建索引、分割索引或使用反向键索引。为了防止与数据块相关的缓冲忙等待，也可以使用较小的块，在这种情况下，单个块中的记录就较少，所以这个块就不是那么"繁忙"。或者可以设置更大的PCTFREE,使数据扩大物理分布，减少记录间的热点竞争。在执行DML (insert/update/ delete)时，Oracle向数据块中写入信息，对于多事务并发访问的数据表，关于ITL的竞争和等待可能出现，为了减少这个等待，可以增加initrans，使用多个ITL槽。在Oracle9i 中，可以使用ASSM这个新特性Oracle 使用位图来管理空间使用，减小争用。   3、实例有效性信息
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事件。   2、实例负载档信息
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各项含义
Redo size:每秒产生的日志大小(单位字节)，可标志数据变更频率, 数据库任务的繁重与否。
Logical reads:平决每秒产生的逻辑读的block数。Logical Reads= Consistent Gets + DB Block Gets
Block changes:每秒block变化数量，数据库事物带来改变的块数量。
Physical reads:平均每秒数据库从磁盘读取的block数。
Physical writes:平均每秒数据库写磁盘的block数。Physical reads + Physical writes可以近似表征这个系统的IOPS。
User calls:每秒用户调用次数。
Parses:每秒解析次数，包括soft parse和hard parse两种数量的综合。
Hard parses:每秒产生的硬解析次数，硬解析过高，就可能说明你绑定使用的不好，也可能是共享池设置不合理。这时候可以启用参数cursor_sharing=similar|force，该参数默认值为exact。但该参数设置为similar时，存在bug，可能导致执行计划的不优。
Sorts:每秒产生的排序次数。
Logons:每秒登陆的次数。
Executes:每秒执行次数。如果只看这个参数没有太大的意义，可以结合前面其他的参数一起来考虑。结合Parses/ Executes来考虑，我们就可以知道解析的频繁程度。Hard parses/Executes就可以看出硬解析的情况是否过度。
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:平均每次排序操作的行数。   1、报表头信息
/* 报表头信息，数据库实例相关信息，包括数据库名称、ID、版本号及主机明等信息。
   另外，重点还需要关注一下报告产生的时间跨度（在这里是14分钟），以及并发数（在这里是272）。
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   说在前面，很容易被忽略的几个点：在读报告的时候，我们首先需要看清楚，留意3个内容，这份报告所对应的数据库版本，cluster方式，以及报告的时间段。尤其需要注意的就是时间段，脱离了时间段的statspck将是毫无意义的，甚至会得出错误的结果。

YAPP方法：
传统的优化数据库的指标是各种命中率的统计，并以命中率作为优化的目标。随着系统和业务模式的发展， 这种优化的方法已经过时，YAPP方法由此诞生。YAPP方法的最终目标就是缩短response time。
Response Time = Service Time + Wait Time
Service Time = CPU Parse + CPU Recursive + CPU Other
这里的CPU Other可以理解为是SQL语句的执行时间（包括逻辑读等）。
CPU ther = CPU used by this session - parse time cpu - recursive cpu usage
所以优化的最终目标定位在Service Time 和 Wait Time上：
1）当性能问题在Service Time上时，由上面的公式可以看到，解决问题的方向就是SQL语句的优化上面。2）如果性能问题在Wait Time上，则解决的方向就需要找到具体的等待事件了。如果等待时间在整个响应时间中占较大的比例，并且主要是块读取相关的等待时，下一步就是找出哪些SQL造成了过多的物理读，可以查看statspack报告中的SQL ordered by Reads部分。

对于Service Time的问题，可以具体分析其中几种时间的比重：
CPU ther = CPU used by this session - parse time cpu - recursive cpu usage
1）如果CPU Other（执行时间）在整个响应时间中占较大的比例，那么下一步就是找出那些造成了最多逻辑IO的SQL语句，可以从statspack报告的SQL ordered by Gets部分找到。
2）如果parse time cpu（分析时间）在整个响应时间中占较大的比例，那么下一步就是查找哪些SQL分析过多，这在statspack报告中在SQL ordered by Parse Calls中列出。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22578826/viewspace-665095/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22578826/viewspace-665095/