行迁移检测及解决一例

行迁移(row migration)：由于在table的pctfree设置过小，在update这个table并使其行长增大的时候，就有可能因为块的剩余空间不够存储该行，oracle会把该行数据迁移到另外一个有足够空闲空间的block中，此即发生了行迁移；发生行迁移时，行rowid并不变，原先存储该行的地方增加了一个新的指针，该指针指向迁移后的block id，所以我们在访问发生了行迁移的行时，会要读取迁移前和迁移后的2个block，一个表如果有大量的行发生了row migration，那么就有必要查看该table的storage设置了，看看pctpree是否可以调整的更大一点。
链接(row chained)：和行迁移相对应的是行链接row chained，行连接常发生在行很大的情况下，如有long，raw，lob列，当insert一条新的记录时，一个block空间不够容纳一条记录，oracle会链接若干个block用来存储此记录，此即称为行链接；行链接是应为行的长度太长，要想避免，只能增加爱block size，在oracle9i之前，创建好数据库后您无法修改block size，要求你在建库的时候就要规划好，在oracle9i，提供了多块大小的技术，您可以设置2k,4k,8k,16k,32k大小的block，若想使用，您还必须设置相应的db_nk_cache_size缓冲池的大小。

下面是应用中客户维表行迁移的检测和消除步骤
检查user_tables信息

Select table_name,pct_free,num_rows,blocks,chain_cnt From user_tables Where table_name='CUST_DIMT0';
TABLE_NAME                      PCT_FREE  NUM_ROWS    BLOCKS CHAIN_CNT
------------------------------ ---------- ---------- ---------- ----------
CUST_DIMT0                            20    363338      8846      7449
 

利用oracle自带的脚本utlchain.sq，新增表chained_rows，用于存储发生行迁移记录信息

SQL> @ $ORACLE_HOME/rdbms/admin/utlchain.sql

Table created.

SQL> grant all on chained_rows to dwh;
Grant succeeded.

用analyze命令分析该表

SQL> analyze table cust_dimt0 list chained rows into sys.chained_rows;
Table analyzed

Chained_rows用于存放产生行迁移的记录信息，其中head_rowid表示产生行迁移记录的rowid；
从该表中任意找一个head_rowed,看看行迁移消除前的执行计划

SQL> set autotrace traceonly;
SQL> select * from cust_dimt0 where rowid='AAAF9UAARAAAAWGAA3';
Execution Plan
----------------------------------------------------------
  0     SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=172)
  1   0  TABLE ACCESS (BY USER ROWID) OF 'CUST_DIMT0' (Cost=1 Card=1 Bytes=172)
Statistics
----------------------------------------------------------
         0 recursive calls
         0 db block gets
         2 consistent gets
         0 physical reads
         0 redo size
      1467 bytes sent via SQL*Net to client
       275 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client

可以看到，取一条记录需要2个consistent read ;

接下来，来消除该表上的行迁移
修改表的pct_free，增大为25，日后需再观察，是否有大量的行迁移情况，如有，则说明还需要调整该参数。

SQL> alter table cust_dimt0 pctfree 25;

建立临时表，存放发生行迁移的记录

CREATE TABLE CUST_DIMT0_080331 AS SELECT * FROM CUST_DIMT0 WHERE ROWID IN (SELECT HEAD_ROWID FROM SYS.CHAINED_ROWS WHERE TABLE_NAME='CUST_DIMT0');

查看是否有外键约束应用于此表上，如果有。则先禁用此约束，操作结束后再启用

SQL> select constraint_name,constraint_type,r_owner,r_constraint_name from dba_constraints where table_name='CUST_DIMT0' and wner='DWH';
CONSTRAINT_NAME               CONSTRAINT_TYPE R_OWNER                       R_CONSTRAINT_NAME
------------------------------ --------------- ------------------------------ ------------------------------
CUST_DIMT0_PK                 P                                             
SYS_C006476                   C                                             
SYS_C006477                   C                                             
SYS_C006478                   C                                             
SYS_C006479                   C     
 

SQL>

SELECT * FROM DBA_CONSTRAINTS WHERE R_CONSTRAINT_NAME='CUST_DIMT0_PK';

未选定行
如有相关表建有外键指向该表，需要先禁用约束

alter table xxx disable constraint yyyy

删除发生行迁移的记录

SQL> DELETE CUST_DIMT0 WHERE ROWID IN (SELECT HEAD_ROWID FROM SYS.CHAINED_ROWS WHERE TABLE_NAME='CUST_DIMT0');

从临时表中重新插入记录

SQL> INSERT INTO CUST_DIMT0 SELECT * FROM CUST_DIMT0_080331;

看看执行行迁移消除后的执行计划

SQL> select * from cust_dimt0 where rowid='AAAF9UAARAAAAWGAA3';
未选定行
Execution Plan
----------------------------------------------------------
  0     SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=172)
  1   0  TABLE ACCESS (BY USER ROWID) OF 'CUST_DIMT0' (Cost=1 Card=1 Bytes=172)
 
Statistics
----------------------------------------------------------
         0 recursive calls
         0 db block gets
         1 consistent gets
         0 physical reads
         0 redo size
      1222 bytes sent via SQL*Net to client
       234 bytes received via SQL*Net from client
         1 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
0        rows processed

可以发现，执行行迁移消除后，consistent gets为1个block了。

最后，drop临时表

SQL> drop table CUST_DIMT0_080331;
 
Table dropped

注：行迁移主要是由于update的时候，块剩余空间满足不了而导致的，对于行迁移的消除，除了上面介绍的方法外，采用exp/imp ,先把表导出，然后再导入，或者采用move命令，同样，可以达到行迁移消除的目的。