[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#表示什么,估计数据块有类型.
http://www.juliandyke.com/Internals/BlockClasses.php
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