SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
create table wxh_tbd as select * from dba_objects;
create index t on wxh_tbd(object_id);
select dbms_rowid.ROWID_RELATIVE_FNO(rowid) fn, dbms_rowid.rowid_block_number(rowid) bl, wxh_tbd.object_id from wxh_tbd where rownum<100;
这几步的工作是创建了一个表,在object_id上建立一个索引,最后一个查询是想找到几条记录位于一个数据块上,方便我们做实验。
实验1)都是查询的情况,看看有没有buffer busy wait.
分别在三个会话里运行如下查询,object_id 为198,194,199的都位于同一个数据块,在会话一中开启10046:
declare
c number;
begin
for i in 1 ..600000 loop
select count(*) into c from wxh_tbd where object_id=198;
end loop;
end;
/
declare
c number;
begin
for i in 1 ..600000 loop
select count(*) into c from wxh_tbd where object_id=194;
end loop;
end;
/
declare
c number;
begin
for i in 1 ..600000 loop
select count(*) into c from wxh_tbd where object_id=199;
end loop;
end;
/
然后查看会话一中跟踪文件的等待时间:
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
latch: cache buffers chains 257 0.02 0.19
latch: library cache 13 0.00 0.01
latch: library cache pin 6 0.00 0.00
看到了吧,惊讶吧?根本没有buffer busy wait.
实验2)有查询、有DML的情况,看看有没有buffer busy wait.两个会话都开启10046
declare
c number;
begin
for i in 1 ..600000 loop
select count(*) into c from wxh_tbd where object_id=199;
end loop;
end;
/
begin
for i in 1 ..400000 loop
UPDATE wxh_tbd SET object_id=194 where object_id=194;
end loop;
end;
/
两个会话的等待时间分别如下:
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
latch: cache buffers chains 226 0.00 0.05
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
rdbms ipc reply 229 0.00 0.02
log file switch completion 9 0.99 1.46
log buffer space 2 0.10 0.21
latch: cache buffers chains 163 0.00 0.03
log file switch (checkpoint incomplete) 2 0.08 0.12
也没有看到buffer busy wait.
实验3)两个会话都是DML的情况,看看有没有buffer busy wait事件,会话1开启10046。
begin
for i in 1 ..400000 loop
UPDATE wxh_tbd SET object_id=194 where object_id=194;
end loop;
end;
/
begin
for i in 1 ..400000 loop
UPDATE wxh_tbd SET object_id=198 where object_id=198;
end loop;
end;
/
会话1的等待事件如下:
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
undo segment extension 34 0.00 0.00
buffer busy waits 20889 0.99 4.31
log file switch completion 5 1.00 2.11
log file switch (checkpoint incomplete) 5 0.99 1.22
latch: cache buffers chains 151 0.00 0.02
latch: library cache pin 9 0.00 0.00
latch: library cache 13 0.00 0.00
log buffer space 3 0.11 0.32
********************************************************************************
终于看到了等待事件buffer busy waits.
先大概说下读取数据块的过程:
读取数据块的时候,如果数据块已经在内存里了,那么首先需要根据数据块的DBA即地址,HASH出它在哪一个桶(hash bucket)里,cache buffer chains 把桶里的数据块串了起来,如果想要访问数据块,需要获得cache buffer chain latch,这个latch是用来保护数据块的,获得latch以后,pin数据块。
buffer busy waits就发生在pin数据块的过程里,从实验来看,读读,读写都不会有冲突,也就是都兼容。可是写写的话,就会出现buffer busy waits。
几乎大多数的人都误解了,以为buffer busy waits事件表明读的太频繁了,其实不是。而是写(修改)的太频繁了。
我想之所以有误解,是因为10G以前,把一个等待事件read by other session也划入到了buffer busy waits里,read by other session的意思是,多个session并发请求相同的数据块,但因该数据块不在buffer_cache中而必须从磁盘读取,处理这种情况,oracle会只让其中一个sesion进行磁盘读取,此时其它session等待块从磁盘上读取进buffer_cache而抛出read by other session等待事件。10G以后,read by other session被单独了出来,buffer busy waits变的纯粹,就是上面我说的那种情况了。
Buffer Busy Waits是怎么产生的?
作为一个Oracle Dba,如果你从未遇到过Buffer Busy Waits等待,那么你算不上一个真正的Oracle Dba。Buffer Busy Waits是Oracle 数据库非常常见的一个等待,特别是在并发写比较频繁的环境里。说起为什么会产生这个等待,首先要描述下,Oracle读写数据块的过程:
1)首先依据数据块地址计算出(HASH算法)数据块所在的HASH BUCKET。
2)根据桶的编号,计算出保护这个桶的CBC LATCH,然后申请CBC LATCH,找寻数据块在不在桶里(内存里),我们这里假设在内存里。
3)读取/修改数据块。
4)释放CBC LATCH。
以上的描述看似是非常通畅,但是存在一个问题,CBC LATCH的持有是排他的(我们暂时不考虑复杂情况:共享LATCH的持有情况),如果在排他持有CBC LATCH的情况下,读取数据块内容,那么这个LATCH的持有时间就会比较长,因为相对于LATCH的获取和释放这种CPU原子操作,读取数据块的内容是非常耗时的,因此在持有CBC LATCH的情况下,读取数据块,对于读写频繁的数据库/块,那么势必会造成CBC LATCH的争用。为了解决这个问题,Oracle引入了buffer pin(buffer lock)的功能。
我们有必要对读取数据块的内容重新做下描述,大致步骤如下:
1)首先需要判断数据块所在的HASH BUCKET。
2)然后申请CBC LATCH,定位到数据块。
3)以S/X模式获取数据块的buffer pin/lock。(读取获得s模式,修改获得x模式,S和S模式具有兼容性,S和X、X和X模式不具有兼容性)。
4)释放CBC LATCH
5)在PIN的保护下,读取/修改数据块。
6)获得CBC LATCH。
7)释放(UNPIN)BUFFER PIN(BUFFER LOCK)。
8)释放CBC LATCH。
看似步骤复杂了,CBC LATCH获取/释放了两次,可是却大大的提高了并发度。上面描述的步骤里,持有CBC LATCH的目的变得单纯,只是为了修改BUFFER的PIN模式,然后依靠PIN的模式兼容性来保护数据块,例如:S和S模式的PIN是兼容的,可以并发的读取,S和X模式是不兼容的,后来的会话需要产生等待。
虽然LATCH的持有是排他的,但是这个时间极端,引起争用的可能性不大,如果大家都是来读数据块的,那么BUFFER LOCK的S模式之间都是具有共享性的,不会产生争用。但是同一个时刻,如果一个进程以S模式持有了数据块的BUFFER LOCK,另一个进程想以X模式持有,那么就会出现争用,因为道理很简单,S模式的BUFFER LOCK和X模式的BUFFER LOCK不兼容。同理,两个同时欲修改同一个数据块的进程,也会遭遇BUFFER LOCK冲突.这个冲突以ORACLE 等待事件表示出来就是Buffer Busy Waits,也就是说Buffer Busy Waits等待的本质是buffer lock的争用导致的。
我们平时经常说读不阻塞写,写不阻塞读,那是在物理的数据块级别,在内存里,读写/写写在同一个时刻都是互相阻塞的。只有读读不阻塞。
为了方便理解,上面很多步骤做了简化,下面对某些点做些补充:
1)一旦你PIN住了一个数据块,不需要立即去UNPIN(移除PIN)它。ORACLE认为你的本次调用后还有可能去访问这个数据块,因此保留了PIN,直到本次调用结束再UNPIN。
2)Oracle在对唯一索引/undo块/唯一索引的回表/索引root、branch块的设计上,在访问(读取)的时候,获取的是共享的CBC LATCH,不需要去PIN数据块,在持有共享CBC LATCH的情况下读取数据块。可能的原因是这些块修改的可能性比较小,因此Oracle单独的采用这种机制。因此对于普通数据块的读取都是需要获取2次CBC LACTH,而对于这种特殊的数据块,只获取一次共享CBC LATCH就OK 了。
3)我们上面所说的情况都是在数据块已经存在在内存里的情况。如果数据块不在内存,有可能会产生READ BY OTHER SESSION争用等待。有时间我们再看这个等待的原因。
4)上面描述只符合10G后的版本。在10G前读读也会产生BUFFER BUSY WAITS,10G后把这方面的BUFFER BUSY WAITS归到了READ BY OTHER SESSION等待里。
5)上面的描述基本都采用了数据块这个词,更准确的表达应该是buffer block。
buffer busy waits 缓冲区忙等待 (BUFFER热块)
这是为了等待一个以非共享方式使用的缓冲区,或者正在被读入缓冲存储器的缓冲区。缓冲区忙等待不应大于1%。检查缓冲等待统计部分(或V$WAITSTAT):
A、如果等待处于字段头部,应增加自由列表(freelist)的组数,或者增加pctused到pctfree之间的距离。
B、如果等待处于回退段(undo)头部块,可以通过增加回滚段(rollback segment)来解决缓冲区的问题;
C、如果等待处于回退段(undo)非头部块上,就需要降低驱动一致读取的表中的数据密度,或者增大DB_CACHE_SIZE;
D、如果等待处于数据块,可以将数据移到另一数据块以避开这个"热"数据块、增加表中的自由列表或使用LMT表空间;
E、如果等待处于索引块,应该重建索引、分割索引或使用反向键索引。
为了防止与数据块相关的缓冲忙等待,也可以使用较小的块:在这种情况下,单个块中的记录就较少,所以这个块就不是那么"繁忙"。在执行DML(插入/更新 /删除)时,Oracle DBWR就向块中写入信息,包括所有对块状态"感兴趣"的用户(感兴趣的事务表,ITL)。为减少这一区域的等待,可以增加initrans,这样会在块中创建空间,从而使你能够使用多个ITL槽。你也可以增加该块所在表中的pctfree(当根据指定的initrans 建立的槽数量不足时,这样可以使ITL 信息数量达到maxtrans 指定的数量)。