一、行迁移
1.1、行迁移概念
当一个行上的更新操作(原来的数据存在且没有减少)导致当前的数据不能在容纳在当前块，我们需要进行行迁移。一个行迁移意味着整行数据将会移动，仅仅保留的是一个转移地址。因此整行数据都被移动，原始的数据块上仅仅保留的是指向新块的一个地址信息。
成因：当行被update时，如果update更新的行大于数据块的pctfree值，就需要申请第2个块，从而形成迁移。
后果：导致应用需要访问更多的数据块，性能下降。
预防：1.将数据块的pctfree调大；
2.针对表空间扩大数据块的大小。
检查：analyze table 表名 validate structure cascade into chained_rows;
2.1、实例：
实验说明：
(以EMPLOYEES表为例，如果涉及到该表有主键，并且有别的表的外键REFERENCE关联到本表，必须要执行步骤2和步骤7，否则不必执行)；
1． 执行$ORACLE_HOME/rdbms/admin目录下的utlchain.sql脚本创建chained_rows表。
2. 禁用所有其它表上关联到此表上的所有限制(假想EMPLOYEES表有主键PK_EMPLOYEES_ID，假想test表有外键f_employees_id关联reference到employees表）。
select index_name,index_type,table_name from user_indexes where table_name='EMPLOYEES';
select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from USER_CONSTRAINTS where R_CONSTRAINT_NAME='PK_EMPLOYEES_ID';
alter table test disable constraint f_employees_id;
3． 将存在有行迁移的表（用table_name代替）中的产生行迁移的行的rowid放入到chained_rows表中。
4． 将表中的行迁移的row id放入临时表中保存。
5． 删除原来表中存在的行迁移的记录行。
6． 从临时表中取出并重新插入那些被删除了的数据到原来的表中，并删除临时表。
7． 启用所有其它表上关联到此表上的所有限制。
alter table test enable constraint f_employees_id;
此外还可以采用move和exp/imp的方式（特别注意move会导致索引失效，需要重建索引）。
1 ----创建实验表---- 2 SQL> DROP TABLE EMPLOYEES PURGE; 3 DROP TABLE EMPLOYEES PURGE 4 * 5 第 1 行出现错误: 6 ORA-00942: 表或视图不存在 7 8 9 SQL> CREATE TABLE EMPLOYEES AS SELECT * FROM HR.EMPLOYEES ; 10 11 表已创建。 12 13 SQL> desc EMPLOYEES; 14 名称 是否为空? 类型 15 ----------------------------------------- -------- ---------------------------- 16 EMPLOYEE_ID NUMBER(6) 17 FIRST_NAME VARCHAR2(20) 18 LAST_NAME NOT NULL VARCHAR2(25) 19 EMAIL NOT NULL VARCHAR2(25) 20 PHONE_NUMBER VARCHAR2(20) 21 HIRE_DATE NOT NULL DATE 22 JOB_ID NOT NULL VARCHAR2(10) 23 SALARY NUMBER(8,2) 24 COMMISSION_PCT NUMBER(2,2) 25 MANAGER_ID NUMBER(6) 26 DEPARTMENT_ID NUMBER(4) 27 28 SQL> create index idx_emp_id on employees(employee_id); 29 30 索引已创建。 31 32 ---扩大字段---- 33 SQL> alter table EMPLOYEES modify FIRST_NAME VARCHAR2(1000); 34 35 表已更改。 36 37 SQL> alter table EMPLOYEES modify LAST_NAME VARCHAR2(1000); 38 39 表已更改。 40 41 SQL> alter table EMPLOYEES modify EMAIL VARCHAR2(1000); 42 43 表已更改。 44 45 SQL> alter table EMPLOYEES modify PHONE_NUMBER VARCHAR2(1000); 46 47 表已更改。 48 49 SQL> desc employees; 50 名称 是否为空? 类型 51 ----------------------------------------- -------- ---------------------------- 52 EMPLOYEE_ID NUMBER(6) 53 FIRST_NAME VARCHAR2(1000) 54 LAST_NAME NOT NULL VARCHAR2(1000) 55 EMAIL NOT NULL VARCHAR2(1000) 56 PHONE_NUMBER VARCHAR2(1000) 57 HIRE_DATE NOT NULL DATE 58 JOB_ID NOT NULL VARCHAR2(10) 59 SALARY NUMBER(8,2) 60 COMMISSION_PCT NUMBER(2,2) 61 MANAGER_ID NUMBER(6) 62 DEPARTMENT_ID NUMBER(4) 63 64 ----更新表---- 65 SQL> UPDATE EMPLOYEES 66 2 SET FIRST_NAME = LPAD('1', 1000, '*'), LAST_NAME = LPAD('1', 1000, '*'), EMAIL = LPAD('1', 1000, '*'), 67 3 PHONE_NUMBER = LPAD('1', 1000, '*'); 68 69 已更新107行。 70 71 SQL> commit; 72 73 提交完成。 74 75 ----行迁移优化前，先看看该语句逻辑读情况(执行计划及代价都一样，没必要展现了，就展现statistics即可）---- 76 SQL> set autotrace traceonly stat 77 SQL> set linesize 1000 78 SQL> select /*+index(EMPLOYEES,idx_emp_id)*/ * from EMPLOYEES where employee_id>0; 79 80 已选择107行。 81 82 83 统计信息 84 ---------------------------------------------------------- 85 152 recursive calls 86 0 db block gets 87 310 consistent gets 88 0 physical reads 89 0 redo size 90 437664 bytes sent via SQL*Net to client 91 492 bytes received via SQL*Net from client 92 9 SQL*Net roundtrips to/from client 93 0 sorts (memory) 94 0 sorts (disk) 95 107 rows processed 96 97 SQL> select /*+index(EMPLOYEES,idx_emp_id)*/ * from EMPLOYEES where employee_id>0; 98 99 已选择107行。100 101 102 统计信息103 ----------------------------------------------------------104 0 recursive calls105 0 db block gets106 219 consistent gets107 0 physical reads108 0 redo size109 437664 bytes sent via SQL*Net to client110 492 bytes received via SQL*Net from client111 9 SQL*Net roundtrips to/from client112 0 sorts (memory)113 0 sorts (disk)114 107 rows processed115 116 SQL> set autotrace off 117 118 ----- 发现存在行迁移的方法119 --首先建chaind_rows相关表，这是必需的步骤120 SQL> drop table chained_rows purge;121 drop table chained_rows purge122 *123 第 1 行出现错误:124 ORA-00942: 表或视图不存在125 126 127 SQL> @?/rdbms/admin/utlchain.sql128 129 表已创建。130 ----以下命令针对EMPLOYEES表和EMPLOYEES_BK做分析，将产生行迁移的记录插入到chained_rows表中131 132 SQL> analyze table EMPLOYEES list chained rows into chained_rows;133 134 表已分析。135 136 SQL> select count(*) from chained_rows where table_name='EMPLOYEES';137 138 COUNT(*)139 ----------140 105141 ---以下方法可以去除行迁移142 SQL> drop table EMPLOYEES_TMP;143 drop table EMPLOYEES_TMP144 *145 第 1 行出现错误:146 ORA-00942: 表或视图不存在147 148 149 SQL> create table EMPLOYEES_TMP as select * from EMPLOYEES where rowid in (select head_rowid from chained_rows);150 151 表已创建。152 153 SQL> Delete from EMPLOYEES where rowid in (select head_rowid from chained_rows);154 155 已删除105行。156 157 SQL> Insert into EMPLOYEES select * from EMPLOYEES_TMP;158 159 已创建105行。160 161 SQL> delete from chained_rows ;162 163 已删除105行。164 165 SQL> commit;166 167 提交完成。168 169 SQL> analyze table EMPLOYEES list chained rows into chained_rows;170 171 表已分析。172 173 SQL> select count(*) from chained_rows where table_name='EMPLOYEES';174 175 COUNT(*)176 ----------177 0178 179 --这时的取值一定为0，用这种方法做行迁移消除，肯定是没问题的！180 181 ---行迁移优化后，先看看该语句逻辑读情况(执行计划及代价都一样，没必要展现了，就展现statistics即可）182 SET AUTOTRACE traceonly statistics183 SQL> set linesize 1000184 SQL> select /*+index(EMPLOYEES,idx_emp_id)*/ * from EMPLOYEES where employee_id>0;185 186 已选择107行。187 188 189 统计信息190 ----------------------------------------------------------191 0 recursive calls192 0 db block gets193 116 consistent gets194 0 physical reads195 0 redo size196 437034 bytes sent via SQL*Net to client197 492 bytes received via SQL*Net from client198 9 SQL*Net roundtrips to/from client199 0 sorts (memory)200 0 sorts (disk)201 107 rows processed 二、行链接
2.1、行链接概念
当一行数据太大而不能在一个单数据块容纳时，行链接由此产生。举例来说，当你使用了4kb的Oracle数据块大小，而你需要插入一行数据是8k，Oracle则需要使用3个数据块分成片来存储。因此，引起行链接的情形通常是，表上行记录的大小超出了数据库Oracle块的大小。
产生原因：当一行数据大于一个数据块，ORACLE会同时分配两个数据块，并在第一个块上登记第二个块的地址，从而形成行链接。
预防方法：针对表空间扩大数据块大小。
检查：analyze table 表名 validate structure cascade into chained_rows;
1 ----建表---- 2 SQL> DROP TABLE EMPLOYEES PURGE; 3 4 表已删除。 5 6 SQL> CREATE TABLE EMPLOYEES AS SELECT * FROM HR.EMPLOYEES ; 7 8 表已创建。 9 10 SQL> set linesize 80; 11 SQL> desc EMPLOYEES; 12 名称 是否为空? 类型 13 ----------------------------------------- -------- ---------------------------- 14 EMPLOYEE_ID NUMBER(6) 15 FIRST_NAME VARCHAR2(20) 16 LAST_NAME NOT NULL VARCHAR2(25) 17 EMAIL NOT NULL VARCHAR2(25) 18 PHONE_NUMBER VARCHAR2(20) 19 HIRE_DATE NOT NULL DATE 20 JOB_ID NOT NULL VARCHAR2(10) 21 SALARY NUMBER(8,2) 22 COMMISSION_PCT NUMBER(2,2) 23 MANAGER_ID NUMBER(6) 24 DEPARTMENT_ID NUMBER(4) 25 26 SQL> create index idx_emp_id on employees(employee_id); 27 28 索引已创建。 29 ----扩大字段---- 30 SQL> alter table EMPLOYEES modify FIRST_NAME VARCHAR2(2000); 31 32 表已更改。 33 34 SQL> alter table EMPLOYEES modify LAST_NAME VARCHAR2(2000); 35 36 表已更改。 37 38 SQL> alter table EMPLOYEES modify EMAIL VARCHAR2(2000); 39 40 表已更改。 41 42 SQL> alter table EMPLOYEES modify PHONE_NUMBER VARCHAR2(2000); 43 44 表已更改。 45 ----更新表---- 46 UPDATE EMPLOYEES 47 SET FIRST_NAME = LPAD('1', 2000, '*'), LAST_NAME = LPAD('1', 2000, '*'), EMAIL = LPAD('1', 2000, '*'), 48 PHONE_NUMBER = LPAD('1', 2000, '*'); 49 COMMIT; 50 51 已更新107行。 52 53 SQL> 54 提交完成。 55 56 -----行链接移优化前，先看看该语句逻辑读情况 57 SET AUTOTRACE traceonly 58 SQL> set linesize 1000 59 SQL> select /*+index(EMPLOYEES,idx_emp_id)*/ * from EMPLOYEES where employee_id>0; 60 统计信息 61 ---------------------------------------------------------- 62 153 recursive calls 63 1 db block gets 64 415 consistent gets 65 0 physical reads 66 176 redo size 67 868529 bytes sent via SQL*Net to client 68 492 bytes received via SQL*Net from client 69 9 SQL*Net roundtrips to/from client 70 0 sorts (memory) 71 0 sorts (disk) 72 107 rows processed 73 SQL> select /*+index(EMPLOYEES,idx_emp_id)*/ * from EMPLOYEES where employee_id>0; 74 75 已选择107行。 76 77 78 统计信息 79 ---------------------------------------------------------- 80 7 recursive calls 81 0 db block gets 82 397 consistent gets 83 0 physical reads 84 0 redo size 85 868529 bytes sent via SQL*Net to client 86 492 bytes received via SQL*Net from client 87 9 SQL*Net roundtrips to/from client 88 0 sorts (memory) 89 0 sorts (disk) 90 107 rows processed 91 92 SQL> set autotrace off 93 --------- 发现存在行链接的方法 94 --首先建chaind_rows相关表，这是必需的步骤 95 SQL> drop table chained_rows purge; 96 97 表已删除。 98 99 SQL> @?/rdbms/admin/utlchain.sql100 101 表已创建。102 103 ----以下命令针对EMPLOYEES表和EMPLOYEES_BK做分析，将产生行迁移的记录插入到chained_rows表中104 105 SQL> analyze table EMPLOYEES list chained rows into chained_rows;106 107 表已分析。108 109 SQL> select count(*) from chained_rows where table_name='EMPLOYEES';110 111 COUNT(*)112 ----------113 107114 115 ---用消除行迁移的方法根本无法消除行链接!!!116 117 SQL> drop table EMPLOYEES_TMP;118 119 表已删除。120 121 SQL> create table EMPLOYEES_TMP as select * from EMPLOYEES where rowid in (select head_rowid from chained_rows);122 123 表已创建。124 125 SQL> Delete from EMPLOYEES where rowid in (select head_rowid from chained_rows);126 127 已删除107行。128 129 SQL> Insert into EMPLOYEES select * from EMPLOYEES_TMP;130 131 已创建107行。132 133 SQL> delete from chained_rows ;134 135 已删除107行。136 137 SQL> commit;138 139 提交完成。140 --发现用消除行迁移的方法根本无法消除行链接!141 SQL> analyze table EMPLOYEES list chained rows into chained_rows;142 143 表已分析。144 145 SQL> select count(*) from chained_rows where table_name='EMPLOYEES';146 147 COUNT(*)148 ----------149 107150 151 SQL> SET AUTOTRACE traceonly stat152 SQL> select /*+index(EMPLOYEES,idx_emp_id)*/ * from EMPLOYEES where employee_id>0;153 154 已选择107行。155 156 157 统计信息158 ----------------------------------------------------------159 0 recursive calls160 0 db block gets161 223 consistent gets162 0 physical reads163 0 redo size164 867923 bytes sent via SQL*Net to client165 492 bytes received via SQL*Net from client166 9 SQL*Net roundtrips to/from client167 0 sorts (memory)168 0 sorts (disk)169 107 rows processed170 171 ---启动大小为32K的块新建表空间(WINDOWS下只能使用2K,4K，8K和16K)172 --行链接只有通过加大BLOCK块的方式才可以避免，如下：173 create tablespace TBS_JACK_16k174 blocksize 16k175 datafile '/u01/app/oracle/oradata/orcl/TBS_JACK_32K_01.dbf' size 100m176 autoextend on177 extent management local178 6 segment space management auto;179 create tablespace TBS_JACK_16k180 *181 第 1 行出现错误:182 ORA-29339: 表空间块大小 16384 与配置的块大小不匹配183 ------------------184 185 ----解决问题后再次创建表空间----186 SQL> /187 188 表空间已创建。189 190 SQL> DROP TABLE EMPLOYEES_BK PURGE;191 DROP TABLE EMPLOYEES_BK PURGE192 *193 第 1 行出现错误:194 ORA-00942: 表或视图不存在195 SQL> CREATE TABLE EMPLOYEES_BK TABLESPACE TBS_JACK_16K AS SELECT * FROM EMPLOYEES;196 197 表已创建。198 199 SQL> delete from chained_rows ;200 201 已删除107行。202 SQL> analyze table EMPLOYEES_BK list chained rows into chained_rows;203 204 表已分析。205 206 SQL> select count(*) from chained_rows where table_name='EMPLOYEES_BK';207 208 COUNT(*)209 ----------210 0