update/select也可能产生buffer busy waits。

最新推荐文章于 2022-08-08 19:40:49 发布

转载最新推荐文章于 2022-08-08 19:40:49 发布 · 145 阅读

本文通过实验演示了在Oracle数据库中update与select操作如何产生buffer busy waits，并详细解释了其背后的原因，包括查询进程如何处理正在被修改的数据块以及回滚段的争用问题。

update/select也可能产生buffer busy waits。

创建一个表，然后让一个进程去更新，10个进程去查询，也会产生buffer busy wait.
create table bfw_test(id char(1000));
insert into bfw_test select ' ' from dba_objects;
insert into bfw_test select ' ' from dba_objects;
insert into bfw_test select ' ' from dba_objects;
insert into bfw_test select ' ' from dba_objects;
insert into bfw_test select ' ' from dba_objects;
insert into bfw_test select ' ' from dba_objects;
insert into bfw_test select ' ' from dba_objects;
insert into bfw_test select ' ' from dba_objects;
insert into bfw_test select ' ' from dba_objects;
insert into bfw_test select ' ' from dba_objects;
insert into bfw_test select ' ' from dba_objects;
insert into bfw_test select ' ' from dba_objects;
insert into bfw_test select ' ' from dba_objects;
COMMIT;
var job_no number
begin
for idx in 1 .. 20 loop
dbms_job.submit(:job_no,'bfw_do_select;');
end loop;
commit;
end;
/
CREATE OR REPLACE PROCEDURE bfw_do_select
IS
BEGIN
FOR x IN ( SELECT t1.id AS id1 ,t2.id AS id2
FROM bfw_test t1,bfw_test t2 WHERE ROWNUM<50000) LOOP
NULL;
END LOOP;
END;
/
CREATE OR REPLACE PROCEDURE bfw_do_update
IS
BEGIN
UPDATE bfw_test SET id=' ';
END;
/
var job_no NUMBER
BEGIN
Dbms_Job.submit(:job_no,'bfw_do_update;');
COMMIT;
FOR idx IN 1 .. 10 LOOP
Dbms_Job.submit(:job_no,'bfw_do_select;');
END LOOP;
COMMIT;
END;
/
ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';
EXEC bfw_do_select;
EXEC bfw_do_select;
EXEC bfw_do_select;
EXEC bfw_do_select;
EXEC bfw_do_select;
EXEC bfw_do_select;
EXEC bfw_do_select;
EXEC bfw_do_select;
EXEC bfw_do_select;
EXEC bfw_do_select;
EXEC bfw_do_select;
EXEC bfw_do_select;
EXEC bfw_do_select;
EXEC bfw_do_select;
ALTER SESSION SET EVENTS '10046 trace name context off';

grep 'buffer busy waits' crmgsb_ora_28937.trc
WAIT #4: nam='buffer busy waits' ela= 7952 file#=27 block#=177182 class#=70 obj#=0 tim=1291601461099090
WAIT #5: nam='buffer busy waits' ela= 13 file#=22 block#=40097 class#=69 obj#=0 tim=1291601461343589
WAIT #5: nam='buffer busy waits' ela= 19 file#=22 block#=40097 class#=69 obj#=0 tim=1291601461344171
WAIT #5: nam='buffer busy waits' ela= 12 file#=22 block#=40097 class#=69 obj#=0 tim=1291601461345032
WAIT #5: nam='buffer busy waits' ela= 21 file#=22 block#=40097 class#=69 obj#=0 tim=1291601461345321
WAIT #5: nam='buffer busy waits' ela= 12 file#=22 block#=40097 class#=69 obj#=0 tim=1291601461345871
WAIT #5: nam='buffer busy waits' ela= 7 file#=22 block#=40097 class#=69 obj#=0 tim=1291601461346544
WAIT #5: nam='buffer busy waits' ela= 22 file#=22 block#=40097 class#=69 obj#=0 tim=1291601461347506
WAIT #5: nam='buffer busy waits' ela= 11 file#=22 block#=40097 class#=69 obj#=0 tim=1291601461350077
WAIT #5: nam='buffer busy waits' ela= 12 file#=22 block#=40097 class#=69 obj#=0 tim=1291601461353383
WAIT #5: nam='buffer busy waits' ela= 28 file#=22 block#=40097 class#=69 obj#=0 tim=1291601461353833
WAIT #5: nam='buffer busy waits' ela= 9 file#=22 block#=40097 class#=69 obj#=0 tim=1291601461354845
WAIT #5: nam='buffer busy waits' ela= 24 file#=22 block#=40097 class#=69 obj#=0 tim=1291601461355967
WAIT #5: nam='buffer busy waits' ela= 20 file#=22 block#=40097 class#=69 obj#=0 tim=1291601461357634
WAIT #5: nam='buffer busy waits' ela= 8 file#=22 block#=40097 class#=69 obj#=0 tim=1291601461358110
WAIT #5: nam='buffer busy waits' ela= 16 file#=22 block#=40097 class#=69 obj#=0 tim=1291601461358692
WAIT #5: nam='buffer busy waits' ela= 19 file#=22 block#=40097 class#=69 obj#=0 tim=1291601461359301
WAIT #5: nam='buffer busy waits' ela= 14 file#=22 block#=40097 class#=69 obj#=0 tim=1291601461359907
WAIT #5: nam='buffer busy waits' ela= 9 file#=22 block#=40097 class#=69 obj#=0 tim=1291601461362439
WAIT #5: nam='buffer busy waits' ela= 7 file#=22 block#=40097 class#=69 obj#=0 tim=1291601461362856
WAIT #5: nam='buffer busy waits' ela= 9 file#=22 block#=40097 class#=69 obj#=0 tim=1291601461363378
WAIT #5: nam='buffer busy waits' ela= 19 file#=22 block#=40097 class#=69 obj#=0 tim=1291601461363971
WAIT #5: nam='buffer busy waits' ela= 23 file#=22 block#=40097 class#=69 obj#=0 tim=1291601461364313
WAIT #5: nam='buffer busy waits' ela= 7 file#=22 block#=40097 class#=69 obj#=0 tim=1291601461364533
WAIT #5: nam='buffer busy waits' ela= 18 file#=22 block#=40097 class#=69 obj#=0 tim=1291601461366256
WAIT #5: nam='buffer busy waits' ela= 17 file#=22 block#=40097 class#=69 obj#=0 tim=1291601461366781
WAIT #5: nam='buffer busy waits' ela= 21 file#=22 block#=40097 class#=69 obj#=0 tim=1291601461368419
WAIT #5: nam='buffer busy waits' ela= 16 file#=22 block#=40097 class#=69 obj#=0 tim=1291601461368648
WAIT #5: nam='buffer busy waits' ela= 18 file#=22 block#=40097 class#=69 obj#=0 tim=1291601461369445
WAIT #5: nam='buffer busy waits' ela= 16 file#=22 block#=40097 class#=69 obj#=0 tim=1291601461370238
WAIT #5: nam='buffer busy waits' ela= 10 file#=22 block#=40097 class#=69 obj#=0 tim=1291601461372504
WAIT #5: nam='buffer busy waits' ela= 19 file#=22 block#=40097 class#=69 obj#=0 tim=1291601461372831
WAIT #5: nam='buffer busy waits' ela= 13 file#=22 block#=40097 class#=69 obj#=0 tim=1291601461373058
WAIT #5: nam='buffer busy waits' ela= 26 file#=22 block#=40097 class#=69 obj#=0 tim=1291601461373312
WAIT #5: nam='buffer busy waits' ela= 18 file#=22 block#=40097 class#=69 obj#=0 tim=1291601461373845
WAIT #5: nam='buffer busy waits' ela= 17 file#=22 block#=40097 class#=69 obj#=0 tim=1291601461373949
WAIT #5: nam='buffer busy waits' ela= 27 file#=22 block#=40097 class#=69 obj#=0 tim=1291601461374313
WAIT #5: nam='buffer busy waits' ela= 21 file#=22 block#=40097 class#=69 obj#=0 tim=1291601461374828
WAIT #5: nam='buffer busy waits' ela= 18 file#=22 block#=40097 class#=69 obj#=0 tim=1291601461375556
WAIT #5: nam='buffer busy waits' ela= 15 file#=22 block#=40097 class#=69 obj#=0 tim=1291601461375975

查看块的类别：

1 Data block

2 sort block

3 save undo block

4 segment header

5 save undo header

7
extend map

8 lst level bmb

9 2nd level bmb

10 3rd level bmb

11 bitmap block

12 bitmap index block

13 file header block

14 unused

15+2*r undo header block(r=undo segment的编号）

16+2*r undo block(r=undo segment编号）

等待时间里class的列别都是回滚段的。
那什么时候select /update也会出现buffer busy waits呢？分两种情况：
1）查询进程如果发现数据块正在被修改，那么就会根据回滚段去构造CR块，而不是去产生buffer busy waits.
2)修改进程为了保存前镜像(updte语句)，正在修改回滚段头或回滚段块，这个时候查询进程发现块被修改，那么需要去构造CR块，也需要去查询回滚段头和回滚段块。我们知道数据块可以根据回滚段的信息构造出CR块，但是回滚段块呢？，当一个进程正在对回滚段进行修改时候，另一个进程想查询，也会构造CR吗？不会的。这个时候只能等待，因此会产生回滚段争用导致的buffer busy waits。

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

转载于:http://blog.itpub.net/22034023/viewspace-681025/