查看: 1678|回复: 0

[原创] ORACLE LMT(locally-managed tablespaces)位图管理机制

[复制链接]
论坛徽章:
19
娜美
日期:2017-06-26 15:18:15火眼金睛
日期:2018-09-30 22:00:00目光如炬
日期:2018-09-16 22:00:01目光如炬
日期:2018-09-02 22:00:00火眼金睛
日期:2018-08-31 22:00:00目光如炬
日期:2018-07-29 22:00:00火眼金睛
日期:2018-04-30 22:00:00目光如炬
日期:2018-04-29 22:00:00目光如炬
日期:2018-04-22 22:00:00火眼金睛
日期:2018-02-28 22:00:00
发表于 2018-5-21 14:24 | 显示全部楼层 |阅读模式

ORACLE LMT(locally-managed tablespaces)位图管理机制

作者简介:
----------------------------------------------------
@ 孙显鹏,海天起点oracle技术专家,十年从业经验
@ 精通oracle内部原理,擅长调优和解决疑难问题
@ 致力于帮助客户解决生产中的问题,提高生产效率。
@ 爱好:书法,周易,中医。微信:sunyunyi_sun
@ 易曰:精义入神,以致用也!
@ 调优乃燮理阴阳何其难也!
-----------------------------------------------------

环境:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
概念:
在LMT下每个数据文件头存在位图块用来表示该数据文件的块的使用情况,下面是每个file的架构:
The locally-managed (bitmapped) tablespace file has the following structure:
• File header: 1 block  --1号块
• Bitmapped file space header: 1 block  --2号块
• Head portion of bitmap blocks: N blocks --3号块开始
• Useful file blocks: U units (A unit is a number of blocks.)
• Tail portion of bitmap blocks: M blocks
If a Unit = B blocks, then the total file size = 1 + 1 + N + U*B +M.
The operating system file allocated will in some cases be file size + 1 block for the OS
header.

The fields in this control block are:
• RelFno: Relative file number
• Unit: Size of one unit expressed in blocks
(system-managed chunks are 64 KB, uniform-sized are specified by the user.)
• Size: File size in blocks (excluding the OS header block)
• Flag: Can take the following values:
- 0x01 initial control at beginning of file
- 0x02 initial control in file in middle
- 0x04 initial control in different file
- 0x08 auto extension is on
- 0x10 do not trust and maintain stats
• AutoExtend: The file allowed to extend automatically
• Increment: Extend by how many blocks
• MaxSize: Extend to what size expressed in blocks (excluding the OS header block)
• Initial Area: How many bitmap blocks are in the initial control portion (including this block)
• Tail: Last useful block (not necessarily last block of the file)
• First: Where to start looking for space from (bit before the first free extent)
• Free: Number of chunks that can still fit in this file
• Header Opcode --请参照oracle官方文档,因为内容较多这里不打印

The bitmap blocks have two parts:
Bitmap control structure
Vector dump
The fields in the bitmap control structure are:
RelFno: Relative file number to which the bitmap belongs
BeginBlock: Which block number the first bit represents
Flag: Zero for permanent files, one for temp files
First: Where to start looking for free space (bit before first free bit )
Free: Number of free slots (bits) in the bitmap (not the file)



接下来的实验我们通过分析2号块和3号块看看LMT下file的位图管理机制.

SQL> create tablespace test datafile '+data' size 10m;

Tablespace created.

SQL> alter system checkpoint;

System altered.

SQL>
SQL> alter system dump datafile 5 block 2;

System altered.

SQL>  alter system dump datafile 5 block 3;

System altered.

Block dump from disk:
buffer tsn: 5 rdba: 0x01400002 (5/2)
scn: 0x1b3845 seq: 0x02 flg: 0x04 tail: 0x38451d02
frmt: 0x02 chkval: 0xbb5a type: 0x1d=KTFB Bitmapped File Space Header
Hex dump of block: st=0, typ_found=1
File Space Header Block:
Header Control:
RelFno: 5, Unit: 8, Size: 1280, Flag: 1(0x1)  --一个单元8个块,size=10m
AutoExtend: NO, Increment: 0, MaxSize: 0
Initial Area: 126, Tail: 1279, First: 0, Free: 144
Deallocation scn: 0x0000000000000003
Header Opcode:
Save: No Pending Op
End dump data blocks tsn: 5 file#: 5 minblk 2 maxblk 2

*** 2018-05-07T14:53:50.255488+08:00
Start dump data blocks tsn: 5 file#:5 minblk 3 maxblk 3

Block dump from disk:
buffer tsn: 5 rdba: 0x01400003 (5/3)
scn: 0x1b3845 seq: 0x01 flg: 0x04 tail: 0x38451e01
frmt: 0x02 chkval: 0x41c3 type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
File Space Bitmap Block:
BitMap Control:
RelFno: 5, BeginBlock: 128, Flag: 0, First: 0, Free: 63488
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
............
End dump data blocks tsn: 5 file#: 5 minblk 3 maxblk 3

First: 0 表示该文件空。

SQL> create table tb_tet (id number) tablespace test;

Table created.

SQL> insert into tb_tet values (1);

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

SQL> alter system dump datafile 5 block 2;

System altered.

SQL> alter system dump datafile 5 block 3;

System altered.

Block dump from disk:
buffer tsn: 5 rdba: 0x01400002 (5/2)
scn: 0x1b3845 seq: 0x02 flg: 0x04 tail: 0x38451d02
frmt: 0x02 chkval: 0xbb5a type: 0x1d=KTFB Bitmapped File Space Header
Hex dump of block: st=0, typ_found=1
File Space Header Block:
Header Control:
RelFno: 5, Unit: 8, Size: 1280, Flag: 1(0x1)
AutoExtend: NO, Increment: 0, MaxSize: 0
Initial Area: 126, Tail: 1279, First: 1,

Deallocation scn: 0x0000000000000003
Header Opcode:
Save: No Pending Op
End dump data blocks tsn: 5 file#: 5 minblk 2 maxblk 2

Size: select 1280*8 from dual; --10m
Unit: 8 一个unit表示8个块

*** 2018-05-07T14:53:50.255488+08:00
Start dump data blocks tsn: 5 file#:5 minblk 3 maxblk 3      
Block dump from disk:
buffer tsn: 5 rdba: 0x01400003 (5/3)
scn: 0x1b3845 seq: 0x01 flg: 0x04 tail: 0x38451e01
frmt: 0x02 chkval: 0x41c3 type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
File Space Bitmap Block:
BitMap Control:
RelFno: 5, BeginBlock: 128, Flag: 0, First: 1, Free: 63487
0100000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
...........
0000000000000000 0000000000000000 0000000000000000 0000000000000000
End dump data blocks tsn: 5 file#: 5 minblk 3 maxblk 3

01--00000001-->00010000
First: 1 表示从第一位开始下面的块为空闲块
Free: 63487 表是位图块的空闲solt,我们使用了一个solt剩下就是Free: 63487 了。


经过一段时间的使用在看看:

SQL> alter system dump datafile 5 block 2;

System altered.

SQL> alter system dump datafile 5 block 3;

*** 2018-05-09T09:45:46.015965+08:00
File Space Header Block:
Header Control:
RelFno: 5, Unit: 8, Size: 1280, Flag: 1(0x1)
AutoExtend: NO, Increment: 0, MaxSize: 0
Initial Area: 126, Tail: 1279, First: 8, Free: 136
Deallocation scn: 0x0000000000000003
Header Opcode:
Save: No Pending Op
End dump data blocks tsn: 5 file#: 5 minblk 2 maxblk 2

*** 2018-05-09T09:45:53.889619+08:00
Start dump data blocks tsn: 5 file#:5 minblk 3 maxblk 3
Block dump from disk:
buffer tsn: 5 rdba: 0x01400003 (5/3)
scn: 0x1d8259 seq: 0x01 flg: 0x04 tail: 0x82591e01
frmt: 0x02 chkval: 0x4eca type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
File Space Bitmap Block:
BitMap Control:
RelFno: 5, BeginBlock: 128, Flag: 0, First: 8, Free: 63480
FF00000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000

First: 8 --从第8个solt开始寻找空闲块。

那么如果我们把对象drop掉会怎么样?
SQL> select table_name,TABLESPACE_NAME from dba_tables where TABLESPACE_NAME='TEST';

TABLE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
TB_TET
TEST

TB_ASSM_PCTFREE
TEST

drop table tb_tet;
drop table tb_assm_pctfree;

SQL> drop table tb_tet;
drop table tb_assm_pctfree;
Table dropped.

SQL>

Table dropped.

SQL>
SQL> alter system checkpoint;

System altered.

SQL> /

System altered.

SQL> alter system dump datafile 5 block 2;

System altered.

SQL> alter system dump datafile 5 block 3;

System altered.


*** 2018-05-09T09:49:54.528443+08:00
Block dump from disk:
buffer tsn: 5 rdba: 0x01400002 (5/2)
scn: 0x1d8259 seq: 0x02 flg: 0x04 tail: 0x82591d02
frmt: 0x02 chkval: 0xbbfc type: 0x1d=KTFB Bitmapped File Space Header
Hex dump of block: st=0, typ_found=1
File Space Header Block:
Header Control:
RelFno: 5, Unit: 8, Size: 1280, Flag: 1(0x1)
AutoExtend: NO, Increment: 0, MaxSize: 0
Initial Area: 126, Tail: 1279, First: 8, Free: 136
Deallocation scn: 0x0000000000000003
Header Opcode:
Save: No Pending Op
End dump data blocks tsn: 5 file#: 5 minblk 2 maxblk 2

*** 2018-05-09T09:50:03.458663+08:00
Start dump data blocks tsn: 5 file#:5 minblk 3 maxblk 3
Block dump from disk:
buffer tsn: 5 rdba: 0x01400003 (5/3)
scn: 0x1d8259 seq: 0x01 flg: 0x04 tail: 0x82591e01
frmt: 0x02 chkval: 0x4eca type: 0x1e=KTFB Bitmapped File Space Bitmap
File Space Bitmap Block:
BitMap Control:
RelFno: 5, BeginBlock: 128, Flag: 0, First: 8, Free: 63480
FF00000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
First: 8 --没有变化

SQL> select EXTENT_ID,SEGMENT_NAME,BLOCK_ID from dba_extents where file_id=5;

no rows selected

select ORIGINAL_NAME,object_name,TS_NAME from dba_recyclebin;
ORIGINAL_NAME                  OBJECT_NAME                    TS_NAME
------------------------------ ------------------------------ ------------------------------
TB_ASSM_PCTFREE                BIN$a65WvZIsdx7gU2U4qMCrDg==$0 TEST
TB_ASSM_PCTFREE                BIN$a67yOLm6ffvgU2U4qMDDgQ==$0 TEST
TB_ASSM_PCTFREE                BIN$a7yqzRzDFBngU2U4qMB13Q==$0 TEST
TB_ASSM_PCTFREE                BIN$a7zR8JpJdofgU2U4qMDbZw==$0 TEST
TB_ASSM_PCTFREE                BIN$a7zR8JpKdofgU2U4qMDbZw==$0 TEST
TB_TET                         BIN$a7zR8JpLdofgU2U4qMDbZw==$0 TEST
TB_ASSM_PCTFREE                BIN$a7zR8JpMdofgU2U4qMDbZw==$0 TEST

7 rows selected.

SQL> purge dba_recyclebin;

DBA Recyclebin purged.

SQL> select ORIGINAL_NAME,object_name,TS_NAME from dba_recyclebin;

no rows selected

alter system checkpoint;

SQL> alter system dump datafile 5 block 2;

System altered.

SQL> alter system dump datafile 5 block 3;

System altered.


*** 2018-05-09T10:03:52.320782+08:00
Start dump data blocks tsn: 5 file#:5 minblk 2 maxblk 2

File Space Header Block:
Header Control:
RelFno: 5, Unit: 8, Size: 1280, Flag: 1(0x1)
AutoExtend: NO, Increment: 0, MaxSize: 0
Initial Area: 126, Tail: 1279, First: 0, Free: 144 --原来为136
Deallocation scn: 0x00000000001d8881
Header Opcode:
Save: No Pending Op
End dump data blocks tsn: 5 file#: 5 minblk 2 maxblk 2

*** 2018-05-09T10:03:56.660626+08:00
Start dump data blocks tsn: 5 file#:5 minblk 3 maxblk 3

Block dump from disk:
buffer tsn: 5 rdba: 0x01400003 (5/3)
scn: 0x1d8882 seq: 0x01 flg: 0x04 tail: 0x88821e01
frmt: 0x02 chkval: 0x41c5 type: 0x1e=KTFB Bitmapped File Space Bitmap
File Space Bitmap Block:
BitMap Control:
RelFno: 5, BeginBlock: 128, Flag: 0, First: 0, Free: 63488
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000

First: 0 --空间已经回收!!


我这里建立一个表看看solt单位:


drop table tb_assm_pctfree
/
create table tb_assm_pctfree
(
co1 CHAR(1000),
co2 CHAR(1000),
co3 CHAR(1000),
co4 CHAR(1000),
co5 CHAR(1000),
co6 CHAR(1000),
co7 CHAR(1000),
co8 CHAR(900)  
)PCTFREE 10
tablespace test
/

insert into tb_assm_pctfree values ('sunxianpeng','sun','sun','sun','sun','sun','sun','sun');
/
/

  select
   dbms_rowid.rowid_relative_fno(rowid) ralative_fno,
   dbms_rowid.rowid_block_number(rowid) block_number
   from tb_assm_pctfree;

RALATIVE_FNO BLOCK_NUMBER
------------ ------------
           5          131
           5          132
           5          133
           5          134
           5          135
           5          140
           5          141
           5          142

8 rows selected.

SQL> alter system checkpoint;

System altered.

alter system dump datafile 5 block 3;

File Space Bitmap Block:
BitMap Control:
RelFno: 5, BeginBlock: 128, Flag: 0, First: 1, Free: 63487
0100000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000

First: 1 :为什么插入8个数据块还是1呢?这是因为Unit: 8表示一个solt表示
8个块。我们插入9个块看看:


insert into tb_assm_pctfree values ('sunxianpeng','sun','sun','sun','sun','sun','sun','sun');

RALATIVE_FNO BLOCK_NUMBER
------------ ------------
           5          131
           5          132
           5          133
           5          134
           5          135
           5          140
           5          141
           5          142
           5          143

9 rows selected.

SQL> alter system checkpoint;

System altered.

alter system dump datafile 5 block 3;

File Space Bitmap Block:
BitMap Control:
RelFno: 5, BeginBlock: 128, Flag: 0, First: 2, Free: 63486
0300000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000

我们看看03的意思:03位十六进制转换为二进制为:00000011--第四位和高四位
调换位置就是:00110000 那么就是从第二个solt开始查找空闲块。



sunxianpeng

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

第67期:Neo4j图数据库平台架构最佳实践
【微学堂】10月18日 20:00(周四)

当下,数据的规模和类型每时每刻都在呈几何级数的增长,仅能够管理大量的数据是不够的,关键是能从海量数据中发掘出有用的信息,特别是数据之间的关联,能高效存储和处理数据之间关联的新型数据库为图数据库。 本讲座将介绍Neo4j图数据库的基本概念、设计特点、架构和经典应用场景实战分享。

进入课堂>>
TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备16024965号 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表