[20150122]buffer busy waits特例.txt
--oracle 里有句名言:读不阻塞写,写阻塞读.
--如果读读模式,是否会出现buffer busy waits呢?通过例子来说明:
1.建立测试环境:
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> create table t as select rownum id,'test' data from dual connect by levelTable created.
SCOTT@test> @stats t
exec sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false)
PL/SQL procedure successfully completed.
SCOTT@test> select rowid,t.* from t where id=42;
ROWID ID DATA
------------------ ---------- ----
AABIwbAAEAAAAfjAAp 42 test
SCOTT@test> @lookup_rowid AABIwbAAEAAAAfjAAp
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
298011 4 2019 41 4,2019 alter system dump datafile 4 block 2019
--建立测试脚本:
$ cat f1.sql
declare
m_id number;
m_data varchar2(200);
begin
for i in 1 .. 1e9 loop
select data into m_data from t where id = 42;
end loop;
end ;
/
$ cat f.sh
#! /bin/bash
sqlplus -s scott/btbtms @$1 &
sqlplus -s scott/btbtms @$1 &
sqlplus -s scott/btbtms @$1 &
sqlplus -s scott/btbtms @$1 &
2.开始测试:
$ source f.sh f1.sql
SCOTT@test> select p1raw,p2raw,sid,serial#,seq#,event,state,wait_time_micro,seconds_in_wait from v$session where wait_class'Idle' order by event ;
P1RAW P2RAW SID SERIAL# SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT
---------------- ---------------- ---------- ---------- ---------- ---------------------------------------- ------------------- --------------- ---------------
0000000062657100 0000000000000001 6 15 1415 SQL*Net message to client WAITED SHORT TIME 2 0
00000000664C4F47 0000000000000001 203 75 54 cursor: pin S WAITED SHORT TIME 7 0
00000000BC9103C8 000000000000009B 596 43 47 latch: cache buffers chains WAITED SHORT TIME 437 1
00000000BC9103C8 000000000000009B 395 25 43 latch: cache buffers chains WAITED SHORT TIME 463 1
00000000BC9103C8 000000000000009B 12 43 55 latch: cache buffers chains WAITED SHORT TIME 494 1
--再增加4个会话:
$ source f.sh f1.sql
SCOTT@test> select p1raw,p2raw,sid,serial#,seq#,event,state,wait_time_micro,seconds_in_wait from v$session where wait_class'Idle' order by event ;
P1RAW P2RAW SID SERIAL# SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT
---------------- ---------------- ---------- ---------- ---------- ---------------------------------------- ------------------- --------------- ---------------
0000000062657100 0000000000000001 6 15 1491 SQL*Net message to client WAITED SHORT TIME 3 0
00000000664C4F47 0000018F00000007 12 43 182 cursor: pin S WAITED KNOWN TIME 10906 1
00000000664C4F47 0000018F00000007 203 75 187 cursor: pin S WAITED KNOWN TIME 10854 1
00000000664C4F47 0000018F00000007 595 15 120 cursor: pin S WAITED KNOWN TIME 221355 1
00000000664C4F47 0000018F00000007 596 43 195 cursor: pin S WAITED KNOWN TIME 23502 1
00000000664C4F47 0000018F00000007 201 47 118 cursor: pin S WAITED KNOWN TIME 11327 1
00000000BC8D0380 000000000000009B 399 27 117 latch: cache buffers chains WAITED SHORT TIME 64 0
00000000BC8D0380 000000000000009B 395 25 188 latch: cache buffers chains WAITED SHORT TIME 458 0
00000000BC8D0380 000000000000009B 14 53 121 latch: cache buffers chains WAITING 5281 0
9 rows selected.
--再增加4个会话:
$ source f.sh f1.sql
-- 依旧没有出现buffer busy waits.
3.测试2,建立表大一些.
SCOTT@test> create table t as select rownum id,'test' data from dual connect by levelTable created.
SCOTT@test> ALTER TABLE t MINIMIZE RECORDS_PER_BLOCK ;
Table altered.
--这样每块仅仅2条记录,可以很快建立"大表".
SCOTT@test> delete from t ;
2 rows deleted.
SCOTT@test> commit ;
Commit complete.
SCOTT@test> insert into t select rownum id,'test' data from dual connect by level10000 rows created.
SCOTT@test> commit ;
Commit complete.
SCOTT@test> @stats t
exec sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false)
PL/SQL procedure successfully completed.
SCOTT@test> select header_file,header_block,bytes,blocks from dba_segments where owner=user and segment_name='T';
HEADER_FILE HEADER_BLOCK BYTES BLOCKS
----------- ------------ ---------- ----------
4 1802 41943040 5120
SCOTT@test> select p1raw,p2raw,sid,serial#,seq#,event,state,wait_time_micro,seconds_in_wait from v$session where wait_class'Idle' order by event ;
P1RAW P2RAW SID SERIAL# SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT
---------------- ---------------- ---------- ---------- ---------- ---------------------------------------- ------------------- --------------- ---------------
0000000062657100 0000000000000001 406 37 5399 SQL*Net message to client WAITED SHORT TIME 4 0
0000000000000004 000000000000070A 206 55 55 buffer busy waits WAITED SHORT TIME 18 0
0000000000000004 000000000000070A 207 43 93 buffer busy waits WAITED SHORT TIME 15 1
00000000BCA208E0 000000000000009B 400 69 73 latch: cache buffers chains WAITED SHORT TIME 54 0
00000000BCA208E0 000000000000009B 205 21 106 latch: cache buffers chains WAITED SHORT TIME 45 1
--可以发现很快出现buffer busy waits.
SCOTT@test> select * from V$EVENT_NAME where name='buffer busy waits';
EVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ---------- -------------------- -------------------- -------------------- -------------------- ------------- ----------- --------------------
94 2161531084 buffer busy waits file# block# class# 3875070507 4 Concurrency
--可以发现参数P1表示file#,P2表示block#. 000000000000070A(16进制)
SCOTT@test> @16to10 70a
16 to 10 DEC
------------
1802
--正好是段头.也就是即使在读读模式,对于段头,buffer herder的buffer bin要设置为排他模式,这样才会出现buffer busy waits.
--因为段头保存extent map,全表扫描要多次读取.
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x01000708 Data dba: 0x0100070b
Extent 1 : L1 dba: 0x01000708 Data dba: 0x01000710
Extent 2 : L1 dba: 0x01000718 Data dba: 0x01000719
Extent 3 : L1 dba: 0x01000718 Data dba: 0x01000720
Extent 4 : L1 dba: 0x01000728 Data dba: 0x01000729
Extent 5 : L1 dba: 0x01000728 Data dba: 0x01000730
Extent 6 : L1 dba: 0x01000738 Data dba: 0x01000739
Extent 7 : L1 dba: 0x01000738 Data dba: 0x01000740
Extent 8 : L1 dba: 0x01000748 Data dba: 0x01000749
Extent 9 : L1 dba: 0x01000748 Data dba: 0x01000750
Extent 10 : L1 dba: 0x01000758 Data dba: 0x01000759
Extent 11 : L1 dba: 0x01000758 Data dba: 0x01000768
Extent 12 : L1 dba: 0x01000770 Data dba: 0x01000771
Extent 13 : L1 dba: 0x01000770 Data dba: 0x010007c8
Extent 14 : L1 dba: 0x010007d0 Data dba: 0x010007d1
Extent 15 : L1 dba: 0x010007d0 Data dba: 0x010007d8
Extent 16 : L1 dba: 0x01000b00 Data dba: 0x01000b02
Extent 17 : L1 dba: 0x01000b80 Data dba: 0x01000b82
Extent 18 : L1 dba: 0x01000c80 Data dba: 0x01000c82
Extent 19 : L1 dba: 0x01000e00 Data dba: 0x01000e02
Extent 20 : L1 dba: 0x01000f00 Data dba: 0x01000f02
Extent 21 : L1 dba: 0x01001080 Data dba: 0x01001082
Extent 22 : L1 dba: 0x01001180 Data dba: 0x01001182
Extent 23 : L1 dba: 0x01001300 Data dba: 0x01001302
Extent 24 : L1 dba: 0x01001480 Data dba: 0x01001482
Extent 25 : L1 dba: 0x01001600 Data dba: 0x01001602
Extent 26 : L1 dba: 0x01001700 Data dba: 0x01001702
Extent 27 : L1 dba: 0x01001800 Data dba: 0x01001802
Extent 28 : L1 dba: 0x01001980 Data dba: 0x01001982
Extent 29 : L1 dba: 0x01001a80 Data dba: 0x01001a82
Extent 30 : L1 dba: 0x01001c00 Data dba: 0x01001c02
Extent 31 : L1 dba: 0x01001d00 Data dba: 0x01001d02
Extent 32 : L1 dba: 0x01001d80 Data dba: 0x01001d82
Extent 33 : L1 dba: 0x01001e00 Data dba: 0x01001e02
Extent 34 : L1 dba: 0x01001e80 Data dba: 0x01001e82
Extent 35 : L1 dba: 0x01001f00 Data dba: 0x01001f02
Extent 36 : L1 dba: 0x01001f80 Data dba: 0x01001f82
Extent 37 : L1 dba: 0x01002000 Data dba: 0x01002002
Extent 38 : L1 dba: 0x01002080 Data dba: 0x01002082
Extent 39 : L1 dba: 0x01002100 Data dba: 0x01002102
Extent 40 : L1 dba: 0x01002180 Data dba: 0x01002182
Extent 41 : L1 dba: 0x01002200 Data dba: 0x01002202
Extent 42 : L1 dba: 0x01002280 Data dba: 0x01002282
Extent 43 : L1 dba: 0x01002300 Data dba: 0x01002302
Extent 44 : L1 dba: 0x01002380 Data dba: 0x01002382
Extent 45 : L1 dba: 0x01002400 Data dba: 0x01002402
Extent 46 : L1 dba: 0x01002480 Data dba: 0x01002482
Extent 47 : L1 dba: 0x01002500 Data dba: 0x01002502
Extent 48 : L1 dba: 0x01002580 Data dba: 0x01002582
Extent 49 : L1 dba: 0x01002600 Data dba: 0x01002602
Extent 50 : L1 dba: 0x01002680 Data dba: 0x01002682
Extent 51 : L1 dba: 0x01002700 Data dba: 0x01002702
Extent 52 : L1 dba: 0x01002780 Data dba: 0x01002782
Extent 53 : L1 dba: 0x01002800 Data dba: 0x01002802
Extent 54 : L1 dba: 0x01002880 Data dba: 0x01002882
--------------------------------------------------------
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x01000709
End dump data blocks tsn: 4 file#: 4 minblk 1802 maxblk 1802
--为什么测试1没有出现呢?与表的大小有关吗?继续测试.
4.为了测试方便,加入如下脚本:
$ cat b1.sql
drop table t purge ;
create table t (id number,data varchar2(20));
insert into t select rownum id,'name' data from dual connect by levelALTER TABLE t MINIMIZE RECORDS_PER_BLOCK ;
delete from t;
commit ;
insert into t select rownum id,'name' data from dual connect by level@stats t
select header_file,header_block,bytes,blocks from dba_segments where owner=user and segment_name='T';
-- 我的测试插入257条记录,就会出现buffer busy waits等待事件.
-- 注意我使用的表空间是系统管理表空间,8k.
SCOTT@test> column PARTITION_NAME noprint
SCOTT@test> select * from dba_extents where owner=user and segment_name='T';
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ ------------- ------------- ---------------- ---------- ---------- ---------- ---------- ---------- ------------
SCOTT T TABLE USERS 0 4 1800 65536 8 4
SCOTT T TABLE USERS 1 4 1808 65536 8 4
SCOTT T TABLE USERS 2 4 1816 65536 8 4
SCOTT T TABLE USERS 3 4 1824 65536 8 4
SCOTT T TABLE USERS 4 4 1832 65536 8 4
SCOTT T TABLE USERS 5 4 1840 65536 8 4
SCOTT T TABLE USERS 6 4 1848 65536 8 4
SCOTT T TABLE USERS 7 4 1856 65536 8 4
SCOTT T TABLE USERS 8 4 1864 65536 8 4
SCOTT T TABLE USERS 9 4 1872 65536 8 4
SCOTT T TABLE USERS 10 4 1880 65536 8 4
SCOTT T TABLE USERS 11 4 1896 65536 8 4
SCOTT T TABLE USERS 12 4 1904 65536 8 4
SCOTT T TABLE USERS 13 4 1992 65536 8 4
SCOTT T TABLE USERS 14 4 2000 65536 8 4
SCOTT T TABLE USERS 15 4 2008 65536 8 4
SCOTT T TABLE USERS 16 4 2816 1048576 128 4
17 rows selected.
-- 每块2条记录,前面16个EXTENT_ID,占用8块. 这样16*8*2 = 256条. 也就是开始使用extend大小1M(128块的)时候,在全表扫描时出现时,会出现
-- buffer busy waits.
-- 实际上块里面包含段头,1级位图,2级位图,前面0-15extetnd,有8个1级位图,1个2级位图,1个段头(兼3级位图),插入256-10*2=236条以后都在
-- EXTENT_ID=16的区域.
-- 另外测试时要注意另外一个细节: 11G的direct path direct.
SCOTT@test> alter system checkpoint ;
System altered.
SCOTT@test> alter system dump datafile 4 block 1802;
System altered.
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 17 #blocks: 256
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x01000b80 ext#: 16 blk#: 128 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 244
mapblk 0x00000000 offset: 16
Unlocked
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x010007e0 ext#: 15 blk#: 8 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 118
mapblk 0x00000000 offset: 15
Level 1 BMB for High HWM block: 0x01000b01
Level 1 BMB for Low HWM block: 0x010007d0
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x01000709
Last Level 1 BMB: 0x01000b01
Last Level II BMB: 0x01000709
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 17 obj#: 298135 flag: 0x10000000
Inc # 0
Extent Map
-----------------------------------------------------------------
0x01000708 length: 8
0x01000710 length: 8
0x01000718 length: 8
0x01000720 length: 8
0x01000728 length: 8
0x01000730 length: 8
0x01000738 length: 8
0x01000740 length: 8
0x01000748 length: 8
0x01000750 length: 8
0x01000758 length: 8
0x01000768 length: 8
0x01000770 length: 8
0x010007c8 length: 8
0x010007d0 length: 8
0x010007d8 length: 8
0x01000b00 length: 128
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x01000708 Data dba: 0x0100070b
Extent 1 : L1 dba: 0x01000708 Data dba: 0x01000710
Extent 2 : L1 dba: 0x01000718 Data dba: 0x01000719
Extent 3 : L1 dba: 0x01000718 Data dba: 0x01000720
Extent 4 : L1 dba: 0x01000728 Data dba: 0x01000729
Extent 5 : L1 dba: 0x01000728 Data dba: 0x01000730
Extent 6 : L1 dba: 0x01000738 Data dba: 0x01000739
Extent 7 : L1 dba: 0x01000738 Data dba: 0x01000740
Extent 8 : L1 dba: 0x01000748 Data dba: 0x01000749
Extent 9 : L1 dba: 0x01000748 Data dba: 0x01000750
Extent 10 : L1 dba: 0x01000758 Data dba: 0x01000759
Extent 11 : L1 dba: 0x01000758 Data dba: 0x01000768
Extent 12 : L1 dba: 0x01000770 Data dba: 0x01000771
Extent 13 : L1 dba: 0x01000770 Data dba: 0x010007c8
Extent 14 : L1 dba: 0x010007d0 Data dba: 0x010007d1
Extent 15 : L1 dba: 0x010007d0 Data dba: 0x010007d8
Extent 16 : L1 dba: 0x01000b00 Data dba: 0x01000b02
--------------------------------------------------------
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x01000709
End dump data blocks tsn: 4 file#: 4 minblk 1802 maxblk 1802
SCOTT@test> alter system dump datafile 4 block 1800;
System altered.
Dump of First Level Bitmap Block
--------------------------------
nbits : 4 nranges: 2 parent dba: 0x01000709 poffset: 0
unformatted: 0 total: 16 first useful block: 3
owning instance : 1
instance ownership changed at 01/23/2015 10:38:43
Last successful Search 01/23/2015 10:38:43
Freeness Status: nf1 0 nf2 0 nf3 0 nf4 0
Extent Map Block Offset: 4294967295
First free datablock : 16
Bitmap block lock opcode 0
Locker xid: : 0x0000.000.00000000
Dealloc scn: -887287252.2
Flag: 0x00000000 (-/-/-/-/-/-)
Inc #: 0 Objd: 298135
--------------------------------------------------------
DBA Ranges :
--------------------------------------------------------
0x01000708 Length: 8 Offset: 0
0x01000710 Length: 8 Offset: 8
0:Metadata 1:Metadata 2:Metadata 3:FULL
4:FULL 5:FULL 6:FULL 7:FULL
8:FULL 9:FULL 10:FULL 11:FULL
12:FULL 13:FULL 14:FULL 15:FULL
--------------------------------------------------------
End dump data blocks tsn: 4 file#: 4 minblk 1800 maxblk 1800
- 3-15 extent都是full.
SCOTT@test> alter system dump datafile 4 block 2816;
System altered.
Dump of First Level Bitmap Block
--------------------------------
nbits : 4 nranges: 1 parent dba: 0x01000709 poffset: 8
unformatted: 0 total: 64 first useful block: 2
owning instance : 1
instance ownership changed at 01/23/2015 10:38:43
Last successful Search 01/23/2015 10:38:43
Freeness Status: nf1 0 nf2 0 nf3 0 nf4 53
Extent Map Block Offset: 4294967295
First free datablock : 2
Bitmap block lock opcode 0
Locker xid: : 0x0000.000.00000000
Dealloc scn: -887287252.2
Flag: 0x00000000 (-/-/-/-/-/-)
Inc #: 0 Objd: 298135
--------------------------------------------------------
DBA Ranges :
--------------------------------------------------------
0x01000b00 Length: 64 Offset: 0
0:Metadata 1:Metadata 2:75-100% free 3:75-100% free
4:75-100% free 5:FULL 6:75-100% free 7:75-100% free
8:75-100% free 9:FULL 10:75-100% free 11:75-100% free
12:75-100% free 13:FULL 14:75-100% free 15:75-100% free
16:75-100% free 17:75-100% free 18:75-100% free 19:75-100% free
20:75-100% free 21:75-100% free 22:75-100% free 23:75-100% free
24:75-100% free 25:75-100% free 26:75-100% free 27:75-100% free
28:75-100% free 29:75-100% free 30:75-100% free 31:75-100% free
32:75-100% free 33:75-100% free 34:75-100% free 35:75-100% free
36:75-100% free 37:75-100% free 38:75-100% free 39:75-100% free
40:FULL 41:75-100% free 42:75-100% free 43:75-100% free
44:FULL 45:75-100% free 46:75-100% free 47:75-100% free
48:FULL 49:75-100% free 50:75-100% free 51:75-100% free
52:FULL 53:75-100% free 54:75-100% free 55:75-100% free
56:FULL 57:75-100% free 58:75-100% free 59:75-100% free
60:FULL 61:75-100% free 62:75-100% free 63:75-100% free
--------------------------------------------------------
End dump data blocks tsn: 4 file#: 4 minblk 2816 maxblk 2816
--对比不同就是插入256条记录时.
Dump of First Level Bitmap Block
--------------------------------
nbits : 4 nranges: 1 parent dba: 0x01000709 poffset: 8
unformatted: 16 total: 64 first useful block: 2
owning instance : 1
instance ownership changed at 01/23/2015 11:07:43
Last successful Search 01/23/2015 11:07:43
Freeness Status: nf1 0 nf2 0 nf3 0 nf4 38
Extent Map Block Offset: 4294967295
First free datablock : 2
Bitmap block lock opcode 0
Locker xid: : 0x0000.000.00000000
Dealloc scn: -887058979.2
Flag: 0x00000000 (-/-/-/-/-/-)
Inc #: 0 Objd: 298137
--------------------------------------------------------
DBA Ranges :
--------------------------------------------------------
0x01000b00 Length: 64 Offset: 0
0:Metadata 1:Metadata 2:75-100% free 3:FULL
4:75-100% free 5:75-100% free 6:75-100% free 7:FULL
8:75-100% free 9:75-100% free 10:75-100% free 11:75-100% free
12:75-100% free 13:75-100% free 14:75-100% free 15:75-100% free
16:unformatted 17:unformatted 18:unformatted 19:unformatted
20:unformatted 21:unformatted 22:unformatted 23:unformatted
24:unformatted 25:unformatted 26:unformatted 27:unformatted
28:unformatted 29:unformatted 30:unformatted 31:unformatted
32:75-100% free 33:75-100% free 34:75-100% free 35:75-100% free
36:75-100% free 37:75-100% free 38:75-100% free 39:75-100% free
40:75-100% free 41:75-100% free 42:FULL 43:75-100% free
44:75-100% free 45:75-100% free 46:FULL 47:75-100% free
48:75-100% free 49:75-100% free 50:FULL 51:75-100% free
52:75-100% free 53:75-100% free 54:FULL 55:75-100% free
56:75-100% free 57:75-100% free 58:FULL 59:75-100% free
60:75-100% free 61:75-100% free 62:FULL 63:75-100% free
--------------------------------------------------------
End dump data blocks tsn: 4 file#: 4 minblk 2816 maxblk 2816
--存在16个没有格式化的块.而插入257条记录,包含的块全表格式化.这些分析超出我的能力,放弃.
-- 总之表大小达到一定程度,在读读模式下,也会出现buffer busy waits.而且是段头.
