Oracle常见等待事件

buffer busy waits

解释：

1. 一个会话试图修改一个数据块，但是这数据块正在被另一个会话修改；
2. 一个会话需要读取一个数据块，但是这数据块正在被另一个会话读取到内存中。

# 问题定位
SQL> col name for a30
SQL> col parameter1 for a30
SQL> col parameter2 for a30
SQL> col parameter3 for a30
SQL> 
SQL> select name, parameter1, parameter2, parameter3 from v$event_name where name = 'buffer busy waits';

NAME                           PARAMETER1                     PARAMETER2                     PARAMETER3
------------------------------ ------------------------------ ------------------------------ ------------------------------
buffer busy waits              file#                          block#                         class#
# file#：表示等待访问数据块所在的文件id号
# block#：表示等待访问的数据块号
# class#：原因码
# 如果等待处于字段头部，应该增加自由列表的组数，或者增加pctused到pctfree之间的距离；
# 如果等待处于回滚段头部块，可以增加回滚段来解决缓冲区的问题；
# 如果等待处于回滚段非头部块上，就需要降低驱动一致读取的表中的数据密度，或者增大DB_CACHE_SIZE；
# 如果等待处于数据块，可以将数据移动到另一个数据块以避开这个“热”数据块，或者增加表中的自由列表或使用LMT表空间。

# 问题解决：
select segment_type, owner || '.' || segment_name from dba_extents where file_id = file_id and block_id between block_id and block_id + blocks - 1;

free buffer waits

解释：

1. 当一个会话将数据块从磁盘读取到内存时，需要再内存中找到空闲空间来存放数据块，当内存中没有空闲空间时，就会产生这个等待事件；
2. 会话在做一次性读时，需要构造数据块在某个时刻的前镜像，此时需要申请内存来存放这些新构造的数据块，如果内存中无法找到这样的内存块，也会发生这个等待事件。

原因：

1. data buffer太小，导致空闲空间不够；
2. 内存中的脏数据太多，DBWR无法及时将这些脏数据写到磁盘中以释放空间。

library cache lock

解释：

1. alter table 或者 alter package/procedure 以X模式持有 library cache lock，造成堵塞。

原因：

1. 错误的用户名密码；
2. 正在执行收集统计信息；
3. 错误的sql解析；
4. bug。

library cache pin

解释：

1. 管理library cache并发访问。

原因：

1. 如果oracle要对pl/sql或者视图这样的对象做重新编译，需要将这些对象pin到共享池中，如果这些对象被某用户特有，就会产生一个library cache pin的等待。

db file scattered read

原因：

1. 全表扫描；
2. 索引快速扫描。

direct path read

解释：

1. 服务器进程直接读取数据文件，不会读取buffer cache。
   原因：
2. 磁盘有大量的临时数据产生，比如排序、并行执行等；
3. PGA中空闲空间不足。

direct path write

解释：

1. 直接将数据从PGA中写入到磁盘文件上，而不经过SGA。

原因：

1. 使用临时段空间排序（内存不足）；
2. 数据的直接加载（使用append方式加载数据）；
3. 并行DML操作。

log buffer space

解释：

1. buffer没有可用空间来存放新产生的redo log数据时。

原因：

1. 增加redo buffer的大小；
2. 增加磁盘的I/O性能。

log file sequential read

原因：

1. 当进程等待从redo文件中读入块是会产生该等待事件；
2. arch进程在读取redo文件时会产生该等待事件。

log file switch（archiving need）

解释：

1. 归档模式下，这个等待事件发生在在线日志切换，需要切换的在线日志还没有被归档进程归档完毕时。

原因：

1. arch归档进程异常。

log file switch（checkpoint incomplete）

解释：

1. 当一个在线日志切换到下一个在线日志时，必须保证要切换到的在线日志上的记录信息被写到磁盘上。

redo三种状态：

1. active：这个日志上面保护的信息还没有checkpoint；
2. inactive：这个日志上面保护的信息已经完成checkpoint；
3. current：当前的日志

oracle实例恢复时，会使用状态为current和active的日志进行实例恢复。
原因：

1. 日志文件太少或者日志组太少。

log file sync

原因：

1. 频繁提交或者rollback，检查应用是否有过多的短小事务，可以使用批处理进行缓解；
2. OS的IO缓慢，可将日志文件放在裸设备上或者绑定在raid 0或raid 10上；
3. 过大的日志缓冲区会允许lgwr进程懒惰，因为log buffer中的数据量无法达到写入的条件，导致更多重做条目堆积在日志缓冲区中。当事务提交或者3s醒来时，lgwr才会把所有数据写入到redo log file中，由于数据很多，lgwr进程需要用更多的事件等待redo写完毕。这种情况，可以调小参数_log_io_size参数，默认值为1/3或者1MB，取两者之间较小的值；
4. cpu负载高；
5. RAC私有网络性能差，导致LMS同步commit scn慢。

判断：

1. 如果log file sync等待事件很高，但log file parallel write的等待时间并不高，这意味着log file sync的原因并不是缓慢的日志I/O，而是应用程序过多的提交造成；
2. 如果log file sync的等待时间和log file parallel write等待时间基本相同，说明是I/O问题造成的log file sync等待事件；
3. 检查哪些表可以使用nologging，会较少redo产生量；
4. 检查redo log file是否足够大，确保redo log file 每隔15-20分钟就切换一次。

cursor: pin S wait on X

解释：
实际上替代了cursor的library cache pin；

1. cursor: pin S --> 代表执行（share pin）；
2. cursor: pin X --> 代表解析 ；
3. cursor: pin S wait on --> 代表执行正在等待解析操作。

共享池中的等待事件：

1. 硬解析： latch: share pool，硬解析需要所有类型的mutex，包括library cache: mutex, cursor: pin, hash table, cursor parent；
2. 软解析：library cache: mutex, cursor: pin, hash table, cursor parent；
3. 软软解析（将子游标堆6[保存执行计划信息]，缓存在pga中）： 两次cursor: pin S和一次library cache: mutex X；
4. sql版本过多：library cache和hash table类型等待；

分析：

1. 如果只有cursor: pin类型和library cache: mutex型竞争激烈，是软解析问题；
2. 如果还有其他类mutex型等待，则是软解析导致；
3. 如果还有shared pool latch竞争激烈，一定是硬解析过多，由于大量进程同时请求从共享池中分配内存导致；
4. 如果library cache lock和hash table型等待事件同时出现，则是版本过多的硬解析；
5. 如果只有hash table类型等待而没有library cache lock，则是版本过多的父游标有很多并发的软解析。

解决方法：

1. 硬解析异常等待处理：使用绑定变量，cursor sharing=true, 禁用adaptive cursor sharing， 配置充足的shared pool，若v$sqlarea_sql_text列中相似的sql很多，说明未使用绑定变量；
2. 软解析争用处理：调整session cached cursors参数，使软解析变成软软解析；
3. 软软解析导致争用处理： cursor pin: s, 使用提示符/**/改变sql hash值，将1条sql变为多条减少征用；应用层缓存游标，实现一次解析，多次执行。

tip： 导致high version count原因：
1. owner不同
2. 表统计信息发生变化
3. 系统环境统计信息发生变化
4. 绑定变量长度变大会导致BIND_MISMATCH

latch: cache buffer chains

原因：

1. buffer cache太少：也说明sql效率低，较多的逻辑读意味着较多的latch get操作，从而增加了锁存器争用。多个进程同时扫描或者大范围的索引或表时，可能会发生大量的cache buffer chains锁存器征用。
2. 热块争用：多个会话重复访问一个或多个由一个子cache buffer chains锁存器保护的块时，就会产生热块争用。当多个会话争用cache buffer chains锁存器时，找出是否有热块的最好方法是检查latch free等待事件的pl raw参数值。

select sid,p1raw,p2,p3,seconds_in_wait,wait_time,state from v$session_wait where event='latch: cache buffers chains' order by 3,2;

# 注意替换P1RAW
# 查看关联热块对象
select a.hladdr, a.file#, a.dbablk, a.tch, a.obj, b.object_name from x$bh a, dba_objects b where (a.obj = b.object_id or a.obj = b.data_object_id) and a.hladdr = 'P1RAW' union select hladdr, file#, dbablk, tch, obj, null from x$bh where obj in (select obj from x$bh where hladdr = 'P1RAW' minus select object_id from dba_objects minus select data_object_id from dba_objects) and hladdr = 'P1RAW' order by 4;

最后

用理论指导实践，实际生产中某个问题可能并不是单一地从某个等待事件去分析，而应该用统一的思维方式辩证的去解决。