[20161214]關於Buffer Busy Waits.txt

--oracle一直在不斷的改進,oracle對外宣傳總是讀寫不會相互阻塞,實際上從內部看讀讀不會阻塞,寫寫一定會出現阻塞,
--如果讀寫呢? 實際上寫入會阻塞讀取操作,這個時候讀取會出現等待(以前我一直以為這時寫入程式會話會出現等待事件Buffer Busy
--Waits,實際上存在很大的錯誤!!),等待事件就是Buffer Busy Waits,還是透過測試來講解.

1.環境:
SCOTT@book> @ &r/ver1

PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> create table t1 as select rownum id,'test' name from dual connect by level<=2;
Table created.

SCOTT@book> select rowid,t1.* from t1;
ROWID               ID NAME
------------------ --- ------
AAAVpIAAEAAAAILAAA   1 test
AAAVpIAAEAAAAILAAB   2 test

2.建立測試指令碼:
$ cat a.sql
declare
j number;
begin
for i in 1..1e7 loop
select id into j from t1 where rowid='AAAVpIAAEAAAAILAAA';
end loop;
end;
/

$ cat b.sql
begin
for i in 1..1e5 loop
update t1 set name='test' where rowid='AAAVpIAAEAAAAILAAB';
commit;
end loop;
end;
/

--開啟2個會話分別執行:
--會話1:
SCOTT@book(56,1139)>
SCOTT@book(56,1139)> @a.sql

--會話2:
SCOTT@book(68,697)>
SCOTT@book(68,697)> @ b.sql

SCOTT@book> @ &r/wait
P1RAW            P2RAW            P3RAW                    P1         P2         P3        SID    SERIAL#       SEQ# EVENT                                    STATE               WAIT_TIME_MICRO SECONDS_IN_WAIT
---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- ------------------- --------------- ---------------
0000000062657100 0000000000000001 00               1650815232          1          0         46        969         81 SQL*Net message to client                WAITED SHORT TIME                 2               0
0000000000000004 000000000000020B 0000000000000001          4        523          1         56       1139      15212 buffer busy waits                        WAITED SHORT TIME                 2               0
00               00               00                        0          0          0         68        697         34 log file switch (checkpoint incomplete) WAITED SHORT TIME               418               2

SCOTT@book> @ &r/wait
P1RAW            P2RAW            P3RAW                    P1         P2         P3        SID    SERIAL#       SEQ# EVENT                                    STATE               WAIT_TIME_MICRO SECONDS_IN_WAIT
---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- ------------------- --------------- ---------------
0000000000000001 0000000000000100 00000000FFFFFFFF          1        256 4294967295        254          3      23917 Log archive I/O                          WAITING                         337               0
0000000062657100 0000000000000001 00               1650815232          1          0         46        969        105 SQL*Net message to client                WAITED SHORT TIME                 2               0
0000000000000004 000000000000020B 0000000000000001          4        523          1         56       1139       9237 buffer busy waits                        WAITED SHORT TIME                 2               0
00               00               00                        0          0          0         68        697         44 log file switch completion               WAITED SHORT TIME               458               0

--出現等待事件buffer busy waits 的session是(sid,serial#)=(56,1139),正好執行的是大量讀取的操作.

SCOTT@book> select * from v$event_name where lower(name) like lower('buffer busy waits');
EVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
------ ---------- ----------------- -------------------- -------------------- -------------------- ------------- ----------- --------------------
95 2161531084 buffer busy waits file# block# class# 3875070507 4 Concurrency

SCOTT@book> @ &r/rowid AAAVpIAAEAAAAILAAB
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
88648 4 523 1 0x100020B 4,523 alter system dump datafile 4 block 523 ;

--至於P3=1 class#表示什麼,估計資料塊有型別.

Block
Class    Description
    1    Data block
    2    Sort block
    3    Save undo block
    4    Segment header
    5    Save undo header
    6    Free list
    7    Extent map
    8    1st level bitmap block
    9    2nd level bitmap block
   10    3rd level bitmap block
   11    Bitmap block
   12    Bitmap index block
   13    File header block
   14    Unused
   15    System undo block
   16    System undo block
   17    Undo header
   18    Undo block

[20161214]關於Buffer Busy Waits.txt

相關文章