3

深入解析 Oracle PAGETABLE EXTENT MAP BLOCK结构

深入解析 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块二进制对比图解

20210130192340

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进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论