ORACLE 常见的等待事件

log file sync

首先了解下两个等待事件的概念：
Log File Sync ：是从提交开始到提交结束的时间（也可称为提交响应时间）。
Log File Parallel Write ：是LGWR开始写Redo File到Redo File结束的时间。
明确了这一点，可以知道，Log file sync 包含了log file parallel write。所以，log file sync等待时间一出，必先看log file parallel write。

log file sync产生的原因：

1. log file parallel write等待时间过大从而导致了log file sync 等待时间过长。
   如果log file sync平均等待时间为和log file parallel write非常接近，那么问题就很明显了，Redo file I/O缓慢，拖慢了提交的过程
   解决办法：优化redo日志文件存储，使之存放在更快的磁盘上（可以将日志文件和数据文件分开放在不同的存储上），就可以减少这个等待事件的单次等待时间
2. 数据库服务器资源争用，CPU负载高
   Log File Sync的时间不止log file parallel write。服务器进程开始提交，到通知LGWR写Redo，LGWR写完Redo通知进程提交完毕，来回通知也是要消耗CPU的。除去来回通知 外，Commit还有增加SCN等等操作，如果log file sync和log file parallel write差距很大，证明I/O没有问题，但有可能是CPU资源紧张，导致进程和LGWR来回通知或其他的需要CPU的操作，得不到足够的CPU，因而产 生延迟。

具体解决办法：

1. 优化redo日志文件存储，使之存放在更快的磁盘上（可以将日志文件和数据文件分开放在不同的存储上），就可以减少这个等待事件的单次等待时间
2. 加大日志缓冲区（log buffer）。如果数据文件的I/O性能有问题，平均单块读的等待时间偏长，那么通过加大db cache来减少I/O总次数，从而达到优化I/O的效果。加大日志缓存区的原理也是一样的，这样可以使日志缓存中的存储更多的redo日志数据，从而减少由于redo日志缓存区不足而产生lgwr写操作的数量，使平均每次写入redo日志文件的redo字节数增加，从而减少redo的I/O次数，进而达到优化log file sync等待事件的目的。
3. 减少提交的次数。如果提交过于频繁，那么无论怎么优化都无法彻底解决问题。通过加大一次提交记录的数量，减少提交批次，可以有效地减少log file sync等待时间。采用此方法就意味着需要对应进行较大的调整，甚至要对应用架构做出修改，这种修改的代价将十分巨大。
4. 适当使用NOLOGGING/UNRECOVERABLE等选项

direct path read

direct path read(直接路径读)：直接路径读是违反传统的数据读取方式的，指服务器进程直接读取数据文件到PGA的内存，不经过buffer cache。传统的数据库读取的方式是先在内存中搜索，如果搜索不到数据，那么就在把数据从磁盘读到内存中，然后PGA再中SGA中获取数据，这样数据就缓存到内存中了，下次再次访问的时候，就可以直接从SGA中获取，不用再进行物理读。
官方说明介绍：When a session is reading buffers from disk directly into the PGA (opposed to the buffer cache in SGA), it waits on this event. If the I/O subsystem does not support asynchronous I/Os, then each wait corresponds to a physical read request.
If the I/O subsystem supports asynchronous I/O, then the process is able to overlap issuing read requests with processing the blocks existing in the PGA. When the process attempts to access a block in the PGA that has not yet been read from disk, it then issues a wait call and updates the statistics for this event. Hence, the number of waits is not necessarily the same as the number of read requests (unlike db file scattered read and db file sequential read).
Check the following V$SESSION_WAIT parameter columns:
P1: File_id for the read call
P2: Start block_id for the read call
P3: Number of blocks in the read call

DirectPath Reads 说明
在oracle 11g以前的版本中，如果对大表进行全表扫描，wait event是：db file scattered read；
在11g中，如果对大表进行全表扫描，wait event是：direct path read；
即在11g中，大表全表扫描是将数据块直接读入会话的pga区域。
在11g中，大表全表扫描时数据块不经过sga而直接进pga，这样会造成每次进行大表全表扫描，物理读都是很大，
而在10g中，由于全表扫描的数据块在sga中已经存在，所以执行全表扫描时，它的物理读为0。
但是这里主要是Oracle在优化策略上的进步，即假定大表频繁全表扫描这种现象，在生产库上不会太多，
通过把数据直接读入pga，进而减少了cachebuffer的繁忙交换程度，提高了cachebuffer的使用效率.

direct path read具备更多的优势：

1. 减少了对栓latch的使用，避免可能的栓争用
2. 物理IO的大小不再取决于buffer_cache中所存在的块；试想某个8个块的extent中1,3,5,7号块在高速缓存中，
   而2,4,6,8块没有被缓存，传统的方式在读取该extent时将会是对2,4,6,8块进行4次db file sequential read,
   这是一种十分可怕的状况，其效率往往要比单次读取这个区间的所有8个块还要低得多，
   虽然Oracle为了避免这种情况总是尽可能的不缓存大表的块(读入后总是放在队列最冷的一端);
   而direct path read则可以完全避免这类问题，尽可能地单次读入更多的物理块。

direct path read也会引入一些缺点：

1. 在直接路径读取某段前需要对该对象进行一次段级的检查点(A segmentcheckpoint).
2. 可能导致重复的延迟块清除操作。
3. 如果全表扫描不可避免，而且频繁，会导致IO问题，进而引起宕库。

当然对于小表来说，Oracle允许通过Buffer Cache来进行全表扫描，因为这可能更快，也对性能影响不大。
小表受到隐含参数：_small_table_threshold 影响。如果表大于 5 倍的小表限制，则自动会使用DPR替代FTS。
可以设置初始化参数： _serial_direct_read 来禁用串行直接路径读。
当然，Oracle通过一个内部的限制，来决定执行DPR的阈值。
可以通过设置10949事件屏蔽这个特性，返回到Oracle 11g之前的模式上：
alter session set events ‘10949 trace name context forever, level 1’;
还有一个参数 _very_large_object_threshold 用于设定（MB单位）使用DPR方式的上限，这个参数需要结合10949事件共同发挥作用。
10949 事件设置任何一个级别都将禁用DPR的方式执行FTS，但是仅限于小于 5 倍 BUFFER Cache的数据表，
同时，如果一个表的大小大于 0.8 倍的 _very_large_object_threshold 设置，也会执行DPR。
这些限定的目标在于：
对于大表的全表扫描，必须通过Direct Path Read方式执行，以减少对于Buffer Cache的冲击和性能影响。
但是我们可以通过参数调整来决定执行DPR的上限和下限。

找出产生direct path read的SQL

select sql_id, count(1)
  from v$session
 where event = 'direct path read'
 group by sql_id;

select *
  from v$sql
 where sql_id in (select distinct sql_id
                    from v$session
                   where event = 'direct path read');