深入解析 Oracle PAGETABLE EXTENT MAP BLOCK结构
1. 内容介绍
从Oracle 9i开始，管理段空间有两种方法：手动段空间管理和自动段空间管理(Auto Segment Space Management,ASSM),
Oracle默认使用assm进行空间管理，在assm空间管理时，Oracle在段头块(0x23=PAGETABLE SEGMENT HEADER)存储extmap信息,
表示该表使用了哪些数据块，extmap从offset 280开始到2735结束，最多可存储307个ext，大于307个ext时，oracle 使用单向链表结构
指针mhnext(offset 260--263) 4 bytes指向0x24=PAGETABLE EXTENT MAP BLOCK(以下简称EMB块)存储extmap信息，
在EMB块中存储指针mhnext(offset 24~27)指向下一个EMB块直到mhnext存储的值为(0x00000000)为至，
本文主要对EMB块结构做以下说明，
问题1、 EMB块结构到底是什么? 答： 段头块中大于307个extmap使用单向链表指针存储在EMB块中。
问题2、 什么时候会出现EMB块结构? 答： 单表exts>307,7.75时出现第一个EMB块，exts>307+508=815,39.5G时出现第二个EMB块
问题3、 一个EMB块中最多可以存储多少个extmap信息? 答： 508个
问题4、 drop操作对EMB块结构做了哪些变更?
答：
4.1 drop操作将段头块next_MapHeader设置为0x00000000
4.2 drop操作未删除段头块中的L2地址信息
4.3 drop操作将EMB块next_MapHeader设置为0x00000000
4.4 drop操作对EMB块删除Extent Map时保留ext 0,但Auxillary Map未删除,所以单表大于EMB块中的extmap信息可以通过保留的Auxillary Map恢复
2. 问题2、 什么时候会出现EMB块结构?
通过测试证明，当ext数大于307时会出现EMB块结构。
2.1 环境准备
create user hsql identified by hsql;
grant connect,resource,dba to hsql;
drop tablespace hsql including contents and datafiles;
create tablespace hsql datafile '/data1/enmo/hsql01.dbf' size 10M autoextend on;
drop table hsql.drop_1 purge;
create table hsql.drop_1(c_char1 char(2000),c_char2 char(2000)) tablespace hsql;
declare
exts number;
begin
exts := 0;
while(exts<308) loop
for i in 1 .. 1000 loop
insert into hsql.drop_1 values(i,'orastar');
end loop;
commit;
select count(1) into exts from dba_extents where owner='HSQL' AND SEGMENT_NAME='DROP_1';
end loop;
end;
/
2.2 数据字典查询
SQL> set linesize 200 pagesize 200
col owner for a10
col segment_name for a10
select owner,segment_name,header_file,header_block,SEGMENT_TYPE from dba_segments where segment_name='DROP_1';SQL> SQL> SQL>
OWNER SEGMENT_NA HEADER_FILE HEADER_BLOCK SEGMENT_TYPE
---------- ---------- ----------- ------------ ------------------
HSQL DROP_1 5 130 TABLE
SQL>
2.3 dump 段头块信息
alter system dump datafile 5 block 130;
select value from v$diag_info where name='Default Trace File';
--output: /u01/app/oracle/diag/rdbms/enmo/enmo/trace/enmo_ora_5261.trc
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 308 #blocks: 1024000 <-- #extents: 308,表示一共有308个ext
last map 0x014f8088 #maps: 1 offset: 2716
Highwater:: 0x014fa080 ext#: 307 blk#: 8192 ext size: 8192
#blocks in seg. hdr's freelists: 0
#blocks below: 1022514
mapblk 0x014f8088 offset: 0
Unlocked
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x01400110 ext#: 16 blk#: 16 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 132
mapblk 0x00000000 offset: 16
Level 1 BMB for High HWM block: 0x014f8087
Level 1 BMB for Low HWM block: 0x01400100
--------------------------------------------------------
Segment Type: 1 nl2: 2 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x01482088
Last Level 1 BMB: 0x014f8087
Last Level II BMB: 0x01482088
Last Level III BMB: 0x00000000
Map Header:: next 0x014f8088 #extents: 307 obj#: 13806 flag: 0x10000000 <--mpnext: 0x014f8088表示0x24结构地址，exts: 307表示当前块存储307个ext
2.4 dump 0x24结构信息
alter system dump datafile 5 block 1015944;
select value from v$diag_info where name='Default Trace File';
--output: /u01/app/oracle/diag/rdbms/enmo/enmo/trace/enmo_ora_5258.trc
buffer tsn: 5 rdba: 0x014f8088 (5/1015944)
scn: 0x0000.0009a484 seq: 0x02 flg: 0x04 tail: 0xa4842402
frmt: 0x02 chkval: 0x3d24 type: 0x24=PAGETABLE EXTENT MAP BLOCK <-- EMB块结构代码
EMB Dump:
Map Header:: next 0x00000000 #extents: 1 obj#: 13806 flag: 0x10000000
Inc # 0
Extent Map
-----------------------------------------------------------------
0x014f8080 length: 8192
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x014f8080 Data dba: 0x014f8089
--------------------------------------------------------
3. 问题3、 一个EMB块中最多可以存储多少个extmap信息?
EMB块中Extent Map信息从offset 32~35(start of em)到 offset 4100~4103(start of am),最多存储508个extmap地址。
3.1 一个extmap时 oracle dump
scn: 0x0000.0009a484 seq: 0x02 flg: 0x04 tail: 0xa4842402
frmt: 0x02 chkval: 0x3d24 type: 0x24=PAGETABLE EXTENT MAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00002B77AB69FA00 to 0x00002B77AB6A1A00
2B77AB69FA00 0000A224 014F8088 0009A484 04020000 [$.....O.........] <-- 前20byte,kcbh
2B77AB69FA10 00003D24 00000001 00000000 000035EE [$=...........5..] <-- offset 20~23: exts_mh,24~27: next_mh,28~31: blks_mh
2B77AB69FA20 10000000 014F8080 00002000 00000000 [......O.. ......] <-- offset 32~35: flag_mh,36~39: dba_em,40~43: len_em
2B77AB69FA30 00000000 00000000 00000000 00000000 [................]
Repeat 252 times
2B77AB6A0A00 00000000 014F8080 014F8089 00180008 [......O...O.....] <-- offset 4100~4103: L1_am,4104~4107: d_am
2B77AB6A0A10 00140008 000035E8 000035E8 00000005 [.....5...5......]
2B77AB6A0A20 00000000 431E010B 00FC0000 00000D03 [.......C........]
2B77AB6A0A30 0008CBD8 01410F79 01400082 010312FA [....y.A...@.....]
2B77AB6A0A40 00000001 46000000 00180008 00140008 [.......F........]
2B77AB6A0A50 2C0035E8 D0FE0201 20323607 20202020 [.5.,.....62 ]
2B77AB6A0A60 20202020 20202020 20202020 20202020 [ ]
Repeat 123 times
2B77AB6A1220 20202020 20202020 07D0FE20 7361726F [ ...oras]
2B77AB6A1230 20726174 20202020 20202020 20202020 [tar ]
2B77AB6A1240 20202020 20202020 20202020 20202020 [ ]
Repeat 122 times
2B77AB6A19F0 20202020 20202020 00000000 A4842402 [ .....$..]
EMB Dump:
Map Header:: next 0x00000000 #extents: 1 obj#: 13806 flag: 0x10000000
Inc # 0
Extent Map
-----------------------------------------------------------------
0x014f8080 length: 8192
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x014f8080 Data dba: 0x014f8089
--------------------------------------------------------
3.2 测算出现第二个EMB块需要多大空间
set serveroutput on
DECLARE
exts number;
blocks number;
BEGIN
exts:=307+508;
blocks:=0;
for i in 1..exts loop
if(i<=16) then blocks := blocks + 8;
elsif(i<=63+16) then blocks := blocks + 128;
elsif(i<=120+63+16) then blocks := blocks + 1024;
else
blocks := blocks + 8192;
end if;
end loop;
dbms_output.put_line('Total blocks: '||to_char(blocks));
END;
/
Total blocks: 5177344
PL/SQL procedure successfully completed.
SQL> select 5177344*8/1024/1024 size_g from dual;
SIZE_G
----------
39.5
SQL>
3.3 测算出现第1个EMB块需要多大空间
SQL> set serveroutput on
DECLARE
exts number;
blocks number;
BEGIN
exts:=307;
blocks:=0;
for i in 1..exts loop
if(i<=16) then blocks := blocks + 8;
elsif(i<=63+16) then blocks := blocks + 128;
elsif(i<=120+63+16) then blocks := blocSQL> 2 3 4 5 6 7 8 9 10 ks + 1024;
else
blocks := blocks + 8192;
end if;
end loop;
dbms_output.put_line('Total blocks: '||to_char(blocks));
END;
/ 11 12 13 14 15 16 17
Total blocks: 1015808
PL/SQL procedure successfully completed.
SQL> select 1015808*8/1024/1024 size_g from dual;
SIZE_G
----------
7.75
SQL>
3.4 验证第二个EMB块
create user hsql identified by hsql;
grant connect,resource,dba to hsql;
drop tablespace hsql including contents and datafiles;
create tablespace hsql datafile '+data02' size 30G autoextend on;
alter tablespace hsql add datafile '+data02' size 15G autoextend on;
drop table hsql.drop_1 purge;
create table hsql.drop_1(c_char1 char(2000),c_char2 char(2000)) tablespace hsql;
declare
exts number;
begin
exts := 0;
while(exts<823) loop
for i in 1 .. 1000 loop
insert into hsql.drop_1 values(i,'orastar');
end loop;
commit;
select count(1) into exts from dba_extents where owner='HSQL' AND SEGMENT_NAME='DROP_1';
end loop;
end;
/
3.5 检查段头块信息
alter system dump datafile 7 block 130;
select value from v$diag_info where name='Default Trace File';
--output: /u01/app/oracle/diag/rdbms/epmsn/epmsn1/trace/epmsn1_ora_44778.trc
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 823 #blocks: 5242880
last map 0x01b12088 #maps: 2 offset: 2716
Highwater:: 0x01b22080 ext#: 822 blk#: 8192 ext size: 8192
#blocks in seg. hdr's freelists: 0
#blocks below: 5237269
mapblk 0x01b12088 offset: 7
Unlocked
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x01c00100 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: 0x01b20087
Level 1 BMB for Low HWM block: 0x01c000f0
--------------------------------------------------------
Segment Type: 1 nl2: 6 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x01a94088
Last Level 1 BMB: 0x01b20087
Last Level II BMB: 0x01a94088
Last Level III BMB: 0x00000000
Map Header:: next 0x01c7c088 #extents: 307 obj#: 90171 flag: 0x10000000
Inc # 0
Extent Map
0x01c00080 length: 8
0x01c00088 length: 8
0x01c00090 length: 8
...
0x01c78080 length: 8192
0x01878080 length: 8192
0x01c7a080 length: 8192
0x0187a080 length: 8192
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x01c00080 Data dba: 0x01c00083
Extent 1 : L1 dba: 0x01c00080 Data dba: 0x01c00088
Extent 2 : L1 dba: 0x01c00090 Data dba: 0x01c00091
...
Extent 304 : L1 dba: 0x01878080 Data dba: 0x01878088
Extent 305 : L1 dba: 0x01c7a080 Data dba: 0x01c7a088
Extent 306 : L1 dba: 0x0187a080 Data dba: 0x0187a088
--------------------------------------------------------
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x01c00081
DBA 2: 0x01840088
DBA 3: 0x018be088
DBA 4: 0x0193c088
DBA 5: 0x019ba088
DBA 6: 0x01a94088
3.6 检查第一个EMB块
alter system dump datafile 7 block 508040;
select value from v$diag_info where name='Default Trace File';
--output: /u01/app/oracle/diag/rdbms/epmsn/epmsn1/trace/epmsn1_ora_45259.trc
EMB Dump:
Map Header:: next 0x01b12088 #extents: 508 obj#: 90171 flag: 0x10000000
Inc # 0
Extent Map
-----------------------------------------------------------------
0x01c7c080 length: 8192
0x0187c080 length: 8192
0x01c7e080 length: 8192
0x0187e080 length: 8192
0x01c80080 length: 8192
0x01880080 length: 8192
...
0x01b0c080 length: 8192
0x01b0e080 length: 8192
0x01b10080 length: 8192
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x01c7c080 Data dba: 0x01c7c089
Extent 1 : L1 dba: 0x0187c080 Data dba: 0x0187c088
Extent 2 : L1 dba: 0x01c7e080 Data dba: 0x01c7e088
...
Extent 505 : L1 dba: 0x01b0c080 Data dba: 0x01b0c088
Extent 506 : L1 dba: 0x01b0e080 Data dba: 0x01b0e088
Extent 507 : L1 dba: 0x01b10080 Data dba: 0x01b10088
--------------------------------------------------------
3.7 检查第二个EMB块
alter system dump datafile 6 block 3219592;
select value from v$diag_info where name='Default Trace File';
--output: /u01/app/oracle/diag/rdbms/epmsn/epmsn1/trace/epmsn1_ora_46626.trc
**************rdba地址
select dbms_utility.data_block_address_file(to_number('1b12088','xxxxxxx')) file_id,
dbms_utility.data_block_address_block(to_number('1b12088','xxxxxxx')) block_id from dual;
FILE_ID BLOCK_ID
---------- ----------
6 3219592
Dump of memory from 0x00002AB3E6D73A00 to 0x00002AB3E6D75A00
2AB3E6D73A00 0000A224 01B12088 00814AFB 04010000 [$.... ...J......]
2AB3E6D73A10 00009694 00000008 00000000 0001603B [............;`..]
2AB3E6D73A20 10000000 01B12080 00002000 01B14080 [..... ... ...@..]
2AB3E6D73A30 00002000 01B16080 00002000 01B18080 [. ...`... ......]
2AB3E6D73A40 00002000 01B1A080 00002000 01B1C080 [. ....... ......]
2AB3E6D73A50 00002000 01B1E080 00002000 01B20080 [. ....... ......]
2AB3E6D73A60 00002000 00000000 00000000 00000000 [. ..............]
2AB3E6D73A70 00000000 00000000 00000000 00000000 [................]
Repeat 248 times
2AB3E6D74A00 00000000 01B12080 01B12089 01B14080 [..... ... ...@..]
2AB3E6D74A10 01B14088 01B16080 01B16088 01B18080 [.@...`...`......]
2AB3E6D74A20 01B18088 01B1A080 01B1A088 01B1C080 [................]
2AB3E6D74A30 01B1C088 01B1E080 01B1E088 01B20080 [................]
2AB3E6D74A40 01B20088 00000000 00000000 00000000 [................]
2AB3E6D74A50 2C000000 D0FE0201 30383207 20202020 [...,.....280 ]
2AB3E6D74A60 20202020 20202020 20202020 20202020 [ ]
Repeat 123 times
2AB3E6D75220 20202020 20202020 07D0FE20 7361726F [ ...oras]
2AB3E6D75230 20726174 20202020 20202020 20202020 [tar ]
2AB3E6D75240 20202020 20202020 20202020 20202020 [ ]
Repeat 122 times
2AB3E6D759F0 20202020 20202020 00000000 4AFB2401 [ .....$.J]
EMB Dump:
Map Header:: next 0x00000000 #extents: 8 obj#: 90171 flag: 0x10000000
Inc # 0
Extent Map
-----------------------------------------------------------------
0x01b12080 length: 8192
0x01b14080 length: 8192
0x01b16080 length: 8192
0x01b18080 length: 8192
0x01b1a080 length: 8192
0x01b1c080 length: 8192
0x01b1e080 length: 8192
0x01b20080 length: 8192
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x01b12080 Data dba: 0x01b12089
Extent 1 : L1 dba: 0x01b14080 Data dba: 0x01b14088
Extent 2 : L1 dba: 0x01b16080 Data dba: 0x01b16088
Extent 3 : L1 dba: 0x01b18080 Data dba: 0x01b18088
Extent 4 : L1 dba: 0x01b1a080 Data dba: 0x01b1a088
Extent 5 : L1 dba: 0x01b1c080 Data dba: 0x01b1c088
Extent 6 : L1 dba: 0x01b1e080 Data dba: 0x01b1e088
Extent 7 : L1 dba: 0x01b20080 Data dba: 0x01b20088
--------------------------------------------------------
4. drop操作变更分析
4.1 drop 操作后段头块中记录L2及next_mh变化
alter system dump datafile 7 block 130;
select value from v$diag_info where name='Default Trace File';
--output: /u01/app/oracle/diag/rdbms/epmsn/epmsn1/trace/epmsn1_ora_46903.trc
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 8
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x01b22080 ext#: 1 blk#: 8192 ext size: 8192
#blocks in seg. hdr's freelists: 0
#blocks below: 5237269
mapblk 0x00000000 offset: 1
Disk Lock:: Locked by xid: 0x0009.01d.000006f9
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x01c00100 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: 0x01b20087
Level 1 BMB for Low HWM block: 0x01c000f0
--------------------------------------------------------
Segment Type: 1 nl2: 6 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x01a94088
Last Level 1 BMB: 0x01b20087
Last Level II BMB: 0x01a94088
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 1 obj#: 90171 flag: 0x12000000 <--drop操作将段头块next_MapHeader设置为0x00000000
Inc # 0
Extent Map
-----------------------------------------------------------------
0x01c00080 length: 8 <--drop操作将段头块中extentMap/Auxillary Map删除，只保留ext 0;
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x01c00080 Data dba: 0x01c00083
--------------------------------------------------------
Second Level Bitmap block DBAs <
--------------------------------------------------------
DBA 1: 0x01c00081
DBA 2: 0x01840088
DBA 3: 0x018be088
DBA 4: 0x0193c088
DBA 5: 0x019ba088
DBA 6: 0x01a94088
4.2 检查第一个EMB块
alter system dump datafile 7 block 508040;
select value from v$diag_info where name='Default Trace File';
--output: /u01/app/oracle/diag/rdbms/epmsn/epmsn1/trace/epmsn1_ora_47230.trc
EMB Dump:
Map Header:: next 0x00000000 #extents: 1 obj#: 90171 flag: 0x12000000 <--drop操作将EMB块next_MapHeader设置为0x00000000
Inc # 0
Extent Map
-----------------------------------------------------------------
0x01c7c080 length: 8192
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x01c7c080 Data dba: 0x01c7c089
--------------------------------------------------------
4.3 检查第二个EMB块
alter system dump datafile 6 block 3219592;
select value from v$diag_info where name='Default Trace File';
Dump of memory from 0x00002B4FF3CFEA00 to 0x00002B4FF3D00A00
2B4FF3CFEA00 0000A224 01B12088 00816A4A 04070000 [$.... ..Jj......]
2B4FF3CFEA10 000095AF 00000001 00000000 0001603B [............;`..]
2B4FF3CFEA20 12000000 01B12080 00002000 00000000 [..... ... ......]
2B4FF3CFEA30 00000000 00000000 00000000 00000000 [................]
Repeat 252 times
2B4FF3CFFA00 00000000 01B12080 01B12089 01B14080 [..... ... ...@..] <--drop操作对EMB块删除Extent Map时保留ext 0,但Auxillary Map未删除
2B4FF3CFFA10 01B14088 01B16080 01B16088 01B18080 [.@...`...`......]
2B4FF3CFFA20 01B18088 01B1A080 01B1A088 01B1C080 [................]
2B4FF3CFFA30 01B1C088 01B1E080 01B1E088 01B20080 [................]
2B4FF3CFFA40 01B20088 00000000 00000000 00000000 [................]
2B4FF3CFFA50 2C000000 D0FE0201 30383207 20202020 [...,.....280 ]
2B4FF3CFFA60 20202020 20202020 20202020 20202020 [ ]
Repeat 123 times
2B4FF3D00220 20202020 20202020 07D0FE20 7361726F [ ...oras]
2B4FF3D00230 20726174 20202020 20202020 20202020 [tar ]
2B4FF3D00240 20202020 20202020 20202020 20202020 [ ]
Repeat 122 times
2B4FF3D009F0 20202020 20202020 00000000 6A4A2407 [ .....$Jj]
EMB Dump:
Map Header:: next 0x00000000 #extents: 1 obj#: 90171 flag: 0x12000000
Inc # 0
Extent Map
-----------------------------------------------------------------
0x01b12080 length: 8192
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x01b12080 Data dba: 0x01b12089
--------------------------------------------------------
4.4 drop前后EMB块二进制对比图解
5. 内容总结
1. 段头块中大于307个extmap使用单向链表指针存储在EMB块中;
2. 单表exts>307,约7.75G大小时出现第一个EMB块，exts>307+508=815,39.5G大小时出现第二个EMB块，后续按此规律扩展;
3. 一个EMB块中最多可以存储508个extmap信息;
4. drop操作将段头块next_MapHeader设置为0x00000000;
5. drop操作未删除段头块中的L2地址信息，L2中存储着完整的L1信息，所以可以通过段头块中保留的L2恢复所有extmap信息;
6. drop操作将EMB块next_MapHeader设置为0x00000000，虽然该指针地址被清空，但可以通过每个emb块中存储508个extmap，计算出下个emb块的位置;
7. drop操作对EMB块删除Extent Map时保留ext 0,但Auxillary Map未删除,所以单表大于307 exts时（约7.75G）EMB块中的extmap信息可以通过保留的Auxillary Map恢复;
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
