关于Oracle数据库中行迁移和行链接的问题

65 阅读 0 评论 43 点赞

我是靠谱客的博主孝顺未来，最近开发中收集的这篇文章主要介绍关于Oracle数据库中行迁移和行链接的问题，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

行迁移/行链接
Row Migration (行迁移) & Row Chaining (行链接)会影响Oracle数据库性能，通过合理的诊断行迁移/行链接，我们可以较大幅度上提高Oracle数据库的性能。

行链接产生在第一次插入数据的时候如果一个block不能存放一行记录的情况下。这种情况下，Oracle将使用链接一个或者多个在这个段中保留的block存储这一行记录，
行链接比较容易发生在比较大的行上，例如行上有LONG、LONG RAW、LOB等数据类型的字段，这种时候行链接是不可避免的会产生的
如果一个表的结构设计得太宽，也可能导致产生行链接，不可消除，可以将表垂直分割避免其问题！
行链接主要是由于数据库的db_block_size不够大，对于一些大的字段没法在一个block中存储下，从而产生了行链接。对于行链接我们除了增大db_block_size之外没有别的任何办法去避免，
也没有太多可以调整的地方。

当一行记录初始插入的时候事可以存储在一个block中的，由于更新操作导致行长增加了，而block的自由空间已经完全满了，这个时候就产生了行迁移。在这种情况下，
Oracle将会迁移整行数据到一个新的block中（假设一个block中可以存储下整行数据），Oracle会保留被迁移行的原始指针指向新的存放行数据的block，这就意味着被迁移行的ROW ID是不会改变的。
行迁移则主要是由于更新表的时候，由于表的pctfree参数设置太小，导致block中没有足够的空间去容纳更新后的记录，从而产生了行迁移。对于行迁移来说就非常有调整的必要了，因为这个是可以调整和控制清除的。

当发生了行迁移或者行链接，对这行数据操作的性能就会降低，因为Oracle必须要扫描更多的block来获得这行的信息。

行迁移/行链接的检测方法：
建立一个实验表
SCOTT SQL> create table t1 (x varchar2(20));
SCOTT SQL>
begin
for i in 1..1000 loop
insert into t1 values ('A');
end loop;
commit;
end;
/
Table created.
PL/SQL procedure successfully completed.

SCOTT SQL> analyze table t1 compute statistics;

Table analyzed.

SCOTT SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS,NUM_ROWS,AVG_ROW_LEN,CHAIN_CNT from user_tables where table_name='T1';

TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS AVG_ROW_LEN CHAIN_CNT
------------------------------ ---------- ------------ ---------- ----------- ----------
T1 5 3 1000 5 0 --没有产生行迁移

SCOTT SQL> update t1 set x='AAAAAAAAAAAAAAAAAAAA'; --更新表中数据

1000 rows updated.

SCOTT SQL> commit;

Commit complete.

SCOTT SQL> analyze table t1 compute statistics;

Table analyzed.

SCOTT SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS,NUM_ROWS,AVG_ROW_LEN,CHAIN_CNT from user_tables where table_name='T1';

TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS AVG_ROW_LEN CHAIN_CNT
------------------------------ ---------- ------------ ---------- ----------- ----------
T1 13 3 1000 29 891 --产生迁移

我们也可以利用Oracle数据库自身提供的脚本utlchain.sql进行检测，（在$ORACLE_HOME/rdbms/admin目录下）生成chained_rows表

SCOTT SQL> drop table t1 purge;

Table dropped.

SCOTT SQL> create table t1 (x varchar2(20));

Table created.

SCOTT SQL> begin
2    for i in 1..1000 loop
3      insert into t1 values ('A');
4     end loop;
5 commit;
6 end;
7   /

PL/SQL procedure successfully completed.

SCOTT SQL> @?/rdbms/admin/utlchain.sql

Table created.

SCOTT SQL> ANALYZE TABLE t1 LIST CHAINED ROWS INTO chained_rows;

Table analyzed.

SCOTT SQL> select table_name,count(*) from chained_rows group by table_name;

no rows selected

SCOTT SQL> update t1 set x='AAAAAAAAAAAAAAAAAAAA';

1000 rows updated.

SCOTT SQL> ANALYZE TABLE t1 LIST CHAINED ROWS INTO chained_rows;

Table analyzed.

SCOTT SQL> select table_name,count(*) from chained_rows group by table_name;

TABLE_NAME COUNT(*)
------------------------------ ----------
T1 891 --产生迁移

行迁移的清除方法

方法一：
1．执行$ORACLE_HOME/rdbms/admin目录下的utlchain.sql脚本创建chained_rows表
SCOTT SQL> @?/rdbms/admin/utlchain.sql

2．将存在有行迁移的表中的产生行迁移的行的rowid放入到chained_rows表中
SCOTT SQL> ANALYZE TABLE t1 LIST CHAINED ROWS INTO chained_rows;
Table analyzed.

3，将表中的行迁移的rowid放入临时表中保存
SCOTT SQL> create table t1_tmp as select * from t1 where rowid in (select head_rowid from chained_rows where table_name='T1');

Table created.

4，删除原来表中存在的行迁移的记录行
SCOTT SQL> delete t1 where rowid in (select head_rowid from chained_rows where table_name='T1');

891 rows deleted.

5，从临时表中取出并重新插入那些被删除了的数据到原来的表中，并删除临时表
SCOTT SQL> insert into t1 select * from t1_tmp;

891 rows created.

SCOTT SQL> commit;

Commit complete.
对于这种方法，优点是执行起来过程比较简单，容易实现。但是这种方法的缺陷是没有考虑到表关联的情况，在大多数数据库中很多表都是和别的表之间有表关联的，
有外键的限制，这样就造成在步骤4中根本无法delete掉存在有行迁移的记录行，所以这种方法能够适用的表的范围是有限的，只能适用于表上无任何外键关联的表。
由于这种方法在插入和删除数据的时候都没有disable掉索引，这样导致主要消耗时间是在删除和插入时维持索引树的均衡上了，这个对于如果记录数不多的情况，时间上还比较短，
但是如果对于记录数很多的表这个所消耗的时间就不是能够接受的了。显然，这种方法在处理大数据量的表的时候显然是不可取的。
上述方法也可以在步骤4 delete之前禁用所有其它表上关联到此表上的所有限制，但是因为使用这种方法需要重建索引

方法二：使用EXP/IMP工具清除行迁移的方法

1，使用EXP导出存在有行迁移的表
[oracle@report ~]$ exp userid=scott/tiger file=./t1.dmp log=./t1.log buffer=10000 feedback=5000 compress=n tables=t1

2，然后DROP原来的表
SCOTT SQL> drop table t1 purge;

3，IMP开始导出的表
[oracle@report ~]$ imp userid=scott/tiger file=./t1.dmp log=./t1.log buffer=10000 feedback=5000 tables=t1

方法三：使用MOVE命令来清除行迁移的方法

1，查看要清除行迁移的表所在的表空间
SCOTT SQL> select table_name,tablespace_name from user_tables where table_name='T1';

2，查看要清除行迁移的表上的具体索引
SCOTT SQL> select index_name,table_name from user_indexes where table_name='T1';

3，Move要清除RM的表到指定的表空间中去
SCOTT SQL> alter table t1 move tablespace users;

4，重建表上的所有索引
SCOTT SQL> alter index index_name rebuild;