本文介绍了Oracle中的行迁移和行链接现象，详细分析了它们的产生原因、影响，并提供了针对行迁移的优化步骤，包括使用CTAS创建新表、删除迁移行、再插入和清理临时表。
一. 概述、产生原因及影响
row migration：When a row is to be updated and it cannot find the necessary free space in its block,the oracle will move the entie row into a new block and leave a pointer from the orginal block to the new locatio.This process is called row migration
row chain：When a row is too large to fit into any block,row chaining occurs.In this case,the oracle devide the row into smaller chunks. each chunk is stored in a block along with the necessary poiters to retrive and assemble
the entire row.
行迁移：当一个行上的更新操作(原来的数据存在且没有减少)导致当前的数据不能容纳在当前块，需要进行行迁移。行迁移意味着整行数据都被移动，原始的数据块上仅仅保留的是指向新块的一个地址信息(指针)，并且该行原先空间不再被数据库使用，这些剩余的空间称之为空洞，也是产生表碎片的主要原因，表碎片基本上也是不可避免的，但是可以通过一些策略使其降低到一个可以接受的程度。注意，即使发生了行迁移，发生行迁移的行的rowid还是不会变化，这也是行迁移会引起数据库I/O性能降低的原因。可以认为行迁移是行链接的一种特殊形式，但是起因于行链接有很大不同。
产生：update
行链接：当一行数据太大而不能在一个单数据块容纳时，会产生行链接。例如当使用4kb的oracle数据块大小，而需要插入一行数据是8k,Oracle则需要使用三个数据块分成片来存储。因此，引起行链接的情形通常是，表上行记录的大小超出了数据库oracle块的大小。
产生原因：The row is too large to fit into one data block when it isfirstinserted.
表上使用了Long或Long raw数据类型的时候容易产生行链接(此时行链接通常为不可避免的)。其次表上使用多余255列时oracle会将这些过宽的表分片而产生行链接。
影响：行迁移对索引读产生额外的I/O,对全表扫描没什么影响。行连接则影响索引读和全表扫描。总的来说行迁移和行链接引起性能下降的原因主要是由于多余的I/O造成的。当通过索引访问产生行迁移的行时，数据库必须扫描一个或一个以上的数据块才能检索到该行的数据。
二. 模拟行迁移和行链接
行链接
------------------ 参考tom kyte的例子--------------------------------------------
--创建4k blocksize的表空间
SQL> alter system set db_4k_cache_size=1m scope=both;
System altered.
SQL> create tablespace tbs1 datafile '/u01/app/oracle/oradata/orcl/tbs_1.dbf' size 100m blocksize 4k;
Tablespace created.
--行链接测试
--使用定列宽的char类型来创建行链接测试表
SQL> create table row_chain_demo(
2 x int primary key,
3 a char(1000),
4 b char(1000),
5 c char(1000),
6 d char(1000)
7 ) tablespace tbs1;
Table created.
--插入数据
SQL> insert into row_chain_demo(x,a,b,c,d) values(1,'a','b','c','d');
1 row created.
SQL> commit;
Commit complete
--分析测试表，检查行链接
--首先建chaind_rows相关表
SQL> @/u01/app/oracle/product/11.2.0/rdbms/admin/utlchain.sql
Table created.
--分析表
SQL> analyze table row_chain_demo list chained rows into chained_rows;
Table analyzed.
--查询行链接
SQL> select * from chained_rows where table_name='ROW_CHAIN_DEMO';
OWNER_NAME TABLE_NAME
------------------------------ ------------------------------
CLUSTER_NAME PARTITION_NAME
------------------------------ ------------------------------
SUBPARTITION_NAME HEAD_ROWID ANALYZE_T
------------------------------ ------------------ ---------
SYS ROW_CHAIN_DEMO
N/A AAAVqhAAHAAAAEGAAA 29-OCT-16
记录下此时的执行计划：
SQL> set autotrace traceonly statistics
SQL> set linesize 100
SQL> select /*+index(ROW_CHAIN_DEMO,x)*/* from ROW_CHAIN_DEMO where x=1;
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
4815 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--找出row_chain_demo这张表所对应的数据块
SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# from row_chain_demo;
FILE# BLOCK#
---------- ----------
7 262
--dump该数据块进行分析
SQL> alter system dump datafile 7 block 262;
System altered.
--以下为trace文件的内容
data_block_dump,data header at 0x7f28ca793264
===============
tsiz: 0xf98
hsiz: 0x14
pbl: 0x7f28ca793264
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: 0x01c00105.0
-- nrid表示数据块的下一个指针，即其他列数据存放的数据块地址
--通过bbed分析
BBED> p kdbr
sb2 kdbr[0] @118 2977
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @3077 0x28
BBED> x /rnccccccc
rowdata[0] @3077
----------
flag@3077: 0x28 (KDRHFF, KDRHFH)
lock@3078: 0x01
cols@3079: 2 --从这也可以看出数据块中只有表的两个列
nrid@3080:0x01c00105.0
col 0[2] @3086: 1
col 1[1000] @3089: a
--消除行链接
--创建blocksize为32的表空间，并将测试表移动到该表空间
SQL> alter system set db_32k_cache_size=1m scope=both;
System altered.
SQL> create tablespace tbs2 datafile '/u01/app/oracle/oradata/orcl/tbs2.dbf' size 100m blocksize 32k;
Tablespace created.
SQL> alter table row_chain_demo move tablespace tbs2;
Table altered.
SQL> select index_name from dba_indexes where table_name='ROW_CHAIN_DEMO';
INDEX_NAME
------------------------------
SYS_C0011489
SQL> alter index SYS_C0011489 rebuild;
Index altered.
--检查行链接
SQL> delete from chained_rows;
1 row deleted.
SQL> commit;
Commit complete.
SQL> analyze table ROW_CHAIN_DEMO list chained rows into chained_rows;
Table analyzed.
SQL> select * from chained_rows where table_name='CHAIN_ROW_DEMO';
no rows selected
--查看消除行链接后的执行计划状态
SQL> select /*+index(ROW_CHAIN_DEMO,x)*/* from ROW_CHAIN_DEMO where x=1;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
4668 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)http://write.blog.youkuaiyun.com/postedit/52963389
1 rows processed
--可以看出只发生了两次一致性读
行迁移
--创建测试表，保证表的字段能够大于blocksize
SQL> create table row_mig_demo(
2 x int primary key,
3 a char(1000),
4 b char(1000),
5 c char(1000),
6 d char(1000)
7 ) tablespace tbs1;
Table created.
--插入数据，只插入一个字段的值
SQL> insert into row_mig_demo(x) values(1);
1 row created.
SQL> commit;
Commit complete.
--分析表，查看是否有行迁移情况(此时必然没有)
SQL> analyze table row_mig_demo list chained rows into chained_rows;
Table analyzed.
SQL> select * from chained_rows where table_name='ROW_MIG_DEMO';
no rows selected
--查看测试表所在的数据块
SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# from row_mig_demo;
FILE# BLOCK#
---------- ----------
7 277
--dump数据块查看
SQL> alter system dump datafile 7 block 277;
System altered.
--trace文件内容
data_block_dump,data header at 0x7fa1ea9e1864
===============
tsiz: 0xf98
hsiz: 0x14
pbl: 0x7fa1ea9e1864
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0xf8c
avsp=0xf7b
tosp=0xf7b
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0xf8c
block_row_dump:
tab 0, row 0, @0xf8c
tl: 6 fb: --H-FL-- lb: 0x1 cc: 1 --FL:说明此时数据行的头和尾都在block内，cc:1,即只有一个字段的数据
col 0: [ 2] c1 02
end_of_block_dump
--通过bbed查看block数据层内容
BBED> p kdbr
sb2 kdbr[0] @118 3980
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @4080 0x2c
BBED> x /rnccccccc
rowdata[0] @4080
----------
flag@4080: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@4081: 0x01
cols@4082: 1
col 0[2] @4083: 1
--此时的执行计划状态
SQL> select * from ROW_MIG_DEMO where x=1;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
639 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--update测试表，是表的行数据大于blocksize
SQL> update row_mig_demo set a='a',b='b',c='c',d='d' where x=1;
1 row updated.
SQL> commit;
Commit complete.
SQL> delete from chained_rows;
0 rows deleted.
SQL> commit;
Commit complete.
SQL> analyze table row_mig_demo list chained rows into chained_rows;
Table analyzed.
SQL> select * from chained_rows where table_name='ROW_MIG_DEMO';
OWNER_NAME TABLE_NAME
------------------------------ ------------------------------
CLUSTER_NAME PARTITION_NAME
------------------------------ ------------------------------
SUBPARTITION_NAME HEAD_ROWID ANALYZE_T
------------------------------ ------------------ ---------
SYS ROW_MIG_DEMO
N/A AAAVqrAAHAAAAEVAAA 29-OCT-16
--可以看到已经产生了行迁移
--dump数据块查看
SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# from row_mig_demo;
FILE# BLOCK#
---------- ----------
7 277 --表所在数据块地址并未改变
SQL> alter system dump datafile 7 block 277;
System altered.
--trace文件内容
data_block_dump,data header at 0x7f28ca793264
===============
tsiz: 0xf98
hsiz: 0x14
pbl: 0x7f28ca793264
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
nrid: 0x01c00105.0
col 0: [ 2] c1 02
col 1: [1000]
--bbed查看
BBED> p kdbr
sb2 kdbr[0] @118 2965
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @3065 0x28
BBED> x /rnccccccc
rowdata[0] @3065
----------
flag@3065: 0x28 (KDRHFF, KDRHFH)
lock@3066: 0x02
cols@3067: 2
nrid@3068:0x01c00116.0
col 0[2] @3074: 1
col 1[1000] @3077: a
--消除行迁移
SQL> create table row_mig_demp1 as select * from row_mig_demo;
Table created.
三. 优化
行迁移的优化：
a. CTAS(create table as select)将发生行迁移的数据进行重新整理
--将行迁移的数据汇总到临时表中
SQL> create table row_mig_demo_temp asselect * from row_mig_demo where rowid in (select head_rowid from chained_rowswhere table_name='ROW_MIG_DEMO');
--删除表中发生行迁移的数据
SQL> delete from row_mig_demo where rowidin (select head_rowid from chained_rows where table_name='ROW_MIG_DEMO');
--将临时表中发生行迁移的数据插回到原表
SQL> insert into row_mig_demo select *from row_mig_demo_temp;
--删除临时表
drop table row_mig_demo_temp purge;
另外，对于行链接的优化已经在实验中说明
扫一扫
395
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
