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