客服微信
本文为云贝教育 刘峰 原创，请尊重知识产权，转发请注明出处，不接受任何抄袭、演绎和未经注明出处的转载。
行迁移是由 UPDATE 语句引起的， INSERT 和 DELETE 永远不会导致行迁移。
默认情况下， Oracle 会保留一个块的 10%用于行扩展。这是段的 PCTFREE（空闲百分比）设置的，一个使用量已经超过了段的 PCTFREE 设置的块由 ASSM 位图归类为FULL，因此该块不可用于插入，即使它实际上还有 10%的空闲空间。
因此，如果块的行在其生存期间增长不超过 10%（平均而言） ，这是没有问题的：行的新版本有足够的空间可用。如果一个行的扩展使得块中没有足够的空间时，它就必须被移动到具有足够空间的块中。这称之为行迁移（Row Migration）。
与行迁移密切相关的是行链接问题。行链接（Chained Row）是比块还要大的行。显然，如果块大小是 8k，而某行是 20k，则该行必须被分布到 3 个块中。在执行插入时，所有三个块将通过搜索 ASSM 位图来定位可用于插入的块，并且以后检索该行时，所有三个块都会被读出。链接行的 ROWID 指向行的第一个块，这与行迁移的情况相同。
引用一下官方文档里的说明
行链接--一个块里只有一行
The row is too large to fit into one data block when it is first inserted. In row chaining, Oracle Database stores the data for the row in a chain of one or more data blocks reserved for the segment. Row chaining most often occurs with large rows. Examples include rows that contain a column of data type LONG or LONG RAW , or a row with a huge number of columns. Row chaining in these cases is unavoidable.
行迁移-因update导致块中某一行在块中存储不下
A row that originally fit into one data block is updated so that the overall row length increases, but insufficient free space exists to hold the updated row. In row migration, Oracle Database moves the entire row to a new data block,assuming the row can fit in a new block. The original row piece of a migrated row contains a pointer or "forwarding address" to the new block containing the migrated row. The rowid of a migrated row does not change.
一、行迁移的模拟与消除
1、创建一个4K表空间
alter system set db_4k_cache_size=1m scope=both;
drop tablespace tbs1 including contents and datafiles;
create tablespace tbs1 datafile size 10m blocksize 4k;
2、给用户分配表空间配额
alter user u1 quota 1m on tbs1;
3、创建表。字段类型为char的目的为了将4K表空间一个块占满
conn u1/u1@pdb2
create table row_chain_demo(
x int primary key,
a char(1000),
b char(1000),
c char(1000),
d char(1000)
) tablespace tbs1;
4、插入数据
insert into row_chain_demo(x,a,b,c,d) values(1,'a','b','c','d');
commit;
5、分析测试表，检查行链接
--首先建chaind_rows相关表
conn sys/oracle@pdb2
drop table CHAINED_ROWS;
@?/rdbms/admin/utlchain.sql
--分析表
analyze table u1.row_chain_demo list chained rows into chained_rows;
6、查看行链接结果
select * from chained_rows where table_name='ROW_CHAIN_DEMO';
OWNER_NAME TABLE_NAME CLUSTER_NAME
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
PARTITION_NAME SUBPARTITION_NAME HEAD_ROWID ANALYZE_TIMESTAMP
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------ -------------------
U1 ROW_CHAIN_DEMO
N/A AAAR2YAAcAAAAEKAAA 2023-06-30 09:56:02
7、查看有行链接的情况下，执行计划扫描的块数
SYS@pdb> select /*+index(ROW_CHAIN_DEMO,x)*/* from u1.ROW_CHAIN_DEMO where x=1;
xecution Plan
----------------------------------------------------------
Plan hash value: 3029137777
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4007 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ROW_CHAIN_DEMO | 1 | 4007 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C007584 | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("X"=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
4869 bytes sent via SQL*Net to client
432 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
8、查看该行的文件号块号
SYS@pdb> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# from u1.row_chain_demo;
FILE# BLOCK#
---------- ----------
28 266
9、dump 28号文件266号
oradebug setmypid
alter system dump datafile 28 block 266;
oradebug close_trace;
oradebug tracefile_name
dump内容如下
data_block_dump,data header at 0x7aa9d064
===============
tsiz: 0xf98
hsiz: 0x14
pbl: 0x7aa9d064
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0xba1
avsp=0xb8d
tosp=0xb8d
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0xba1
block_row_dump:
tab 0, row 0, @0xba1
tl: 1015 fb: --H-F--- lb: 0x1 cc: 2 -->正常的行记录为--H-FL--,而这里为只有F(fisrt)而没有L(last)，说明在这个数据块中只有行的开始，而没有行的结束，同样cc为2说明这个块中只包含了表的两个列
nrid: 0x07000109.0 -->nrid表示数据块的下一个指针，即其他列数据存放的数据块地址
col 0: [ 2] c1 02
col 1: [1000]
61 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 2
10、查看行链接指向的块
select dbms_utility.data_block_address_file(to_number(ltrim('0x07000109','0x'),'xxxxxxxx')) file_id,
dbms_utility.data_block_address_block(to_number(ltrim('0x07000109','0x'),'xxxxxxxx')) block_id from dual;
FILE_ID BLOCK_ID
---------- ----------
28 265
11、继续dump 28号文件265号
data_block_dump,data header at 0x7f2993e5407c
===============
tsiz: 0xf80
hsiz: 0x14
pbl: 0x7f2993e5407c
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x3bc
avsp=0x3a8
tosp=0x3a8
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x3bc
block_row_dump:
tab 0, row 0, @0x3bc
tl: 3012 fb: -----L-- lb: 0x1 cc: 3 -->这里表示行的LAST尾部
col 0: [1000]
62 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
既然一个块装载不下一行数据，那可以用更大的块来装载，这样就可以消除行迁移
1、创建32kb的表空间
--CDB执行
alter system set db_32k_cache_size=1m scope=both;
--pdb执行
drop tablespace tbs2 including contents and datafiles;
create tablespace tbs2 datafile size 10m blocksize 32k;
alter user u1 quota unlimited on tbs2;
2、将目标表移动到新的表空间
alter table u1.row_chain_demo move tablespace tbs2;
3、将主键索引rebuild
select index_name from dba_indexes where table_name='ROW_CHAIN_DEMO';
INDEX_NAME
--------------------------------------------------------------------------------------------------------------------------------
SYS_C007584
alter index u1.SYS_C007584 rebuild;
4、清除行连接记录表，再次分析，查看行链接消除
delete from chained_rows;
commit;
analyze table u1.ROW_CHAIN_DEMO list chained rows into chained_rows;
select * from chained_rows where table_name='CHAIN_ROW_DEMO';
--无数据
5、再次查看执行计划
select /*+index(ROW_CHAIN_DEMO,x)*/* from u1.ROW_CHAIN_DEMO where x=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3029137777
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4021 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ROW_CHAIN_DEMO | 1 | 4021 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C007584 | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("X"=1)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
1 - SEL$1 / ROW_CHAIN_DEMO@SEL$1
U - index(ROW_CHAIN_DEMO,x) / index specified in the hint doesn't exist
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
4707 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
结论：消除行链接的办法，主要是通过更大块来装载数据。
通过消除行链接实验，逻辑的块数从3降为2。原因是原来一行占用2个块，现在只占用1个块。
1、模拟行迁移
1、创建行迁移的表
--创建测试表，保证修改表之后产生行迁移
drop table row_mig_demo;
create table row_mig_demo(
x int primary key,
a char(1000),
b char(1000),
c char(1000),
d char(1000),
e char(1000)
) tablespace users;
2、插入数据
insert into row_mig_demo values(1,'a','b','c','d','');
insert into row_mig_demo(x) values(2);
commit;
3、分析表发现此时没有行迁移
delete from chained_rows;
commit;
analyze table u1.row_mig_demo list chained rows into chained_rows;
select * from chained_rows where table_name='ROW_MIG_DEMO';
no rows selected
4、查看此行所在在块
select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# from row_mig_demo;
FILE# BLOCK#
---------- ----------
12 295
12 295
5、查看此时的执行计划
set autot trace
set linesize 1000
select * from ROW_MIG_DEMO where x=2;
Execution Plan
----------------------------------------------------------
Plan hash value: 4113747091
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5023 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ROW_MIG_DEMO | 1 | 5023 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C007597 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("X"=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
758 bytes sent via SQL*Net to client
388 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
6、dump 28号文件277号块
oradebug setmypid
alter system dump datafile 12 block 295;
oradebug close_trace;
oradebug tracefile_name
----dump内容如下
data_block_dump,data header at 0x7faefba4f064
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x7faefba4f064
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0xfe0
avsp=0xfc7
tosp=0xfc7
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0xfe6
0x14:pri[1] offs=0xfe0
block_row_dump:
tab 0, row 0, @0xfe6
tl: 4018 fb: --H-FL-- lb: 0x1 cc: 5
col 0: [ 2] c1 02
col 1: [1000]
61 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
。。省略第一条数据
tab 0, row 1, @0xfe0
tl: 6 fb: --H-FL-- lb: 0x1 cc: 1 -->cc: 1 --FL:说明此时数据行的头和尾都在block内，cc:1,即只有一个字段的数据
col 0: [ 2] c1 032
end_of_block_dump
7、修改第二条数据
update row_mig_demo set a='a',b='b',c='c',d='d',e='e' where x=2;
commit;
8、分析表之后发现产生了行迁移
delete from chained_rows;
commit;
analyze table row_mig_demo list chained rows into chained_rows;
select * from chained_rows where table_name='ROW_MIG_DEMO';
OWNER_NAME TABLE_NAME CLUSTER_NAME
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
PARTITION_NAME SUBPARTITION_NAME HEAD_ROWID ANALYZE_TIMESTAMP
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------ -------------------
SYS ROW_MIG_DEMO N/A AAAR3EAAMAAAAEnAAB 2023-07-01 10:04:21
9、查看执行计划
SYS@pdb> select * from ROW_MIG_DEMO where x=2;
Execution Plan
----------------------------------------------------------
Plan hash value: 4113747091
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5023 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ROW_MIG_DEMO | 1 | 5023 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C007597 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("X"=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets -->多扫描了一个块
0 physical reads
0 redo size
5931 bytes sent via SQL*Net to client
399 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
10、再次dump datafile 12 block 295;
oradebug setmypid
alter system dump datafile 12 block 295;
oradebug close_trace;
oradebug tracefile_name
--以下是dump关键信息
tab 0, row 1, @0xfd7
tl: 9 fb: --H----- lb: 0x2 cc: 0
nrid: 0x03000123.0
end_of_block_dump
查看行迁移指向块
select dbms_utility.data_block_address_file(to_number(ltrim('0x03000123','0x'),'xxxxxxxx')) file_id,
dbms_utility.data_block_address_block(to_number(ltrim('0x03000123','0x'),'xxxxxxxx')) block_id from dual;
FILE_ID BLOCK_ID
---------- ----------
12 291
继续dump datafile 12 block 291
oradebug setmypid
alter system dump datafile 12 block 291;
oradebug close_trace;
oradebug tracefile_name
--dump的关键信息
tab 0, row 0, @0xbdd
tl: 5027 fb: ----FL-- lb: 0x1 cc: 6
hrid: 0x03000127.1
col 0: [ 2] c1 03
col 1: [1000]
61 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
。。。
理一下指向关系
该行因为update之后，产生了行迁移，28.295存储了行头信息-->12.291存储了数据信息，所以扫描X=2这行时，多扫描了一个块。
2、消除方法，重建数据表
1、重建表row_mig_demo
create table row_mig_demo2 as select * from row_mig_demo;
drop table row_mig_demo;
alter table row_mig_demo2 rename to row_mig_demo;
alter table row_mig_demo add primary key(x);
2、分析表
delete from chained_rows;
commit;
analyze table u1.row_mig_demo list chained rows into chained_rows;
select * from chained_rows where table_name='ROW_MIG_DEMO';
no rows selected
3、查看执行计划
SYS@pdb> select * from ROW_MIG_DEMO where x=2;
Execution Plan
----------------------------------------------------------
Plan hash value: 4113747091
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5023 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ROW_MIG_DEMO | 1 | 5023 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C007597 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("X"=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
5931 bytes sent via SQL*Net to client
399 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
