当一条记录被更新时,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)。