Oracle行迁移和行链接详解(原创)

行迁移
成 因：当发出update导致记录行长增加，block的剩余空间不足以存放这条记录，就会产生行迁移，发生行迁移时rowid不会改变，原来的block 中会用一个指针存放这条记录在新的block中的地址，发生行迁移会对性能产生影响，因为读这条记录会读两个BLOCK。
后果：导致应用需要访问更多的数据块，性能下降。
预防：1. 将数据块的PCTFREE调大；2. 针对表空间扩大数据块大小
检查：analyze table 表名 validate structure cascade into chained_rows;
操作:（以EMPLOYEES_TEMP表为例，如果涉及到该表有主键，并且有别的表的外键REFERENCE关联到本表，必须要执行步骤2和步骤7，否则不必执行）：
1.   执行$ORACLE_HOME/rdbms/admin目录下的utlchain.sql脚本创建chained_rows表。
2.   禁用所有其它表上关联到此表上的所有限制(假想EMPLOYEES_TEMP表有主键PK_EMPLOYEES_TEMP_ID，假想test表有外键f_EMPLOYEES_TEMP_id关联reference到EMPLOYEES_TEMP表）。
  select index_name,index_type,table_name from user_indexes where table_name='EMPLOYEES_TEMP';
  select  CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from USER_CONSTRAINTS where R_CONSTRAINT_NAME='PK_EMPLOYEES_TEMP_ID';
  alter table test disable constraint f_EMPLOYEES_TEMP_id;
3．  将存在有行迁移的表（用table_name代替）中的产生行迁移的行的rowid放入到chained_rows表中。
4．  将表中的行迁移的row id放入临时表中保存。
5．  删除原来表中存在的行迁移的记录行。
6．  从临时表中取出并重新插入那些被删除了的数据到原来的表中，并删除临时表。
7．  启用所有其它表上关联到此表上的所有限制。
     alter table test enable constraint f_EMPLOYEES_TEMP_id;
注意:此外还可以采用move和exp/imp的方式（特别注意move会导致索引失效，需要重建索引）。
行迁移实验
--- PCTFREE试验准备之建表
DROP TABLE EMPLOYEES_TEMP PURGE;
CREATE TABLE EMPLOYEES_TEMP AS SELECT * FROM HR.EMPLOYEES ;
desc EMPLOYEES_TEMP;
create index idx_emp_id on EMPLOYEES_TEMP(employee_id);
--- PCTFREE试验准备之扩大字段
alter table EMPLOYEES_TEMP modify FIRST_NAME VARCHAR2(1000);
alter table EMPLOYEES_TEMP modify LAST_NAME  VARCHAR2(1000);
alter table EMPLOYEES_TEMP modify EMAIL VARCHAR2(1000);
alter table EMPLOYEES_TEMP modify PHONE_NUMBER  VARCHAR2(1000);
--- PCTFREE试验准备之更新表
UPDATE EMPLOYEES_TEMP
  SET FIRST_NAME = LPAD('1', 1000, '*'), LAST_NAME = LPAD('1', 1000, '*'), EMAIL = LPAD('1', 1000, '*'),
  PHONE_NUMBER = LPAD('1', 1000, '*');
COMMIT;
---行迁移优化前，先看看该语句逻辑读情况(执行计划及代价都一样，没必要展现了，就展现statistics即可）
SET AUTOTRACE traceonly statistics
set linesize 1000
select /*+index(EMPLOYEES_TEMP,idx_emp_id)*/ * from EMPLOYEES_TEMP  where employee_id>0;
/
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        219  consistent gets
          0  physical reads
          0  redo size
     437663  bytes sent via SQL*Net to client
        496  bytes received via SQL*Net from client
          9  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        107  rows processed
set autotrace off
----- 发现存在行迁移的方法
--首先建chaind_rows相关表，这是必需的步骤
@?/rdbms/admin/utlchain.sql
----以下命令针对EMPLOYEES_TEMP表和EMPLOYEES_TEMP_BK做分析，将产生行迁移的记录插入到chained_rows表中
analyze table EMPLOYEES_TEMP list chained rows into chained_rows;

注意:这里只能用analyze table,不能使用dbms_stats，具体请参见
http://czmmiao.iteye.com/blog/1483190
analyze table EMPLOYEES_TEMP compute statistics;
select count(*)  from chained_rows where table_name='EMPLOYEES_TEMP';
---以下方法可以去除行迁移
drop table EMPLOYEES_TEMP_TMP;
create table EMPLOYEES_TEMP_TMP as select * from EMPLOYEES_TEMP where rowid in (select head_rowid from chained_rows);
Delete from EMPLOYEES_TEMP where rowid in (select head_rowid from chained_rows);
Insert into EMPLOYEES_TEMP select * from EMPLOYEES_TEMP_TMP;
delete from chained_rows ;
commit;
analyze table EMPLOYEES_TEMP list chained rows into chained_rows;
select count(*)  from chained_rows where table_name='EMPLOYEES_TEMP';
--这时的取值一定为0，用这种方法做行迁移消除！
---行迁移优化后，先看看该语句逻辑读情况(执行计划及代价都一样，没必要展现了，就展现statistics即可）
SET AUTOTRACE traceonly statistics
set linesize 1000
select /*+index(EMPLOYEES_TEMP,idx_emp_id)*/ * from EMPLOYEES_TEMP  where employee_id>0;
/
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       9221  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed
行迁移消除后逻辑读明显减少。

行链接

当一个BLOCK不足以存放下一条记录的时候，就会发生行连接，这个时候oracle会把这条记录分成几个部分，分别存放在几个block中，然后把这几个block chain起来。行连接同样会影响性能，因为读一条记录至少会读两个BLOCK.

行链接实验
继续扩大字段
alter table EMPLOYEES modify FIRST_NAME VARCHAR2(2000);
alter table EMPLOYEES modify LAST_NAME  VARCHAR2(2000);
alter table EMPLOYEES modify EMAIL VARCHAR2(2000);
alter table EMPLOYEES modify PHONE_NUMBER  VARCHAR2(2000);
--- PCTFREE试验准备之更新表
UPDATE EMPLOYEES
  SET FIRST_NAME = LPAD('1', 2000, '*'), LAST_NAME = LPAD('1', 2000, '*'), EMAIL = LPAD('1', 2000, '*'),
  PHONE_NUMBER = LPAD('1', 2000, '*');
COMMIT;
---行链接移优化前，先看看该语句逻辑读情况
SET AUTOTRACE traceonly statistics
set linesize 1000
select /*+index(EMPLOYEES,idx_emp_id)*/ * from EMPLOYEES  where employee_id>0
/

 

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        324  consistent gets
          0  physical reads
          0  redo size
     868528  bytes sent via SQL*Net to client
        496  bytes received via SQL*Net from client
          9  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        107  rows processed
set autotrace off
----以下命令针对EMPLOYEES表和EMPLOYEES_BK做分析，将产生行迁移的记录插入到chained_rows表中
analyze table EMPLOYEES list chained rows into chained_rows;
select count(*)  from chained_rows where table_name='EMPLOYEES';
---用消除行迁移的方法根本无法消除行链接!!!
drop table EMPLOYEES_TMP;
create table EMPLOYEES_TMP as select * from EMPLOYEES where rowid in (select head_rowid from chained_rows);
Delete from EMPLOYEES where rowid in (select head_rowid from chained_rows);
Insert into EMPLOYEES select * from EMPLOYEES_TMP;
delete from chained_rows ;
commit;
--发现用消除行迁移的方法根本无法消除行链接!
analyze table EMPLOYEES list chained rows into chained_rows;
select count(*)  from chained_rows where table_name='EMPLOYEES';
COUNT(*)

---------------
321
---------------------------------------------------------------------------------
---启动大小为16K的块新建表空间(
--行链接只有通过加大BLOCK块的方式才可以避免，如下：
create tablespace dba_16k
blocksize 16K
datafile  '/home/oracle/dba_16k.DBF' size 100M 
autoextend on 
extent management local
segment space management auto;
alter table EMPLOYEES_TEMP move tablespace dba_16k;
alter index idx_emp_id rebuild ;
delete from chained_rows ;
commit;
analyze table EMPLOYEES_BK list chained rows into chained_rows;
select count(*)  from chained_rows where table_name='EMPLOYEES_TEMP';
COUNT(*)

---------------
0

SET AUTOTRACE traceonly statistics
set linesize 1000
select /*+index(EMPLOYEES,idx_emp_id)*/ * from EMPLOYEES  where employee_id>0
/
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        116  consistent gets
          0  physical reads
          0  redo size
     866745  bytes sent via SQL*Net to client
        496  bytes received via SQL*Net from client
          9  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        107  rows processed

参考至：http://blog.csdn.net/robinson1988/article/details/4728717

              《基于案例学习sql优化》著梁剑斌

如有错误，欢迎指正

邮箱:[e-mail]