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 Cache以及buffer busy waits/gc相關事件AIGC事件
- buffer busy waits引起的會話突增AI會話
- oracle buffer busy waits等待的含義OracleAI
- GC Buffer Busy Waits in RAC: Finding Hot BlocksGCAIBloC
- 【TUNE_ORACLE】等待事件之“buffer busy waits”Oracle事件AI
- gc buffer busyGC
- Oracle Free Buffer WaitsOracleAI
- Oracle優化案例-Bug 5552515引起的buffer busy waits和表物理讀（二十四）Oracle優化AI
- select for update
- buffer busy wait 等待事件說明(轉)AI事件
- gc buffer busy acquire問題處理GCUI
- 延遲塊清理介紹(select也會產生redo的原因)
- [20180305]手工模擬buffer busy wait.txtAI
- 一次gc buffer busy問題的診斷GC
- 深入理解SELECT ... LOCK IN SHARE MODE和SELECT ... FOR UPDATE
- sql查詢更新update selectSQL
- [重慶思莊每日技術分享]-free buffer waits 等待事件AI事件
- mysql update join,insert select 語法MySql
- 33、buffer_cache_3（redo的產生、LRBA、buffer cache裡的等待事件）事件
- update操作會產生幾條mlog$日誌？
- SQLite語句（二）：INSERT DELETE UPDATE SELECTSQLitedelete
- webpack watch模式產生*.hot-update.json檔案Web模式JSON
- Oracle中select for update ...一些區別Oracle
- DBeaver如何生成select，update，delete，insert語句delete
- Oracle：select 或 inactive 會話語句產生鎖？Oracle會話
- MySQL中SELECT+UPDATE併發更新問題MySql
- S/4HANA生產訂單增強WORKORDER_UPDATE方法BEFORE_UPDATE引數分析
- Linux 萬用字元可能產生的問題Linux字元
- 記一次 MySQL select for update 死鎖問題MySql
- 由select for update鎖等待問題引發的深入思考
- java-Mybatis XML 對映器(select,insert, update 和 delete)JavaMyBatisXMLdelete
- Oracle Enqueue WaitsOracleENQAI
- 生產資料update沒加where條件（從執行到恢復）
- 對手機丟失後可能產生的危害的思考
- mysql 高併發 select update 併發更新問題解決方案MySql
- Laravel5.6 如何列印 SQL？insert/update/select 列印方法總結LaravelSQL
- Selenium等待事件Waits事件AI