一、行迁移
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会导致索引失效，需要重建索引）。
----创建实验表----
SQL> DROP TABLE EMPLOYEES PURGE;
DROP TABLE EMPLOYEES PURGE
*
第 1 行出现错误:
ORA-00942: 表或视图不存在 SQL> CREATE TABLE EMPLOYEES AS SELECT * FROM HR.EMPLOYEES ; 表已创建。 SQL> desc EMPLOYEES;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4) SQL> create index idx_emp_id on employees(employee_id); 索引已创建。 ---扩大字段----
SQL> alter table EMPLOYEES modify FIRST_NAME VARCHAR2(1000); 表已更改。 SQL> alter table EMPLOYEES modify LAST_NAME VARCHAR2(1000); 表已更改。 SQL> alter table EMPLOYEES modify EMAIL VARCHAR2(1000); 表已更改。 SQL> alter table EMPLOYEES modify PHONE_NUMBER VARCHAR2(1000); 表已更改。 SQL> desc employees;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NUMBER(6)
FIRST_NAME VARCHAR2(1000)
LAST_NAME NOT NULL VARCHAR2(1000)
EMAIL NOT NULL VARCHAR2(1000)
PHONE_NUMBER VARCHAR2(1000)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4) ----更新表----
SQL> UPDATE EMPLOYEES
2 SET FIRST_NAME = LPAD('', 1000, '*'), LAST_NAME = LPAD('', 1000, '*'), EMAIL = LPAD('', 1000, '*'),
3 PHONE_NUMBER = LPAD('', 1000, '*'); 已更新107行。 SQL> commit; 提交完成。 ----行迁移优化前，先看看该语句逻辑读情况(执行计划及代价都一样，没必要展现了，就展现statistics即可）----
SQL> set autotrace traceonly stat
SQL> set linesize 1000
SQL> select /*+index(EMPLOYEES,idx_emp_id)*/ * from EMPLOYEES where employee_id>0; 已选择107行。 统计信息
----------------------------------------------------------
152 recursive calls
0 db block gets
310 consistent gets
0 physical reads
0 redo size
437664 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
107 rows processed SQL> select /*+index(EMPLOYEES,idx_emp_id)*/ * from EMPLOYEES where employee_id>0; 已选择107行。 统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
219 consistent gets
0 physical reads
0 redo size
437664 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
107 rows processed SQL> set autotrace off ----- 发现存在行迁移的方法
--首先建chaind_rows相关表，这是必需的步骤
SQL> drop table chained_rows purge;
drop table chained_rows purge
*
第 1 行出现错误:
ORA-00942: 表或视图不存在 SQL> @?/rdbms/admin/utlchain.sql 表已创建。
----以下命令针对EMPLOYEES表和EMPLOYEES_BK做分析，将产生行迁移的记录插入到chained_rows表中 SQL> analyze table EMPLOYEES list chained rows into chained_rows; 表已分析。 SQL> select count(*) from chained_rows where table_name='EMPLOYEES'; COUNT(*)
----------
105
---以下方法可以去除行迁移
SQL> drop table EMPLOYEES_TMP;
drop table EMPLOYEES_TMP
*
第 1 行出现错误:
ORA-00942: 表或视图不存在 SQL> create table EMPLOYEES_TMP as select * from EMPLOYEES where rowid in (select head_rowid from chained_rows); 表已创建。 SQL> Delete from EMPLOYEES where rowid in (select head_rowid from chained_rows); 已删除105行。 SQL> Insert into EMPLOYEES select * from EMPLOYEES_TMP; 已创建105行。 SQL> delete from chained_rows ; 已删除105行。 SQL> commit; 提交完成。 SQL> analyze table EMPLOYEES list chained rows into chained_rows; 表已分析。 SQL> select count(*) from chained_rows where table_name='EMPLOYEES'; COUNT(*)
----------
0 --这时的取值一定为0，用这种方法做行迁移消除，肯定是没问题的！ ---行迁移优化后，先看看该语句逻辑读情况(执行计划及代价都一样，没必要展现了，就展现statistics即可）
SET AUTOTRACE traceonly statistics
SQL> set linesize 1000
SQL> select /*+index(EMPLOYEES,idx_emp_id)*/ * from EMPLOYEES where employee_id>0; 已选择107行。 统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
116 consistent gets
0 physical reads
0 redo size
437034 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
107 rows processed
二、行链接
2.1、行链接概念
当一行数据太大而不能在一个单数据块容纳时，行链接由此产生。举例来说，当你使用了4kb的Oracle数据块大小，而你需要插入一行数据是8k，Oracle则需要使用3个数据块分成片来存储。因此，引起行链接的情形通常是，表上行记录的大小超出了数据库Oracle块的大小。
产生原因：当一行数据大于一个数据块，ORACLE会同时分配两个数据块，并在第一个块上登记第二个块的地址，从而形成行链接。
预防方法：针对表空间扩大数据块大小。
检查：analyze table 表名 validate structure cascade into chained_rows;
----建表----
SQL> DROP TABLE EMPLOYEES PURGE; 表已删除。 SQL> CREATE TABLE EMPLOYEES AS SELECT * FROM HR.EMPLOYEES ; 表已创建。 SQL> set linesize 80;
SQL> desc EMPLOYEES;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4) SQL> create index idx_emp_id on employees(employee_id); 索引已创建。
----扩大字段----
SQL> alter table EMPLOYEES modify FIRST_NAME VARCHAR2(2000); 表已更改。 SQL> alter table EMPLOYEES modify LAST_NAME VARCHAR2(2000); 表已更改。 SQL> alter table EMPLOYEES modify EMAIL VARCHAR2(2000); 表已更改。 SQL> alter table EMPLOYEES modify PHONE_NUMBER VARCHAR2(2000); 表已更改。
----更新表----
UPDATE EMPLOYEES
SET FIRST_NAME = LPAD('', 2000, '*'), LAST_NAME = LPAD('', 2000, '*'), EMAIL = LPAD('', 2000, '*'),
PHONE_NUMBER = LPAD('', 2000, '*');
COMMIT; 已更新107行。 SQL>
提交完成。 -----行链接移优化前，先看看该语句逻辑读情况
SET AUTOTRACE traceonly
SQL> set linesize 1000
SQL> select /*+index(EMPLOYEES,idx_emp_id)*/ * from EMPLOYEES where employee_id>0;
统计信息
----------------------------------------------------------
153 recursive calls
1 db block gets
415 consistent gets
0 physical reads
176 redo size
868529 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
107 rows processed
SQL> select /*+index(EMPLOYEES,idx_emp_id)*/ * from EMPLOYEES where employee_id>0; 已选择107行。 统计信息
----------------------------------------------------------
7 recursive calls
0 db block gets
397 consistent gets
0 physical reads
0 redo size
868529 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
107 rows processed SQL> set autotrace off
--------- 发现存在行链接的方法
--首先建chaind_rows相关表，这是必需的步骤
SQL> drop table chained_rows purge; 表已删除。 SQL> @?/rdbms/admin/utlchain.sql 表已创建。 ----以下命令针对EMPLOYEES表和EMPLOYEES_BK做分析，将产生行迁移的记录插入到chained_rows表中 SQL> analyze table EMPLOYEES list chained rows into chained_rows; 表已分析。 SQL> select count(*) from chained_rows where table_name='EMPLOYEES'; COUNT(*)
----------
107 ---用消除行迁移的方法根本无法消除行链接!!! SQL> drop table EMPLOYEES_TMP; 表已删除。 SQL> create table EMPLOYEES_TMP as select * from EMPLOYEES where rowid in (select head_rowid from chained_rows); 表已创建。 SQL> Delete from EMPLOYEES where rowid in (select head_rowid from chained_rows); 已删除107行。 SQL> Insert into EMPLOYEES select * from EMPLOYEES_TMP; 已创建107行。 SQL> delete from chained_rows ; 已删除107行。 SQL> commit; 提交完成。
--发现用消除行迁移的方法根本无法消除行链接!
SQL> analyze table EMPLOYEES list chained rows into chained_rows; 表已分析。 SQL> select count(*) from chained_rows where table_name='EMPLOYEES'; COUNT(*)
----------
107 SQL> SET AUTOTRACE traceonly stat
SQL> select /*+index(EMPLOYEES,idx_emp_id)*/ * from EMPLOYEES where employee_id>0; 已选择107行。 统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
223 consistent gets
0 physical reads
0 redo size
867923 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
107 rows processed ---启动大小为32K的块新建表空间(WINDOWS下只能使用2K,4K，8K和16K)
--行链接只有通过加大BLOCK块的方式才可以避免，如下：
create tablespace TBS_JACK_16k
blocksize 16k
datafile '/u01/app/oracle/oradata/orcl/TBS_JACK_32K_01.dbf' size 100m
autoextend on
extent management local
6 segment space management auto;
create tablespace TBS_JACK_16k
*
第 1 行出现错误:
ORA-29339: 表空间块大小 16384 与配置的块大小不匹配
------------------ORA-29339报错解决办法！ ----解决问题后再次创建表空间----
SQL> / 表空间已创建。 SQL> DROP TABLE EMPLOYEES_BK PURGE;
DROP TABLE EMPLOYEES_BK PURGE
*
第 1 行出现错误:
ORA-00942: 表或视图不存在
SQL> CREATE TABLE EMPLOYEES_BK TABLESPACE TBS_JACK_16K AS SELECT * FROM EMPLOYEES; 表已创建。 SQL> delete from chained_rows ; 已删除107行。
SQL> analyze table EMPLOYEES_BK list chained rows into chained_rows; 表已分析。 SQL> select count(*) from chained_rows where table_name='EMPLOYEES_BK'; COUNT(*)
----------
0