oracle 常见等待事件

常见的等待事件

v$session_wait:当前的等待事件/与等待事件相对应的资源的相关信息，从而确定出产生瓶颈的类型及其对象。

v$session_wait的p1、p2、p3告诉我们等待事件的具体含义，根据事件不同其内容也不相同，下面就一些常见的等待
事件如何处理以及如何定位热点对象和阻塞会话作一些介绍.

<1> db file scattered read DB 文件分散读取

文件分散读取：通常显示与全表扫描相关的等待。当数据库进行全表扫时，基于性能的考虑，数据会分散(scattered)读
入Buffer Cache。如果这个等待事件比较显著，如果这个数目很大，就表明该表找不到索引，或者只能找到有限的索引。
尽管在特定条件下执行全表扫描可能比索引扫描更有效，但如果出现这种等待时，最好检查一下这些全表扫描是否必要。
因为全表扫描被置于LRU(Least Recently Used，最近最少适用)列表的冷端（cold end），所以应尽量存储较小的表，以
避免一次又一次地重复读取它们。

db file scattered read等待事件：是由于多数据块读操作产生的，当我们检索数据时从磁盘上读数据到内存中，一次
I/O读取多个数据块，而数据块在内存中是分散分布并不是连续的，当数据块读取到内存这个过程中会产生
"db file scattered read"事件。
多数据块读场景：FTS(full table scan)全表扫描
IFFS (index fast full scan) 快速索引全扫描：把索引链切割成很多份，多块并行读取

解决方法：全表扫描-调整SQL，增加索引，加快磁盘读写速度

当这个等待事件比较显著时，可以结合v$session_longops动态性能视图来进行诊断，该视图中记录了长时间（运行时
间超过6秒的）运行的事物，可能很多是全表扫描操作（不管怎样，这部分信息都是值得我们注意的）。

该类事件的p1text=file#,p1是file_id，p2是block_id,通过dba_extents即可确定出热点对象(表或索引)
select sid,event,wait_class,p1,p1text,p2,p2text,p3,p3text from v$session_wait where event like ‘%scattered%’;

select owner,segment_name,segment_type from dba_extents
where file_id = &file_id and &block_id between block_id and block_id + &blocks - 1;

<2> db file sequential read DB 文件顺序读取

这一事件通常显示单个块的读取(如索引读取)。这种等待的数目很多时，可能显示表的连接顺序不佳，或者不加选择地
进行索引。对于大量事务处理、调整良好的系统，这一数值大多是很正常的，但在某些情况下，它可能暗示着系统中存在问
题。你应当将这一等待统计量与Statspack 报告中的已知问题（如效率较低的SQL）联系起来。检查索引扫描，以保证每个
扫描都是必要的，并检查多表连接的连接顺序。DB_CACHE_SIZE 也是这些等待出现频率的决定因素。有问题的散列区域
（Hash-area）连接应当出现在PGA 内存中，但它们也会消耗大量内存，从而在顺序读取时导致大量等待。它们也可能以直
接路径读／写等待的形式出现。

db file sequential read等待事件：是由于数据块顺序读产生的，当数据块(索引块)从磁盘一个一个读到内存中时，在这
个过程中oracle会发生"db file sequential read"等待事件。
块顺序读场景：索引块顺序读、数据块顺序读、undo回滚构造一致性读、磁盘I/O瓶颈

一般来讲如果检索数据时走索引范围扫描INDEX RANGE SCAN就会发生数据块顺序读的现象，先读取一个索引块，根
据索引键值对应ROWID信息在去读ROWID所在的数据块，接下来继续找下一个索引块，在读对应的数据块，就这样一个一个
把数据块读取到内存中，这个过程中就会产生"db file sequential read"等待事件。
解决方法：调整SQL,增加buffer cache,加快磁盘读写速度
该类事件的p1text=file#,p1是file_id，p2是block_id,通过dba_extents即可确定出热点对象(表或索引)
select sid,event,wait_class,p1,p1text,p2,p2text,p3,p3text from v$session_wait where event like ‘%sequential%’;

select owner,segment_name,segment_type
from dba_extents
where file_id = &file_id
and &block_id between block_id and block_id + &blocks - 1;

<3> free buffer waits 释放缓冲区等待

free buffer waits 释放缓冲区等待：当一个session试图将一个block读入buffer cache，或者由于读一致需要构造cr的
block找不到free buffer而产生的等待。
这种等待表明系统正在等待内存中的缓冲，因为内存中已经没有可用的缓冲空间了。如果所有SQL 都得到了调优，这种
等待可能表示你需要增大DB_BUFFER_CACHE。释放缓冲区等待也可能表示不加选择的SQL 导致数据溢出了带有索引块的缓
冲存储器，没有为等待系统处理的特定语句留有缓冲区。这种情况通常表示正在执行相当多数量的DML（插入／更新／删除），
并且数据库书写器(DBWR)写的速度不够快，缓冲存储器可能充满了相同缓冲器的多个版本，从而导致效率非常低。为了解决
这个问题，可能需要考虑增加检查点(增大ckpt触发频率)、利用更多的DBWR 进程，或者增加物理磁盘的数量。
通常产生这个等待事件有以下原因：
a) buffer cache设置太小。
b) I/0写太慢，不能及时将Dirty block写到磁盘。
c) 烂SQL导致大量的物理读。
d) Dbwr进程太少
e) DML并发太高，写不赢
select sid,event,wait_class,p1,p1text,p2,p2text,p3,p3text from v$session_wait where event like ‘%free buffer waits%’;

buffer cache大小的设置
buffer cache大小由db_cache_size来设置，在OLTP系统中，对db_cache_size的设置，推荐大小为：
db_cache_size = SGA_MAX_SIZE/2 ~ SGA_MAX_SIZE*2/3
也可以使用advice来确认buffer cache的大小：
SQL> select size_for_estimate “Cache Size(MB)”,size_factor,buffers_for_estimate “Buffers”,
estd_physical_read_factor est_read_factor,estd_physical_reads estd_phy_read,
std_physical_read_time est_phy_read_t from v$d{b}_{c}ach{e}_{a}dvicewherename{=}^{\prime }DEFAUL{T}^{\prime }andbloc{k}_{s}ize=(selectvaluefromv$parameter where name=‘db_block_size’);

<4> buffer busy waits 缓冲区忙等待

这是为了等待一个以非共享方式使用的缓冲区，或者正在被读入缓冲存储器的缓冲区。缓冲区忙等待不应大于
百分之一。检查缓冲等待统计部分（或V$WAITSTAT）:
A、如果等待处于字段头部，应增加自由列表（freelist）的组数，或者增加pctused到pctfree之间的距离。
B、如果等待处于回退段（undo）头部块，可以通过增加回滚段(rollback segment)来解决缓冲区的问题；
C、如果等待处于回退段（undo）非头部块上，就需要降低驱动一致读取的表中的数据密度,或者增大DB_CACHE_SIZE；
D、如果等待处于数据块，可以将数据移到另一数据块以避开这个"热"数据块、增加表中的自由列表或使用LMT表空间；
E、如果等待处于索引块，应该重建索引、分割索引或使用反向键索引。

为了防止与数据块相关的缓冲忙等待，也可以使用较小的块：在这种情况下，单个块中的记录就较少，所以这个块就
不是那么"繁忙"。在执行DML(插入/更新/删除)时,OracleDBWR就向块中写入信息，包括所有对块状态"感兴趣"的用户(感
兴趣的事务表，ITL)。为了减少这一区域的等待，可以增加initrans，这样会在块中创建空间，从而使你能够使用多个ITL槽。
你也可以增加该块所在表中的pctfree(当根据指定的initrans 建立的槽数量不足时，这样可以使ITL 信息数量达到maxtrans
指定的数量）。

<5> 闩锁释放latch free

latch是一种低级排队机制(它们被准确地称为相互排斥机制),用于保护系统全局区域(SGA)中共享内存结构。latch 就像是一
种快速地被获取和释放的内存锁。latch 用于防止共享内存结构被多个用户同时访问。如果latch 不可用，就会记录latch 释放失
败。大多数latch 问题都与以下操作相关：不能使用绑定变量（库缓存latch）、重复生成问题（重复分配latch）、缓冲存储器竞
争问题（缓冲器存储LRU 链），以及缓冲存储器中的"热"块（缓冲存储器链）。也有一些latch 等待与bug（程序错误）有关，
如果怀疑是这种情况，可以检查MetaLink 上的bug 报告。Latch free等待事件三个参数：p1－latch的地址；p2－latch编号；
p3－请求次数

该事件的热点对象可通过以下语句查找，其中00000400837D7800值是v$session_wait中的P1RAW

x$bh中的字段Hladdr表示该blockbuffer在哪个cachebufferchainlatch上，可以通过v$latch_children定位哪些segment是热点块。

select a.hladdr, a.file#, a.dbablk, a.tch, a.obj, b.object_name
from xKaTeX parse error: Expected 'EOF', got '#' at position 136: …ct hladdr, file#̲, dbablk, tch, …bh
where obj in
(select obj from x$bh
where hladdr = ‘00000400837D7800’
minus
select object_id from dba_objects
minus
select data_object_id from dba_objects)
and hladdr = ‘00000400837D7800’
order by 4;

<6> 队列锁enqueue lock

Enqueue 队列锁 是 oracle中 主要的 并发访问控制 和锁的机制。耳熟能详的 TM table lock表锁 和 TX 事务锁都是enqueue 。
enqueue 最多可以有6个模式 ， enqueue lock被设计出来用于那些需要较长时间锁机制的场景。enqueue 的 6个 锁模式分别
为： X 、 SSX、 S、SX、SS、NULL。latch是为了串行访问机制， 而enqueue 更多是为了并发访问控制

enqueue lock 是一种保护共享资源的锁定机制。该锁定机制保护共享资源，如记录中的数据，以避免两个人在同一时间更
新同一数据。enqueue 包括一个排队机制，即FIFO(先进先出)排队机制。注意：Oracle 的latch 机制不是FIFO。Enqueue
等待通常指的是ST enqueue、HW enqueue、TX4 enqueue 和TM enqueue。

A) ST enqueue 用于空间管理和字典管理的表空间的分配。利用LMT，或者试图对区域进行预分配，或者至少使下一
个区域大于有问题的字典管理的表空间。
B) HW enqueue 与段的高水位标记一起使用；手动分配区域可以避免这一等待。
C) TX4 enqueue是最常见的enqueue 等待，通常是以下三个问题之一产生的结果:
[i] 第一个问题是唯一索引中的重复索引，需要执行提交（commit）/回滚（rollback）操作来释放enqueue。
[ii] 第二个问题是对同一位图索引段的多次更新。因为单个位图段可能包含多个行地址(rowid),所以当多个用户试图更新同
一段时,你需要执行提交或回滚操作,以释放enqueue。
[iii] 第三个问题，也是最可能发生的问题是多个用户同时更新同一个块。如果没有自由的ITL槽，就会发生块级锁定。通过增
大initrans 和/或maxtrans以允许使用多个ITL槽，或者增大表上的pctfree 值，就可以很轻松地避免这种情况。
D) TM enqueue 在DML 期间产生，以避免对受影响的对象使用DDL。如果有外来关键字，一定要对它们进行索引，以避免这种常见的锁定问题。

<7> log buffer space 日志缓冲空间

当会话由于日志缓冲区空间不足而无法将重做日志条目复制到日志缓冲区时，会话将在log buffer space事件上等待。LGWR负责写出重
做条目，腾出日志缓冲区空间。当日志缓冲(log buffer)写入重做日志(redo log)的速度比LGWR 的写入速度慢，或者是当日志转换(log switch)
太慢时，就会发生这种等待。为解决这个问题，可以增大日志文件的大小，或者增加日志缓冲器的大小，或者使用写入速度更快的磁盘。
甚至可以考虑使用固态磁盘，因为它们的速度很高。

<8> log file switch 日志文件转换

有两种情况：
A） log file switch (archiving needed)
当日志切换的时候由于日志组循环使用了一圈但日志归档还没有完成，通常是io有严重问题，可增大日志文件和增加日志组，调整
log_archive_max_processes参数
B）log file switch (checkpoint incomplete)
当日志切换的时候由于日志组循环使用了一圈但将被使用的日志组中的checkpoint还没有完成造成，通常是io有严重问题，可增大日
志文件和增加日志组

主要原因还是重做日志文件过小，所以解决方法是将重做日志文件的大小调整为足够大或者添加日志组。而且，使用Direct load operation
或nologging选项对减少重做数据的量也是有帮助的

SQL> select event#,name,parameter1,parameter2,parameter3 from v$event_name where name like ‘%log file switch%’;

EVENT# NAME PARAMETER1 PARAMETER2 PARAMETER3

---

111 log file switch (checkpoint incomplete)
112 log file switch (private strand flush incomplete)
113 log file switch (archiving needed)
115 log file switch completion
503 log file switch (clearing log file)
–private strand flush incomplete:使用的重做日志文件尚未完成对private strand的flush工作，等待这个工作结束
– log file switch completion: 三种等待现象在重做日志文件被循环使用的情况下，将生成许多重做数据，所以在尚未完成工作就重新使
用时发生。因此这些等待现象一向是与log file switch completion等待现象一起出现的。准确的说，服务器进程首先等待log file switch completion
事件。

<9> log file sync 日志文件同步

当用户commit的时候通知lgwr写日志但lwgr正忙，造成的可能原因是commit太频繁或者lgwr一次写日志时间太长（可能是因为一次
log io size 太大），可调整 _log_io_size，结合log_buffer,使得 (_log_io_size*db_block_size)*n = log_buffer,这样可避免和增大log_buffer
引起冲突;放置日志文件于高速磁盘上

<10> library cache pin

该事件通常是发生在先有会话在运行PL/SQL,VIEW,TYPES等object时,又有另外的会话执行
重新编译这些object,即先给对象加上了一个共享锁,然后又给它加排它锁,这样在加排它锁
的会话上就会出现这个等待。
P1,P2可与x$kglpn和x$kglob表相关

X$KGLOB(KernelGenericLibraryCacheManagerObject)X$KGLPN (Kernel Generic Library Cache Manager Object Pins)

– 查询X$KGLOB,可找到相关的object,其SQL语句如下(即把V$SESSION_WAIT中的P1raw与X$KGLOB中的KGLHDADR相关连)

select kglnaown,kglnaobj from X$KGLOBwhereKGLHDADR=(selectp1rawfromv$session_wait where event=‘library cache pin’)

– 查出引起该等待事件的阻塞者的sid

select sid from x$kglpn,v$session
where KGLPNHDL in
(select p1raw from v$sessio{n}_{w}aitwherewai{t}_{t}ime=0andeventlik{e}^{\prime }librarycachepinandKGLPNMOD<>0andv$session.saddr=x$kglpn.kglpnuse

– 查出阻塞者正执行的SQL语句
select sid,sql_text
from v$session,v$sqlarea
where v$session.sq{l}_{a}ddress=v$sqlarea.address
and sid=<阻塞者的sid>

这样,就可找到"library cache pin"等待的根源，从而解决由此引起的性能问题。

<11> library cache lock

该事件通常是由于执行多个DDL操作导致的,即在library cache object上添加一个排它锁
后,又从另一个会话给它添加一个排它锁,这样在第二个会话就会生成等待。可通过到基表
x$kgllk中查找其对应的对象。

– 查询引起该等待事件的阻塞者的sid、会话用户、锁住的对象

select b.sid,a.user_name,a.kglnaobj
from x$kgllka,v$session b
where a.kgllkhdl in
(select p1raw from v$session_wait
where wait_time=0 and event = ‘library cache lock’)
and a.kgllkmod <> 0
and b.saddr=a.kgllkuse

当然也可以直接从v$locked_objects中查看，但没有上面语句直观

根据sid可以到v$process中查出pid，然后将其kill或者其它处理