行迁移
成 因：当发出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/1483190analyze 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(*)---------------
0SET 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
《基于案例学习sql优化》著梁剑斌
如有错误，欢迎指正
