最近阅读了吕海波的《Oracle内核技术解密》，感觉受益匪浅，但是由于篇幅的原因，一些测试被作者略过了，估计是认为太简单了。。因此按照自己的理解补上了。
目录
ASSM与L3、L2、L1块（Auto Segment Space Management）
存储结构
区（extent）
统一区大小表空间和区的使用
创建表空间时设定区域大小为统一的值
创建一个新表空间
SQL> create tablespace tbs_01 datafile '/oradata/ORCL/tbs_01.dbf' size 50m uniform size 1m;
Tablespace created.
创建一张表
SQL> create table tb_01 (id number,name varchar2(20)) tablespace tbs_01;
Table created.
新创建的表如果不插入数据则只再数据字典中记录，并不占用空间
SQL> select table_name from dba_tables where table_name='TB_01';
TABLE_NAME
--------------------------------------------------------------------------------
TB_01
插入一条数据并提交
SQL> insert into tb_01 values(1,'HAHA');
1 row created.
SQL> select extent_id,file_id,block_id,blocks from dba_extents where segment_name='TB_01' order by extent_id;
EXTENT_ID FILE_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
0 5 128 128
SQL> commit;
Commit complete.
SQL> select extent_id,file_id,block_id,blocks from dba_extents where segment_name='TB_01' order by extent_id;
EXTENT_ID FILE_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
0 5 128 128
表示tb_01表从5号文件的第128号块开始，大小为128个块,因为db_block大小为8K，所以128个块为1M
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
SQL> select SEGMENT_NAME,BYTES/1024 KB ,BLOCKS,EXTENTS from dba_segments where segment_name='TB_01';
SEGME KB BLOCKS EXTENTS
----- ---------- ---------- ----------
TB_01 1024 128 1从查询中可以看出tb_01表是从5号文件的128块开始占用空间，从128~257号块为tb_01表的第一个区。(11GR2以后)
0~127块为文件头
文件头包括文件头块和位图块
0~1为文件头块
0~127为位图块
可以使用dump工具转储数据块
先看块头
SQL> select p.spid from v$session s,v$process p where s.paddr=p.addr and s.sid in (select userenv('sid') from dual);
SPID
------------------------
6038
SQL> alter system dump datafile 5 block 0;
System altered.
对应会话ID的trace文件就是
[oracle@localhost trace]$ ll | grep 6038
-rw-r----- 1 oracle oinstall 1162 Dec 15 23:09 orcl_ora_6038.trc
-rw-r----- 1 oracle oinstall 868 Dec 15 23:09 orcl_ora_6038.trm
[oracle@localhost trace]$ vi orcl_ora_6038.trc
Trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6038.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Build label: RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417
ORACLE_HOME: /u01/app/oracle/product/19/dbhome_1
System name: Linux
Node name: localhost.localdomain
Release: 3.10.0-957.el7.x86_64
Version: #1 SMP Thu Oct 4 20:48:51 UTC 2018
Machine: x86_64
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 33
Unix process pid: 6038, image: oracle@localhost.localdomain (TNS V1-V3)
*** 2021-12-15T23:09:46.491040-05:00
*** SESSION ID:(390.18838) 2021-12-15T23:09:46.491123-05:00
*** CLIENT ID:() 2021-12-15T23:09:46.491178-05:00
*** SERVICE NAME:(SYS$USERS) 2021-12-15T23:09:46.491191-05:00
*** MODULE NAME:(sqlplus@localhost.localdomain (TNS V1-V3)) 2021-12-15T23:09:46.491205-05:00
*** ACTION NAME:() 2021-12-15T23:09:46.491219-05:00
*** CLIENT DRIVER:(SQL*PLUS) 2021-12-15T23:09:46.491230-05:00
Start dump data blocks tsn: 8 file#:5 minblk 0 maxblk 0
Block 1 (file header) not dumped:use dump file header command ------------没有任何信息
End dump data blocks tsn: 8 file#: 5 minblk 2 maxblk 0
想要得到完整的信息，需要使用如下命令：
alter system set events 'immediate trace name file_hdrs level 1'; -----控制文件中关于datafile 的记录条目
alter system set events 'immediate trace name file_hdrs level 2'; -----包含level1 以及generic header
alter system set events 'immediate trace name file_hdrs level 3'; ---包含level2以及header information in the datafile
DATA FILE #5:
name #9: /oradata/ORCL/tbs_01.dbf--------------------------------------------文件目录
creation size=6400 block size=8192 status=0xe flg=0x1 head=9 tail=9 dup=1
pdb_id 0, tablespace 8, index=6 krfil=5 prev_file_in_ts=0 prev_file_in_pdb=0
unrecoverable scn: 0x0000000000000000 01/01/1988 00:00:00
Checkpoint cnt:2 scn: 0x00000000002aff7d 12/15/2021 22:14:04------------------checkpoint次数及scn
Stop scn: 0xffffffffffffffff 12/15/2021 22:14:03--0xffff.ffffffff表示该文件正被打开，如果文件未打开表示最后关闭的SCN
Creation Checkpointed at scn: 0x00000000002aff7c 12/15/2021 22:14:03
thread:1 rba:(0xe.28653.10)---------------------------------------------------内存块地址redo block address
enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
......
00000000 00000000 00000000 00000000 00000000 00000000
Offline scn: 0x0000000000000000 prev_range: 0
Online Checkpointed at scn: 0x0000000000000000------表示此数据文件online还是offline，用于快速跳过archive log恢复
thread:0 rba:(0x0.0.0)
enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
......此处全是0 省略
00000000 00000000 00000000 00000000 00000000 00000000
Hot Backup end marker scn: 0x0000000000000000
aux_file is NOT DEFINED
Plugged readony: NO
Plugin scnscn: 0x0000000000000000
Plugin resetlogs scn/timescn: 0x0000000000000000 01/01/1988 00:00:00
Foreign creation scn/timescn: 0x0000000000000000 01/01/1988 00:00:00
Foreign checkpoint scn/timescn: 0x0000000000000000 01/01/1988 00:00:00
Online move state: 0
V10 STYLE FILE HEADER:
Compatibility Vsn = 318767104=0x13000000
Db ID=1618400676=0x6076d5a4, Db Name='ORCL'
Activation ID=0=0x0
Control Seq=11795=0x2e13, File size=6400=0x1900----------------表示控制文件序列号，更新控制文件的时候也会更新seq,如果控制文件中的control seq小于数据文件中的control sel，表示控制文件来自于备份
File Number=5, Blksiz=8192, File Type=3 DATA
Tablespace #8 - TBS_01 rel_fn:5---------------------------------------表空间号
Creation at scn: 0x00000000002aff7c 12/15/2021 22:14:03------------表空间创建时间
Backup taken at scn: 0x0000000000000000 01/01/1988 00:00:00 thread:0
reset logs count:0x41018a28 scn: 0x00000000001d4fd1
prev reset logs count:0x3bf3129f scn: 0x0000000000000001
recovered at 01/01/1988 00:00:00
status:0x4 root dba:0x00000000 chkpt cnt: 2 ctl cnt:1
begin-hot-backup file size: 0--------------------------当backup scn被更新，它才就更新
Checkpointed at scn: 0x00000000002aff7d 12/15/2021 22:14:04---每个检查点都会更新此信息，除了在热备份的时候
thread:1 rba:(0xe.28653.10)
enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
......
00000000 00000000 00000000 00000000 00000000 00000000
Backup Checkpointed at scn: 0x0000000000000000
thread:0 rba:(0x0.0.0)
enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
......
00000000 00000000 00000000 00000000 00000000 00000000
External cache id: 0x0 0x0 0x0 0x0
Absolute fuzzy scn: 0x0000000000000000
Recovery fuzzy scn: 0x0000000000000000 01/01/1988 00:00:00
Terminal Recovery Stamp 01/01/1988 00:00:00
PDB ID=0, PDB Db ID=0=0x0, PDB UID=0=0x0
Previous recovery fuzzy scn: 0x0000000000000000
Last deallocation scn: 0x0000000000000000
Plugged-in scn: 0x0000000000000000
Plugin resetlogs scn: 0x0000000000000000
Foreign creation scn: 0x0000000000000000
Foreign checkpoint scn: 0x0000000000000000
EOF section checkpoint scn: 0x0000000000000000
Undo optimization current scn: 0x0000000000000000
File key structure: ena 0 flg 0x0 mkloc 0
key: 0000000000000000000000000000000000000000000000000000000000000000
mkeyid: 00000000000000000000000000000000
Last read CF transaction OCX clock 0
PDB plugin SCN for converted tts filescn: 0x0000000000000000
Platform Information: Creation Platform ID: 13
Current Platform ID: 13 Last Platform ID: 13
PDB incarnation 0: inc_scn 0x0000000000000000 inc_time 0, br_scn 0x0000000000000000 br_time 0, er_scn 0x0000000000000000 er_time 0
位图块：用于记录表空间中区的分配情况，位图块中每一个二进制位对应一个区是否分配给某个表、索引等对象。如果第一个二进制为为0，表示表空间中第一个区域未分配，如果为1则表示已分配。第二个二进制位对应第二个区，以此类推
第一个位图块为位图段头也就是2号块（Bitmapped File Space Header）
查看位图段头信息
转储5号文件的第2号数据块
00:39:15 SQL> alter system dump datafile 5 block 2;
System altered.
Start dump data blocks tsn: 8 file#:5 minblk 2 maxblk 2
Block dump from cache:
Dump of buffer cache at level 3 for pdb=0 tsn=8 rdba=20971522
BH (0x783de390) file#: 5 rdba: 0x01400002 (5/2) class: 13 ba: 0x78118000
set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,0
dbwrid: 0 obj: -1 objn: 2 tsn: [0/8] afn: 5 hint: f
hash: [0x6c205c18,0x6c205c18] lru: [0x783de5e0,0x783de310]
ckptq: [NULL] fileq: [NULL]
objq: [0x6181bf60,0x783e9468] objaq: [0x783de618,0x6181bf50]
st: XCURRENT md: NULL fpin: 'ktfbwh00: ktfbhfmt' fscn: 0x2aff83 tch: 2
flags: block_written_once
LRBA: [0x0.0.0] LSCN: [0x0] HSCN: [0x2affde] HSUB: [1]
Printing buffer operation history (latest change first):
cnt: 15
01. sid:07 L192:kcbbic2:bic:FBD 02. sid:07 L191:kcbbic2:bic:FBW
03. sid:07 L602:bic1_int:bis:FWC 04. sid:07 L822:bic1_int:ent:rtn
05. sid:07 L832:oswmqbg1:clr:WRT 06. sid:07 L930:kubc:sw:mq
07. sid:07 L913:bxsv:sw:objq 08. sid:07 L608:bxsv:bis:FBW
09. sid:07 L607:bxsv:bis:FFW 10. sid:13 L508:zar:bic:FIM
11. sid:13 L464:chg1_mn:bic:FMS 12. sid:13 L778:chg1_mn:bis:FMS
13. sid:13 L464:chg1_mn:bic:FMS 14. sid:13 L778:chg1_mn:bis:FMS
15. sid:13 L353:gcur:set:MEXCL 16. sid:13 L464:chg1_mn:bic:FMS
buffer tsn: 8 rdba: 0x01400002 (5/2)
scn: 0x2affde seq: 0x02 flg: 0x04 tail: 0xffde1d02
frmt: 0x02 chkval: 0xf48f type: 0x1d=KTFB Bitmapped File Space Header-------------这里表明了此块为位图块头
第二个位图块也就是3号块开始为位图数据
查看位图信息
SQL> alter system dump datafile 5 block 3;
System altered.
frmt: 0x02 chkval: 0x4e0d type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000078212000 to 0x0000000078214000
078212000 0000A21E 01400003 002AFFDE 04010000 [......@...*.....]
078212010 00004E0D 00000005 00000080 00000000 [.N..............]
078212020 00000001 0000F7FF 00000000 00000000 [................]
078212030 00000000 00000000 00000001 00000000 [................]
078212040 00000000 00000000 00000000 00000000 [................]
Repeat 506 times
078213FF0 00000000 00000000 00000000 FFDE1E01 [................]
File Space Bitmap Block:
BitMap Control:
RelFno: 5, BeginBlock: 128, Flag: 0, First: 1, Free: 63487------------------------------RelFno 文件号
0100000000000000 0000000000000000 0000000000000000 0000000000000000 BeginBlock 起始位置
0000000000000000 0000000000000000 0000000000000000 0000000000000000 Flag 0 (0代表永久文件1为非永久)
0000000000000000 0000000000000000 0000000000000000 0000000000000000 First1 Free63487 表示已用和剩余区域
......
块中用16进制来表示2进制，应该将16进制转化为2进制，查看二进制1的个数来计算起始区的个数。
更简单的计算方法是：每个16进制最多表示4个1，分别是十六进制1–二进制1，十六进制3–二进制11，十六进制7–二进制11，十六进制F–二进制1111 来自 <http://www.htsjk.com/shujukunews/6777.html>
手动把表分配几个区
SQL> alter table tb_01 allocate extent(size 5m);
Table altered.
SQL> select tablespace_name,extent_id,file_id,block_id,blocks from dba_extents where tablespace_name='TBS_01';
TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS
------------------------------ ---------- ---------- ---------- ----------
TBS_01 0 5 128 128
TBS_01 1 5 256 128
TBS_01 2 5 384 128
TBS_01 3 5 512 128
TBS_01 4 5 640 128
TBS_01 5 5 768 128
SQL> alter system dump datafile 5 block 3;
System altered.
File Space Bitmap Block:
BitMap Control:
RelFno: 5, BeginBlock: 128, Flag: 0, First: 6, Free: 63482-----------------分配6，剩余63482 其中3F的二进制正好是111111
3F00000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
......
系统管理区大小
在系统管理区中，分配规则不由认为决定，而是交给Oracle决定
创建新的表空间，并新建表。
SQL> create tablespace tbs_02 datafile '/oradata/ORCL/tbs_02.dbf' size 50m reuse;
Tablespace created.
SQL> create table tb_02 (id number,name varchar2(20)) tablespace tbs_02;
Table created.
查看他的分区大小
SQL> select tablespace_name,extent_id,file_id,block_id,blocks from dba_extents where tablespace_name='TBS_02';
TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS
------------------------------ ---------- ---------- ---------- ----------
TBS_02 0 2 128 8
只有8个块 64KB
向其插入数据
SQL> insert into tb_02 select rownum,'HAHA' from dba_objects;
18237 rows created.
SQL> select tablespace_name,extent_id,file_id,block_id,blocks from dba_extents where tablespace_name='TBS_02';
TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS
------------------------------ ---------- ---------- ---------- ----------
TBS_02 0 2 128 8
TBS_02 1 2 136 8
TBS_02 2 2 144 8
TBS_02 3 2 152 8
TBS_02 4 2 160 8
TBS_02 5 2 168 8
TBS_02 6 2 176 8
TBS_02 7 2 184 8
TBS_02 8 2 192 8
插入18237行后占用了8个区 每个区都是64KB
继续插入
SQL> insert into tb_02 select * from tb_02;
18237 rows created.
SQL> insert into tb_02 select * from tb_02;
36474 rows created.
SQL> select tablespace_name,extent_id,file_id,block_id,blocks from dba_extents where tablespace_name='TBS_02';
TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS
------------------------------ ---------- ---------- ---------- ----------
TBS_02 0 2 128 8
TBS_02 1 2 136 8
TBS_02 2 2 144 8
TBS_02 3 2 152 8
TBS_02 4 2 160 8
TBS_02 5 2 168 8
TBS_02 6 2 176 8
TBS_02 7 2 184 8
TBS_02 8 2 192 8
TBS_02 9 2 200 8
TBS_02 10 2 208 8
TBS_02 11 2 216 8
TBS_02 12 2 224 8
TBS_02 13 2 232 8
TBS_02 14 2 240 8
TBS_02 15 2 248 8
TBS_02 16 2 256 128
可以发现到地16个区的时后变成了128个块也就是1MB结论:在系统管理区大小表空间中，取得大小随表的增大而增大
段中块的使用（segment）
段知道表储存空间，前面的区就属于段
初始情况下表ID一旦创建就不会改变，而段ID是会变化的，当表被Tuncate是，原来的段会被删除，在再为表新建一个段，将原来储存的空间释放，重新分配新的区，这样就会出现表ID没变，但段ID变了
多次执行可以发现，每当truncate后OBJECT_ID不变，DARA_OBJECT_ID+1
查看表的ID和段的ID
SQL> select object_name,object_id,data_object_id from dba_objects where owner='TEST';
OBJECT_NAM OBJECT_ID DATA_OBJECT_ID -----------------OBJECT_ID为表ID，DATA_OBJECT_ID为段ID
---------- ---------- --------------
TB_02 74495 74495
截断表查看状态ID
SQL> insert into tb_02 values(1,'HAHA');
1 row created.
SQL> commit;
Commit complete.
SQL> truncate table tb_02;
Table truncated.
SQL> select object_name,object_id,data_object_id from dba_objects where owner='TEST';
OBJECT_NAM OBJECT_ID DATA_OBJECT_ID
---------- ---------- --------------
TB_02 74495 74496
重复一次
SQL> insert into tb_02 values(2,'HEHE');
1 row created.
SQL> commit;
Commit complete.
SQL> truncate table tb_02;
Table truncated.
SQL> select object_name,object_id,data_object_id from dba_objects where owner='TEST';
OBJECT_NAM OBJECT_ID DATA_OBJECT_ID
---------- ---------- --------------
TB_02 74495 74497
块中空间的使用
测试数据删除后回滚数据的位置
删除后回滚验证行的位置
SQL> SELECT DBMS_ROWID.rowid_relative_fno(rowid) FNO,dbms_rowid.rowid_block_number(rowid) block_id,dbms_rowid.ROWID_ROW_NUMBER(rowid) row_id,id,name from tb_02;
no rows selected
SQL> insert into tb_02 values(1,'haha');
1 row created.
SQL> insert into tb_02 values(2,'hehe');
1 row created.
SQL> insert into tb_02 values(3,'hoho');
1 row created.
SQL> insert into tb_02 values(4,'huhu');
1 row created.
SQL> commit;
Commit complete.
SQL> SELECT DBMS_ROWID.rowid_relative_fno(rowid) FNO,dbms_rowid.rowid_block_number(rowid) block_id,dbms_rowid.ROWID_ROW_NUMBER(rowid) row_id,id,name from tb_02;
FNO BLOCK_ID ROW_ID ID NAME
---------- ---------- ---------- ---------- ----------------------------------------
2 180 0 1 haha
2 180 1 2 hehe
2 180 2 3 hoho
2 180 3 4 huhu
SQL> delete from tb_02 where id=3;
1 row deleted.
SQL> SELECT DBMS_ROWID.rowid_relative_fno(rowid) FNO,dbms_rowid.rowid_block_number(rowid) block_id,dbms_rowid.ROWID_ROW_NUMBER(rowid) row_id,id,name from tb_02;
FNO BLOCK_ID ROW_ID ID NAME
---------- ---------- ---------- ---------- ----------------------------------------
2 180 0 1 haha
2 180 1 2 hehe
2 180 3 4 huhu
SQL> rollback;
Rollback complete.
SQL> SELECT DBMS_ROWID.rowid_relative_fno(rowid) FNO,dbms_rowid.rowid_block_number(rowid) block_id,dbms_rowid.ROWID_ROW_NUMBER(rowid) row_id,id,name from tb_02;
FNO BLOCK_ID ROW_ID ID NAME
---------- ---------- ---------- ---------- ----------------------------------------
2 180 0 1 haha
2 180 1 2 hehe
2 180 2 3 hoho
2 180 3 4 huhu
堆表是否有序
测试删除后再插入相同数据时，数据存放位置
删除后再插入验证行的位置
SQL> delete from tb_02 where id=3;
1 row deleted.
SQL> insert into tb_02 values(3,'hoho');
1 row created.
SQL> SELECT DBMS_ROWID.rowid_relative_fno(rowid) FNO,dbms_rowid.rowid_block_number(rowid) block_id,dbms_rowid.ROWID_ROW_NUMBER(rowid) row_id,id from tb_02;
FNO BLOCK_ID ROW_ID ID NAME
---------- ---------- ---------- ---------- ----------------------------------------
2 180 0 1 haha
2 180 1 2 hehe
2 180 3 4 huhu
2 180 4 3 hoho
ASSM与L3、L2、L1块（Auto Segment Space Management）
ASSM管理的整体结构
L3块一般是段头，存放着L2块的信息，如果存满了，就会再分配一个L3块
查看段头位置
SQL> create tablespace tbs_03 datafile '/oradata/ORCL/tbs_03.dbf' size 50m reuse;
Tablespace created.
SQL> create table tb_03 (id number,name varchar2(20)) tablespace tbs_03;
Table created.
SQL> insert into tb_03 values(1,'haha');
1 row created.
SQL> commit;
Commit complete.
SQL> select segment_name,HEADER_FILE,HEADER_BLOCK from dba_segments where segment_name='TB_03';
SEGMENT_NA HEADER_FILE HEADER_BLOCK
---------- ----------- ------------
TB_03 8 130------------------130号块为L3块
转储130号块
Start dump data blocks tsn: 10 file#:8 minblk 130 maxblk 130
Block dump from cache:
Dump of buffer cache at level 3 for pdb=0 tsn=10 rdba=33554562
BH (0x73bda9e8) file#: 8 rdba: 0x02000082 (8/130) class: 4 ba: 0x738c6000
.....
buffer tsn: 10 rdba: 0x02000082 (8/130)
scn: 0x2cae5f seq: 0x03 flg: 0x04 tail: 0xae5f2303
frmt: 0x02 chkval: 0x88ba type: 0x23=PAGETABLE SEGMENT HEADER
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00000000738C6000 to 0x00000000738C8000
0738C6000 0000A223 02000082 002CAE5F 04030000 [#......._.,.....]-------与块类型对应0x23
.....
Repeat 185 times
0738C7FF0 00000000 00000000 00000000 AE5F2303 [.............#_.]
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 8------一个区8个块
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x02000088 ext#: 0 blk#: 8 ext size: 8---------高水位为136号块也就是第二个区的第一个块
#blocks in seg. hdr's freelists: 0
#blocks below: 5
mapblk 0x00000000 offset: 0
Unlocked
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x02000088 ext#: 0 blk#: 8 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 5
mapblk 0x00000000 offset: 0
Level 1 BMB for High HWM block: 0x02000080
Level 1 BMB for Low HWM block: 0x02000080
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x02000081---------------------------------此处记录L2块的地址
Last Level 1 BMB: 0x02000080
Last Level II BMB: 0x02000081
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 1 obj#: 74524 flag: 0x10000000
Inc # 0
Extent Map-----------------------------------------------区域位图，因为该段只有一个区所以只有一行
-----------------------------------------------------------------
0x02000080 length: 8
Auxillary Map--------------------------------------区由哪个L1管理及所管理的区-数据块的起始位置DBA
--------------------------------------------------------
Extent 0 : L1 dba: 0x02000080 Data dba: 0x02000083 ----------记录L1管理的区域地址
--------------------------------------------------------
Second Level Bitmap block DBAs-----------------------------------此处记录L2数量，目前只有一个L2快
--------------------------------------------------------
DBA 1: 0x02000081
End dump data blocks tsn: 10 file#: 8 minblk 130 maxblk 130
L2块存放多个L1块的地址
转储129号块
Block dump from disk:
buffer tsn: 10 rdba: 0x02000081 (8/129)
scn: 0x2cae5f seq: 0x01 flg: 0x04 tail: 0xae5f2101
frmt: 0x02 chkval: 0xa696 type: 0x21=SECOND LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F8D1E77B000 to 0x00007F8D1E77D000
7F8D1E77B000 0000A221 02000081 002CAE5F 04010000 [!......._.,.....]-------与块类型对应0x21
.....
Repeat 502 times
7F8D1E77CFF0 00000000 00000000 00000000 AE5F2101 [.............!_.]
Dump of Second Level Bitmap Block
number: 1 nfree: 1 ffree: 0 pdba: 0x02000082------父DBA地址也就是L3的地址
Inc #: 0 Objd: 74524 Flag: 3
opcode:0
xid:
L1 Ranges :--------------------------------------------多个L1时插入数据时：再根据PID进行HASH，得到一个随机值，根据此值选择L2中的L1
--------------------------------------------------------
0x02000080 Free: 7 Inst: 1-------------------------------free:5表示L1中可用空间状态，free:0表示full
--------------------------------------------------------
End dump data blocks tsn: 10 file#: 8 minblk 129 maxblk 129
L1块存放数据块的地址（点击查看L1块内容）
根据L1 Range的信息转储L1块
转储128号块
Block dump from disk:
buffer tsn: 10 rdba: 0x02000080 (8/128)
scn: 0x2cae5f seq: 0x02 flg: 0x04 tail: 0xae5f2002
frmt: 0x02 chkval: 0x2666 type: 0x20=FIRST LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F4505AB8000 to 0x00007F4505ABA000
7F4505AB8000 0000A220 02000080 002CAE5F 04020000 [ ......._.,.....]-------与块类型对应0x21
.....
Repeat 386 times
7F4505AB9FF0 00000000 00000000 00000000 AE5F2002 [............. _.]
Dump of First Level Bitmap Block
--------------------------------
nbits : 4 nranges: 1 parent dba: 0x02000081 poffset: 0-------上级块地址129号块
unformatted: 0 total: 8 first useful block: 3
owning instance : 1
instance ownership changed at 12/17/2021 00:34:21
Last successful Search 12/17/2021 00:34:21
Freeness Status: nf1 0 nf2 0 nf3 0 nf4 0 nf5 5
Extent Map Block Offset: 4294967295
First free datablock : 3
Bitmap block lock opcode 0
Locker xid: : 0x0000.000.00000000
Dealloc scn(ub4/ub4): (0x00000000.001d4fd3)
Format scn: 0x00000000002cae5f
Flag: 0x000002a1 (REJCTX/-/AUX/-/OBJD/-/-/-)
Inc #: 0 Objd: 74524
HWM Flag: HWM Set
Highwater:: 0x02000088 ext#: 0 blk#: 8 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 5
mapblk 0x00000000 offset: 0
--------------------------------------------------------
DBA Ranges :---------------------------------------------------L1的管理范围
--------------------------------------------------------
0x02000080 Length: 8 Offset: 0
0:Metadata 1:Metadata 2:Metadata 3:100% free
4:100% free 5:100% free 6:100% free 7:100% free
--------------------------------------------------------
ktspfsc -
nro:0 ncmp:0 nff:0 nxo:0 lastxs:0 nxid:0 ff:0
clntime: 0 addtime:0 spare1:0 spare2:0
ro: rejection opcode, xo: xid offset List
0. ro:0 xo:-1 1. ro:0 xo:-1 2. ro:0 xo:-1 3. ro:0 xo:-1
4. ro:0 xo:-1 5. ro:0 xo:-1 6. ro:0 xo:-1 7. ro:0 xo:-1
ktspfsc xidlist -
0. NULL 1. NULL 2. NULL
3. NULL 4. NULL 5. NULL
6. NULL 7. NULL 8. NULL
9. NULL 10. NULL 11. NULL
12. NULL 13. NULL 14. NULL
15. NULL 16. NULL 17. NULL
18. NULL 19. NULL 20. NULL
21. NULL 22. NULL 23. NULL
24. NULL 25. NULL 26. NULL
27. NULL 28. NULL 29. NULL
30. NULL 31. NULL 32. NULL
33. NULL 34. NULL 35. NULL
36. NULL 37. NULL 38. NULL
39. NULL 40. NULL 41. NULL
42. NULL 43. NULL 44. NULL
45. NULL 46. NULL 47. NULL
48. NULL 49. NULL 50. NULL
51. NULL 52. NULL 53. NULL
54. NULL 55. NULL 56. NULL
57. NULL 58. NULL 59. NULL
60. NULL 61. NULL 62. NULL
63. NULL
ktspfsr not configured -
End dump data blocks tsn: 10 file#: 8 minblk 128 maxblk 128DBA（data block address）转换方法
将DBA转换成文件块位置
SQL> select to_number('02000081','xxxxxxxx') from dual;
TO_NUMBER('02000081','XXXXXXXX')
--------------------------------
33554561
SQL> SELECT dbms_utility.data_block_address_block(33554561) "BLOCK",dbms_utility.data_block_address_file(33554561) "FILE" FROM dual;
BLOCK FILE
---------- ----------
129 8-----------------------------------L2块的位置为8号文件的第129块
手工转换dba方法
以0x02000080为例
| dba | 16进制 | 2进制 |
| 0x02000080 | 0000001000 0000000000000010000000 |
二进制前10位为文件号位，后22位为块号位
| 2进制 | 10进制 |
| 0000001000 | 8 |
| 0000000000000010000000 |
ASSM管理，插入数据步骤
1、查找数据字典dba_segments确定段头位置(查询段头位置)
2、再段头出查找第一个L2块的位置信息（查看）
3、再L2块中根据执行插入操作的进程PID，做hash运算，得出一个随机数X，在L2中找到第X个L1块的信息（查看）
4、在第3步确定的L1块中继续根据PID进行hash运算得到随机数Y，找到第Y号数据块
5、向Y号数据块插入数据（查看）
查看tb_03表中数据位置
SQL> SELECT DBMS_ROWID.rowid_relative_fno(rowid) FNO,dbms_rowid.rowid_block_number(rowid) block_id,dbms_rowid.ROWID_ROW_NUMBER(rowid) row_id,id,name from tb_03;
FNO BLOCK_ID ROW_ID ID NAME
---------- ---------- ---------- ---------- --------------------
8 134 0 1 haha
它并没有插入到端头块后面的块也就是131号块，而是第134号块段头与ExtentMap
查看map中的内容
Extent Map
-----------------------------------------------------------------
0x02000080 length: 8 --------------------------------------------4,128
0x02000088 length: 8 --------------------------------------------4,136
0x02000090 length: 8 --------------------------------------------4,144
0x02000098 length: 8 --------------------------------------------4,152
0x020000a0 length: 8 --------------------------------------------4,160
0x020000a8 length: 8 --------------------------------------------4,168
0x020000b0 length: 8 --------------------------------------------4,176
0x020000b8 length: 8 --------------------------------------------4,184
0x020000c0 length: 8 --------------------------------------------4,192
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x02000080 Data dba: 0x02000083 ----------data dba 为数据开始的位置 4,131
Extent 1 : L1 dba: 0x02000080 Data dba: 0x02000088 ----------136
Extent 2 : L1 dba: 0x02000090 Data dba: 0x02000091 ----------145
Extent 3 : L1 dba: 0x02000090 Data dba: 0x02000098 ----------152
Extent 4 : L1 dba: 0x020000a0 Data dba: 0x020000a1 ----------161
Extent 5 : L1 dba: 0x020000a0 Data dba: 0x020000a8 ----------168
Extent 6 : L1 dba: 0x020000b0 Data dba: 0x020000b1 ----------177
Extent 7 : L1 dba: 0x020000b0 Data dba: 0x020000b8 ----------184
Extent 8 : L1 dba: 0x020000c0 Data dba: 0x020000c1 ----------193
--------------------------------------------------------
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x02000081
End dump data blocks tsn: 10 file#: 8 minblk 130 maxblk 130全表扫描模拟
1、通过dba_segments中的内容确定段头位置(Oracle本身是从seg$中查询)
2、找到段头位置后会查看他的Extent Map、Auxillary Map
3、按照地图顺序读取每个区。
全表扫描逻辑读模拟
SQL> create tablespace tbs_04 datafile'/oradata/ORCL/tbs_04.dbf' size 20m reuse uniform size 48k;
Tablespace created.
SQL> create table tb_04(id number,name varchar2(20)） tablespace tbs_04;
Table created.
SQL> insert into tb_04 values(1,'HAHA');
1 row created.
SQL> commit;
Commit complete.
查询测试
SQL> set autotrace on;
SQL> select * from tb_04;
ID NAME
---------- --------------------
1 HAHA
Execution Plan
----------------------------------------------------------
Plan hash value: 729550918
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TB_04 | 1 | 25 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
11 recursive calls
10 db block gets
17 consistent gets
2 physical reads
2012 redo size
826 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
多次执行该查询
SQL> select * from tb_04;
ID NAME
---------- --------------------
1 HAHA
Execution Plan
----------------------------------------------------------
Plan hash value: 729550918
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TB_04 | 1 | 25 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets-----------------------多次执行后逻辑读变为4，因为该表空间区只有48K大小，就是6个块出去前三个块L1、L2、L3以后还剩下3个块，L3块因为要读取extent map和auxillary map因此要读两次，因此逻辑读的数为5
0 physical reads
0 redo size
627 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
1、读取root块
2、读取叶块找到数据
3、读取数据块
4、查找叶块中还有没有其他满足条件的行
索引范围扫描模拟
在tb_01表中插入数据、建立索引，并重新收集统计信息
SQL> insert into tb_01 select rownum,'HAHA' from dba_objects;
72968 rows created.
SQL> create index ind_01 on tb_01(id) tablespace tbs_01;
Index created.
SQL> exec dbms_stats.gather_table_stats('TEST','TB_01');
PL/SQL procedure successfully completed.
SQL> select bLevel from dba_indexes where index_name='TB_01' and owner='TEST';
BLEVEL
----------
1
多执行几次查询
SQL> set line 200 pagesize 200
SQL> select * from tb_01 where id='10';
ID NAME
---------- --------------------
10 HAHA
Execution Plan
----------------------------------------------------------
Plan hash value: 3014314283
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TB_01 | 1 | 10 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TB_01 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=10)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets ------------------逻辑读为4，分别是Root块1次，叶块两次，数据块一次，因为是非唯一索引，因此第一次读取叶块为取出目标行，第二次为判断此也快中还有没有满足条件的行
0 physical reads
0 redo size
627 bytes sent via SQL*Net to client
395 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
1、读取root块
2、读取叶块找到数据
3、读取数据块
唯一索引扫描
SQL> drop index table_id;
Index dropped.
SQL> create unique index ind_01 on tb_01(id) tablespace tbs_01;
Index created.
SQL> select * from tb_01 where id=10;
ID NAME
---------- --------------------
10 HAHA
SQL> select * from tb_01 where id=10;
ID NAME
---------- --------------------
10 HAHA
Execution Plan
----------------------------------------------------------
Plan hash value: 2406123479
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
| 0 | SELECT STATEMENT | | 1 | 10 | 2 (0)| 00:0
0:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_01 | 1 | 10 | 2 (0)| 00:0
0:01 |
|* 2 | INDEX UNIQUE SCAN | IND_01 | 1 | | 1 (0)| 00:0
0:01 |
--------------------------------------------------------------------------------
------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=10)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
483 bytes sent via SQL*Net to client
382 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> COL OBJECT_NAME FOR A20
SQL> set line 200
SQL> col owner for a10
SQL> select object_name,object_id,object_type,owner from dba_objects where object_name='IND_01';
OBJECT_NAME OBJECT_ID OBJECT_TYPE OWNER
-------------------- ---------- ----------------------- ----------
IND_01 75165 INDEX TEST
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug tracefile_name;
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_23888.trc
SQL> alter session set events 'immediate trace name treedump level 75165';
Session altered.
----- begin tree dump
branch: 0x1400184 20971908 (0: nrow: 162, level: 1)
leaf: 0x1400185 20971909 (-1: row:485.485 avs:824)
leaf: 0x1400186 20971910 (0: row:479.479 avs:816)
leaf: 0x1400187 20971911 (1: row:479.479 avs:816)
leaf: 0x1400188 20971912 (2: row:479.479 avs:816)
leaf: 0x1400189 20971913 (3: row:479.479 avs:816)
leaf: 0x140018a 20971914 (4: row:478.478 avs:830)
leaf: 0x140018b 20971915 (5: row:479.479 avs:816)
leaf: 0x140018c 20971916 (6: row:479.479 avs:816)
leaf: 0x140018d 20971917 (7: row:479.479 avs:816)
leaf: 0x140018e 20971918 (8: row:478.478 avs:830)
leaf: 0x140018f 20971919 (9: row:479.479 avs:816)
leaf: 0x1400190 20971920 (10: row:479.479 avs:816)
leaf: 0x1400191 20971921 (11: row:479.479 avs:816)
leaf: 0x1400192 20971922 (12: row:479.479 avs:816)
leaf: 0x1400193 20971923 (13: row:478.478 avs:830)
leaf: 0x1400194 20971924 (14: row:479.479 avs:816)
leaf: 0x1400195 20971925 (15: row:479.479 avs:816)
leaf: 0x1400196 20971926 (16: row:479.479 avs:816)
leaf: 0x1400197 20971927 (17: row:479.479 avs:816)
leaf: 0x1400198 20971928 (18: row:478.478 avs:830)
leaf: 0x1400199 20971929 (19: row:475.475 avs:819)
leaf: 0x140019a 20971930 (20: row:449.449 avs:817)
leaf: 0x140019b 20971931 (21: row:449.449 avs:816)
leaf: 0x140019c 20971932 (22: row:449.449 avs:817)
leaf: 0x140019d 20971933 (23: row:449.449 avs:816)
leaf: 0x140019e 20971934 (24: row:449.449 avs:817)
leaf: 0x140019f 20971935 (25: row:449.449 avs:816)
leaf: 0x14001a0 20971936 (26: row:449.449 avs:817)
leaf: 0x14001a1 20971937 (27: row:449.449 avs:816)
leaf: 0x14001a2 20971938 (28: row:449.449 avs:816)
leaf: 0x14001a3 20971939 (29: row:449.449 avs:817)
leaf: 0x14001a4 20971940 (30: row:449.449 avs:816)
leaf: 0x14001a5 20971941 (31: row:449.449 avs:817)
leaf: 0x14001a6 20971942 (32: row:449.449 avs:816)
leaf: 0x14001a7 20971943 (33: row:449.449 avs:817)
leaf: 0x14001a8 20971944 (34: row:449.449 avs:816)
leaf: 0x14001a9 20971945 (35: row:449.449 avs:817)
1270
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
