Buffer bysy wait By the select

要描述下修改行的过程，便于理解buffer busy wait。

1.为了查找要修改的行相应的块存在的位置，请求cache buffer chains latch。

2.对载入块的缓冲区请求buffer block，释放cache buffer chains latch。

3.请求tx锁，有则修改行，未得到tx锁，则释放buffer lock。

4.修改行释放buffer block。

---测试select引起的buffer lock

---创建测试环境

create tablespace bfw_tablespace datafile'/opt/app/oracle/oradata/liqian/bfw_tablespace.dbf'

size 50M autoextend on

extent management local uniform size 1M

[@more@]

segment space management auto;

---create table for test

create table bfw_test(id char(1000)) tablespace bfw_tablespace;

insert into bfw_test select ' ' from dba_objects where rownum<=50000;

---create procedure for select

create or replace procedure bfw_do_select

is

begin

for x in(select*from bfw_test) loop

null;

end loop;

end;

--在多个会话上执行select

var job_no number;

begin

for job_no in 1..2000 loop

dbms_job.submit(:job_no,'bfw_do_select;');

end loop;

commit;

end;

在本会话上执行：

exec bfw_do_select;

--

select s.sid, s.username, s.event,

s.seconds_in_wait, s.wait_time,w.p1,w.p2,w.p3

from v$session s,v$session_wait w

where s.sid=w.sid and

s.state in ('WAITING')

and s.wait_class != 'Idle'

and s.event not like'%resmgr%';

SID USERNAME EVENT SECONDS_IN_WAIT WAIT_TIME P1 P2 P3

----- ------------------------------ ------------------------------ --------------- ---------- ---------- ---------- --------

80 SH read by other session 0 0 6 1388 1

84 SH read by other session 0 0 6 1388 1

90 SH read by other session 0 0 6 1388 1

94 SH read by other session 0 0 6 3535 1

97 SH read by other session 0 0 6 1388 1

98 SH read by other session 0 0 6 3535 1

108 SH read by other session 0 0 6 1388 1

109 SH read by other session 0 0 6 3535 1

113 SH read by other session 0 0 6 1388 1

114 SH read by other session 0 0 6 4989 1

119 SH read by other session 0 0 6 6903 1

122 SH read by other session 0 0 6 3535 1

以上P1代表file#也就是绝对文件号，P2代表block#，p3代表事件的类别。

SQL> select file_name from dba_data_Files where file_id=6

FILE_NAME

--------------------------------------------------

/opt/app/oracle/oradata/liqian/bfw_tablespace.dbf

SQL> l

1 select*

2 from(select event,total_waits,time_waited

3 from v$system_event

4 where wait_class<>'Idle'

5 order by 3 desc

6* ) where rownum<=100

SQL> /

EVENT TOTAL_WAITS TIME_WAITED

------------------------------ ----------- -----------

resmgr:cpu quantum 153812 1175607

read by other session 124783 386951

log file sync 11503 120997

db file sequential read 88968 93065

buffer busy waits 1718 4982

read by other session发生时，会伴随有物理I/O的等待，比如db file sequential read,

db file scattered read.如果block已经缓存在SGA里面，再次测试会以shared模式获得buffer

lock，不会发生此等待。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25586587/viewspace-1053915/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25586587/viewspace-1053915/