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
檢視塊的類別：
等待時間裡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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Buffer Busy Waits是怎麼產生的？AI
- Oracle Buffer Busy WaitsOracleAI
- 【等待事件】buffer busy waits事件AI
- Buffer Busy Waits深入分析AI
- Buffer busy waits/read by other sessionAISession
- buffer busy waits你誤解了嗎？AI
- Buffer Cache以及buffer busy waits/gc相關事件AIGC事件
- buffer busy waits引起的會話突增AI會話
- oracle buffer busy waits等待的含義OracleAI
- buffer busy waits 平均等待時間AI
- buffer cache實驗7-buffer busy waits-完成AI
- GC Buffer Busy Waits in RAC: Finding Hot BlocksGCAIBloC
- 【TUNE_ORACLE】等待事件之“buffer busy waits”Oracle事件AI
- [摘錄]Oracle Wait Interface之Buffer busy waits事件OracleAI事件
- Oracle Dba必須瞭解的buffer busy waits等待OracleAI
- 等待事件_buffer_busy_waits_and_read_by_other_session(1)事件AISession
- 等待事件_buffer_busy_waits_and_read_by_other_session(2)事件AISession
- 等待事件_buffer_busy_waits_and_read_by_other_session(3)事件AISession
- 等待事件_buffer_busy_waits_and_read_by_other_session(4)事件AISession
- 模擬產生CBC LATCH與buffer busy wait等待事件AI事件
- 效能調整一則：buffer busy waits導致主要issueAI
- [20161214]關於Buffer Busy Waits.txtAI
- [20150122]buffer busy waits特例.txtAI
- buffer busy waits與rac cluster wait之間的聯絡AI
- buffer busy waits, latch cache buffers chains, read by other session區別AISession
- gc buffer busyGC
- Oracle Free Buffer WaitsOracleAI
- buffer busy wait 解析AI
- 延遲塊清理介紹(select也會產生redo的原因)
- wait event:gc buffer busyAIGC
- gc buffer busy的優化GC優化
- Buffer Busy Wait小結AI
- zt_buffer busy waitAI
- Oracle優化案例-Bug 5552515引起的buffer busy waits和表物理讀（二十四）Oracle優化AI
- select for update
- gc buffer busy的最佳化GC
- 等待模擬-BUFFER BUSY WAITAI
- buffer busy wait 的深度剖析AI