对wait events 和statistics 的理解小记

buffer busy waits

说明buffer cache中有一些buffers被多个进程尝试同时访问。
查看V$WAITSTAT观察各种类型buffer wait的统计信息。
SELECT class, count FROM V$WAITSTAT WHERE count > 0 ORDER BY count DESC;

也可以查看V$SESSION_WAIT观察当前buffer wait信息,其中P1-FILE_ID, P2- BLOCK_ID,
再通过DBA_EXTENTS查找哪些SEGMENT被争用。
Select * from v$session_wait where event=’buffer busy waits’
SELECT segment_owner, segment_name
  FROM DBA_EXTENTS
 WHERE file_id = <&p1>
   AND <&p2> BETWEEN block_id AND block_id + blocks - 1;

对于segment header争用：
1、很可能是freelist的争用，LMT中的ASSM可以解决问题。
2、如果不能使用ASSM,可以增加FREELIST数量，还不行就使用FREELIST GROUP
查看segment 的freelist 情况：
SELECT SEGMENT_NAME, FREELISTS
  FROM DBA_SEGMENTS
 WHERE SEGMENT_NAME = segment name
   AND SEGMENT_TYPE = segment type;

对于data block争用：
1、优化sql，避免使用选择性差的index
2、使用LMT中的ASSM，或增加FREELIST避免多个进程同时插入数据到相同的块。

对于undo header争用：
使用automatic undo 管理，或者增加rollback segments

对于undo block争用：
使用automatic undo 管理，或者增大rollback segments size

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

free buffer waits

发现找不到free buffer，而通知dbwr 将脏数据写入磁盘，以获得free buffer。
形成等待dbwr完成的因素有：
1、 IO慢，异步磁盘，使用raw device
2、 等待某些资源，比如latches
3、 Buffer cache 太小，使得dbwr花费很长的时间用来写脏数据
4、 Buffer cache 太大，使得dbwr没有能力写出足够的脏数据来满足需求。

检查哪里阻塞了DBWR
1、 检查V$FILESTAT看哪里发生了最多的write操作
2、 检查操作系统的IO情况
3、 检查CACHE是否太小，查看buffer cache hit ratio是否很低，使用V$DB_CACHE_ADVICE 判断是否应该增大CACHE
4、 如果CACHE足够大，IO也没有问题，则考虑使用异步IO，或者多个DBWR进程，以满足负载
调整DB_WRITER_PROCESSES参数（from DBW0 to DBW9 and from DBWa to DBWj），适用于多个CPU(at least one db writer for every 8 CPUs) or multiple processor groups (at least as many db writers as processor groups)
5、 DBWR_IO_SLAVES 

关于LATCH FREE

LATCH FREE

1、 检查在等待什么类型的LATCH,比如shared pool latch, cache buffer LRU chain
检查V$SESSION_WAIT
P1-Address of latch  p2- Latch number  p3- 休眠等待次数
SELECT n.name, SUM(w.p3) Sleeps
  FROM V$SESSION_WAIT w, V$LATCHNAME n
 WHERE w.event = `latch free'
   AND w.p2 = n.latch#
 GROUP BY n.name;

2、 检查LATCH对应的资源使用情况，比如library cache latch竞争严重，则检查the hard and soft parse rates
3、 检查存在LATCH竞争的SESSION所执行的SQL，是否需要优化。

Shared Pool and Library Cache Latch Contention

主要问题出在parse
1、 Unshared SQL
手工检查那些只有一次执行的SQL是否相似：
SELECT sql_text  FROM V$SQLAREA
 WHERE executions < 4  ORDER BY sql_text;
或者:
SELECT SUBSTR(sql_text,1, 60), COUNT(*)
  FROM V$SQLAREA
 WHERE executions < 4
 GROUP BY SUBSTR(sql_text, 1, 60)
 HAVING COUNT(*) > 1;

2、 Reparsed Sharable SQL
SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS
FROM V$SQLAREA
ORDER BY PARSE_CALLS;
当PARSE_CALLS和EXECUTIONS相近的时候，说明进行了REPARSE。优化这些SQL

3、 By Session
检查是否某个session执行了很多的parse。最好结合时间，看parse率
SELECT pa.sid, pa.value "Hard Parses", ex.value "Execute Count"
  FROM v$sesstat pa, v$sesstat ex
 WHERE pa.sid=ex.sid
   AND pa.statistic#=(select statistic#
       FROM v$statname where name='parse count (hard)')
   AND ex.statistic#=(select statistic#
       FROM v$statname where name='execute count')
   AND pa.value>0;

cache buffer lru chain

保护buffers在cache中的链，将buffer增加，移动，移出list时，必须要事先得到这个latch。
由大量buffer输入输出引起，比如低效的SQL重复访问不合适的index (large index range scans) or many full table scans。读Buffer会引起buffer在LUR中的移动。
查看Statements with very high logical I/O or physical I/O, using unselective indexes
或者CACHE太小，DBWR不能及时写出脏数据，而使前台进程花费很长的时间保持着latch去寻找free buffer。

cache buffers chains

用来当从buffer cache中查找、增加、删除buffer时获得。体现为某些数据块被严重争用，即热点块。

cache buffer lru chain latch 和cache buffers chains latch的区别：

关键点在于他们保护的数据结构不同：前者保护LRU chain pointer。LRU chain 用来发现free buffers，移动热buffer到MRU端，并协助完成写脏数据和检查点等动作；后者用来保护hash chain。Hash chain 用来通过hash 算法（根据所在的文件和block id）访问cache在buffer中的blocks。

关于 REDO LOG

log buffer space

等待free space in the log buffer，因为你写redo到log buffer的速度比lgwr写入log file的快。

增大log buffer；如果足够大，检查logfile所在的磁盘是否存在IO竞争；

log file switch

log file switch (archiving needed)

归档目录满了；arch比lgwr写入速度慢，存在lgwr竞争；增加ARCn processes；如果远程归档，检查网络

log file switch (checkpoint incomplete)

检查dbwr是否过慢，考虑使用分布式IO；检查是否logfile过少，或过小

log file sync

当COMMIT/ROLLBACK时，必须等待lgwr将redo写入log file。

检查average time waited，如果很小，但是等待次数很多，说明存在过渡commit现象，而不是使用BATCH COMMIT(比如50rows/COMMIT).

如果average time waited很高，则：

1、 使用专用disk存放logfile，或降低该disk 其他IO；

2、 使用更快IO，比如switch from RAID 5 to RAID 1；

3、 使用raw device；

4、 Batch commit

5、 See if any activity can safely be done with NOLOGGING / UNRECOVERABLE options.

另外：

1、 若 dbwr 写数据块的时候发现需要先写入日志的时候，也会等待lgwr完成。这个时候也产生 log file sync

2、 Other sessions that have unwritten redo records don't wait unless they're also in the middle of COMMIT.

3、 An unnecessarily big log buffer causes high total log file sync time because LGWR has to scan (read) the log buffer(not all the buffer) every time. Scanning takes time, although it's faster than writing.

log file parallel write

参考：http://www.itpub.net/showthread.php?threadid=171809&pagenumber=

除了等待commit/rollback的返回信息，还可能发生在其他触发lgwr写的情况下如3秒、1/3、1MB、dbwr写等

1、 Ensure tablespaces are NOT left in HOT BACKUP mode longer than needed.

Tablespaces in HOT BACKUP mode cause more redo to be generated for each change which can vastly increase the rate of redo generarion.

2、 其他同log file sync

关于IO，包括db file scattered/sequential/direct read and write

db file scattered read

表示通过multiblock read将data读入到很多不连续的内存中（根据file_id/block_id,通过hash算法分布于不同的地方），通常发生于fast full scan of index，或者full table scan。

查看V$SESSION_WAIT: P1-FILE_ID, P2-BLOCK_ID, P3-NUMBER OF BLCOKS(>1)

在大型数据库中，通常物理读的等待和idle wait都排在最前面。当然也要考虑是否有以下的特征：

Direct read wait(fts with parallel) / db file scattered read wait / Poor buffer cache hit ratio / 用户响应时间慢。

查看哪些session正在进行全表扫描：

SELECT s.sql_address, s.sql_hash_value,w.p1,w.p2

  FROM V$SESSION s, V$SESSION_WAIT w

 WHERE w.event LIKE 'db file%read' AND w.sid = s.sid ;

查看是哪个对象

SELECT segment_owner, segment_name

  FROM DBA_EXTENTS

 WHERE file_id = &p1 AND &p2 between block_id AND block_id + blocks - 1 ;

查看是哪个sql

SELECT sql_text

  FROM V$SQL

 WHERE HASH_VALUE=&sql_hash_value AND ADDRESS=&sql_address

db file sequential read

表示通过single block read将data读入到连续的内存中, 往往通过索引.

查看V$SESSION_WAIT: P1-FILE_ID, P2-BLOCK_ID, P3-NUMBER OF BLCOKS(=1)

 

direct path read and direct path read (lob)

将数据从disk中直接读到PGA中，而绕过SGA. 通常发生在DSS或WH

起因：

1、 排序过大，不能在sort memory中完成，而转移到temp disk中。然后又读入，形成直接读。

2、 Parallel slaves 查询

3、 The server process is processing buffers faster than the I/O system can return the buffers. 表明了很大的IO load

解决：

1、 查询V$TEMPSEG_USAGE, 找出产生sort的sql；查询V$SESSTAT, 查看sort size的大小。

2、 调整sql；如果WORKAREA_SIZE_POLICY=MANUAL, 增大SORT_AREA_SIZE;

如果WORKAREA_SIZE_POLICY=AUTO, 增大PGA_AGGREGATE_TARGET

3、 如果table 被定义为很高的degree of parallelism，将会引导optimizer使用parallel slaves 进行full table scan direct path write

情况同read，等待直接从PGA中得到buffer，并写入磁盘。

Parse-Related Statistics

SELECT NAME, VALUE
  FROM V$SYSSTAT
 WHERE NAME IN (  'parse time cpu', 'parse time elapsed'
                , 'parse count (hard)', 'CPU used by this session' );

parse time CPU / parse time elapsed 应该接近1
说明消逝的时间都用来parse sql，而不是等待资源
parse time CPU / CPU used by this session  应该接近0
说明并没有花费很大比例的时间用来parse

SQL*Net

SQL*Net message from/to client
SQL*Net message from/to dblink
说明database process is waiting for acknowledgment from a database link or a client process。如果等待时间很长, 并伴随着响应时间慢的问题，说明the network or the middle-tier 可能是瓶颈。
SQL*Net message from client
网络瓶颈，或者客户进程的资源争用

rdbms ipc reply

This event is used to wait for a reply from one of the background processes.

CPU used by this session

V$SYSSTAT: 被所有session使用的cpu
V$SESSTAT：单独的，可以确定那个session使用了最多的cpu

checkpoint not completed
是 lgwr等待将被覆盖的 logfile 对应的 dirty buffer 被写入数据文件，也就是io 繁忙导致 dbwr写太慢。如果大量的IO只是一个短时期的高峰，增加日志组可以延缓这个周期，从而可能会解决这个问题；如果你的系统永远都处于高峰期？那恐怕你需要使用更高速的存储设备和更好的主机。

CPU TIME
过大说明：
1、过多地执行了某些sql
2、某些sql的执行计划发生变化导致cpu使用过多