行迁移的产生、消除和优化测试

    当一条记录被更新时，oracle会尝试在它保存的数据块中寻找足够的空闲空间（这个空间由pctfree值确定），如果没有足够的空闲空间可用，这条记录将被拆分为两个部分，第一个部分包括指向第二个部分的rowid，该部分任然保留在原来的数据块中，第二个部分包含所有的具体数据，将保存到另外一个新的数据块中，这就是所谓的行迁移。

    简单些，可以这样理解，行迁移是由于更新的行大于数据块的PCTFREE值，就需要申请后续若干个数据块，从而形成行迁移。

    下面就行迁移如何产生、消除以及对性能的影响做相关测试：


--创建测试表
SYS@ora10g> conn zlm/zlm
Connected.
SYS@ora10g> DROP TABLE EMPLOYEES PURGE;
DROP TABLE EMPLOYEES PURGE
           *
ERROR at line 1:
ORA-00942: table or view does not exist


ZLM@ora10g> CREATE TABLE EMPLOYEES AS SELECT * FROM HR.EMPLOYEES ;
Table created.


ZLM@ora10g> desc EMPLOYEES
 Name   Null?    Type
 ----------------------------------------- -------- ----------------------------
 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)


--扩大测试表字段
ZLM@ora10g> create index idx_emp_id on employees(employee_id);
Index created.


ZLM@ora10g> alter table EMPLOYEES modify FIRST_NAME VARCHAR2(1000);
Table altered.


ZLM@ora10g> alter table EMPLOYEES modify LAST_NAME  VARCHAR2(1000);
Table altered.


ZLM@ora10g> alter table EMPLOYEES modify EMAIL VARCHAR2(1000);
Table altered.


ZLM@ora10g> alter table EMPLOYEES modify PHONE_NUMBER  VARCHAR2(1000);
Table altered.


--更新测试表
ZLM@ora10g> UPDATE EMPLOYEES SET FIRST_NAME = LPAD('1', 1000, '*'), LAST_NAME = LPAD('1', 1000, '*'), EMAIL = LPAD('1', 1000, '*'), PHONE_NUMBER = LPAD('1', 1000, '*');


107 rows updated.


ZLM@ora10g> COMMIT;


Commit complete.


--查看逻辑读情况
ZLM@ora10g> SET AUTOTRACE traceonly 
ZLM@ora10g> set linesize 1000
ZLM@ora10g> select /*+index(EMPLOYEES,idx_emp_id)*/ * from EMPLOYEES  where employee_id>0;


107 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2126936620


------------------------------------------------------------------------------------------
| Id  | Operation    | Name| Rows  | Bytes | Cost (%CPU)| Time|
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    ||   107 |   218K|     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES  |   107 |   218K|     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN    | IDX_EMP_ID |   107 ||     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - access("EMPLOYEE_ID">0)


Note
-----
   - dynamic sampling used for this statement




Statistics
----------------------------------------------------------
586  recursive calls
 0  db block gets
377  consistent gets
130  physical reads
 0  redo size
     437610  bytes sent via SQL*Net to client
462  bytes received via SQL*Net from client
 9  SQL*Net roundtrips to/from client
 5  sorts (memory)
 0  sorts (disk)
107  rows processed


ZLM@ora10g> set autotrace off 


--创建chaind_rows并分析测试表
ZLM@ora10g> drop table chained_rows purge;


Table dropped.


ZLM@ora10g> @?/rdbms/admin/utlchain.sql


Table created.


ZLM@ora10g> analyze table EMPLOYEES list chained rows into chained_rows;


Table analyzed.


ZLM@ora10g> ZLM@ora10g> select count(*)  from chained_rows where table_name='EMPLOYEES';


  COUNT(*)
----------
       105


此时可以发现，测试表EMPLOYEE共有记录107条，而其中105条，都发生了行迁移，由于行迁移需要读取更多的数据块，因此对数据库性能是有影响的，那么如何来消除行迁移的影响呢？


消除行迁移的步骤：


1.创建临时表EMPLOYEES_TMP并添加记录（仅添加发生行迁移的记录）
ZLM@ora10g> drop table EMPLOYEES_TMP;
drop table EMPLOYEES_TMP
           *
ERROR at line 1:
ORA-00942: table or view does not exist




ZLM@ora10g> create table EMPLOYEES_TMP as select * from EMPLOYEES where rowid in (select head_rowid from chained_rows);


Table created.


2.删除原表EMPLOYEES中发生行迁移的记录
ZLM@ora10g> Delete from EMPLOYEES where rowid in (select head_rowid from chained_rows);


105 rows deleted.


3.将发生行迁移的记录重新插入到原表EMPLOYEES中
ZLM@ora10g> Insert into EMPLOYEES select * from EMPLOYEES_TMP;


105 rows created.


4.删除链表中的记录
ZLM@ora10g> delete from chained_rows ;


105 rows deleted.


ZLM@ora10g> commit;


Commit complete.


5.重新分析原表并查看行迁移情况
ZLM@ora10g> analyze table EMPLOYEES list chained rows into chained_rows;


Table analyzed.


ZLM@ora10g> select count(*)  from chained_rows where table_name='EMPLOYEES';


  COUNT(*)
----------
0


此时可以看到，由于重新将发生过行迁移的记录重新插入回原表，现在行迁移现象已经消失了。


--再次查看逻辑读情况
ZLM@ora10g> alter system flush shared_pool;


System altered.


ZLM@ora10g> alter system flush buffer_cache;


System altered.


注意：为了消除重复执行相同SQL语句的缓存影响，需要先执行以上两步，清空share pool和db buffer cache的内容，否则会影响最终测试结果。


ZLM@ora10g> SET AUTOTRACE traceonly
ZLM@ora10g> select /*+index(EMPLOYEES,idx_emp_id)*/ * from EMPLOYEES  where employee_id>0;


107 rows selected.




Execution Plan
----------------------------------------------------------
Plan hash value: 2126936620


------------------------------------------------------------------------------------------
| Id  | Operation    | Name| Rows  | Bytes | Cost (%CPU)| Time|
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    ||   107 |   218K|     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES  |   107 |   218K|     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN    | IDX_EMP_ID |   107 ||     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - access("EMPLOYEE_ID">0)


Note
-----
   - dynamic sampling used for this statement




Statistics
----------------------------------------------------------
586  recursive calls
 0  db block gets
276  consistent gets
132  physical reads
 0  redo size
     436980  bytes sent via SQL*Net to client
462  bytes received via SQL*Net from client
 9  SQL*Net roundtrips to/from client
 5  sorts (memory)
 0  sorts (disk)
107  rows processed


ZLM@ora10g> set autotrace off
ZLM@ora10g> 

    通过用刚才的方法消除行链接以后，在执行计划没有发生改变的情况下，逻辑读的数量从377减少为276，已经降低为原来逻辑读的73%左右。

    其实，只要消除行迁移，就能减少一定的逻辑读，最终可以带来数据库性能的提升。除了采用以上的方法来消除行迁移，还可以使用exp/imp、expdp/impdp、alter table move等操作来进行，在生产环境中，则更具可操作性（通常对大表的insert操作都会产生较多redo）。