Row Chaining and Row Migration

Row Chaining and Row Migration
行链接和行迁移

Introduction
------------
介绍

This article discusses the difference between row chaining andmigration.
Guidelines are given on how to detect and resolve from thissituation.
本文主要讨论行链接和行迁移直接的不同。指导怎么发现和解决行链接和行迁移。

Concepts
--------
概念

There are two circumstances when this can occur, the data for a rowin a table
may be too large to fit into a single data block. 

This can be caused by either
row chaining or row migration.

Chaining
---------
行链接

Occurs when the row is too large to fit into one data block when itis first
inserted. In this case, Oracle stores the data for the row in achain of data
blocks (one or more) reserved for that segment. Row chaining mostoften occurs
with large rows, such as rows that contain a column of datatypeLONG, LONG RAW,
LOB, etc. Row chaining in these cases is unavoidable.
行链接产生在第一次插入数据时，如果一个block不能存放一行记录的情况下。在这种情况下Oracle将使用链  接一个或者多个在这个段中保留的block存储这一行记录。行链接容易出现在大行上，比如包含LONG, LONG RAW,
LOB等字段类型的行，行链接是不可能避免的。

Migration
----------
行迁移

Occurs when a row that originally fitted into one data block isupdated so
that the overall row length increases, and the block's free spaceis already
completely filled.  In this case, Oracle migratesthe data for the entire row
to a new data block, assuming the entire row can fit in a newblock.  Oracle
preserves the original row piece of a migrated row to point to thenew block
containing the migrated row: the rowid of a migrated row does notchange.
原来存放到一个block的行被更新，导致行的长度增加，而block的预留空间已使用完，这种情况下，oracle将整行数据迁移到一个新的block，oracle会保留被迁移的行的原始指针指向新的存放行数据的block，被迁移行的rowid不改变。

When a row is chained or migrated, performance associated with thisrow
decreases because Oracle must scan more than one data block toretrieve the
information for that row.
当一行发生链接或迁移，和这行有关的性能会下降，因为oracle如果取这行数据，必须扫描不止一个block。

o INSERT and UPDATE statements that cause migration and chainingperform poorly,
  because they perform additionalprocessing.
  产生行迁移和行链接的insert和update执行效率低。

o SELECTs that use an index to select migrated or chained rows mustperform
  additional I/Os.
  使用index的select查询迁移或者链接的行必须执行额外的I/O。
 

Detection
---------
检查

Migrated and chained rows in a table or cluster can be identifiedby using the
ANALYZE command with the LIST CHAINED ROWS option. This commandcollects
information about each migrated or chained row and places thisinformation into
a specified output table.  To create the tablethat holds the chained rows,
execute script UTLCHAIN.SQL.
使用带LIST CHAINEDROWS的ANALYZE命令，可以发现一个表或簇中迁移和链接的行。这个命令搜集关于迁移和链接的行的信息，并将信息记录到指定的表。执行UTLCHAIN.SQL创建记录迁移的行的表。

  SQL> ANALYZE TABLE scott.emp LIST CHAINEDROWS;
  SQL> SELECT * FROM chained_rows;
 
You can also detect migrated and chained rows by checking the
'table fetch continued row' statistic in the v$sysstat view.
通过v$sysstat的'table fetch continued row'也可以检查迁移和链接的行。

  SQL> SELECT name, value FROM v$sysstat WHEREname = 'table fetch continued row';
 
  NAME                                                                                                                                VALUE
  -------------------------------------------------------------------------
  table fetch continuedrow                                                                                          308

Although migration and chaining are two different things,internally they are
represented by Oracle as one.  When detectingmigration and chaining of rows
you should analyze carrefully what you are dealing with.
尽管行迁移和行链接两个是不同的，但是oracle内部把它作为一个来对待。当发现行迁移和行链接，你需要仔细分析你处理的是行迁移还是行链接。

Resolving
---------
解决

o In most cases chaining is unavoidable, especially when thisinvolves tables
  with large columns such as LONGS, LOBs,etc.  When you have a lot of chained
  rows in different tables and the average rowlength of these tables is not
  that large, then you might consider rebuildingthe database with a larger
  blocksize.
  多数情况下，行链接是无法避免的，尤其当牵扯的表包含LONGS,LOBs等列。如果在不同的表中有大连的链接行，并且这些的表的行平均长度不是太大，那可能需要考虑使用较大的blocksize重建blocksize。
 
  e.g.: You have a database with a 2K block size.Different tables have multiple
              large varchar columns with an average row length of more than2K.  Then this
              means that you will have a lot of chained rows because your blocksize is
              too small.  Rebuilding the database with a largerblock size can give you
              a significant performance benefit.
             
o Migration is caused by PCTFREE being set too low, there is notenough room in
  the block for updates.  Toavoid migration, all tables that are updated should
  have their PCTFREE set so that there is enoughspace within the block for updates.
  You need to increase PCTFREE to avoid migratedrows.  If you leave more free
  space available in the block for updates, thenthe row will have more room to
  grow.
  PCTFREE设置的太低，会导致行迁移，因为block没有为update留下充足的空间。为了避免行迁移，被update的表应该有可以满足update的PCTFREE设置。
 
 
  SQL Script to eliminate row migration :
  消除行迁移的sql脚本：
 
      -- Get thename of the table with migrated rows:
      ACCEPTtable_name PROMPT 'Enter the name of the table with migrated rows:'
 
      -- Clean upfrom last execution
      set echooff
      DROP TABLEmigrated_rows;
      DROP TABLEchained_rows;
 
      -- Createthe CHAINED_ROWS table
      @$ORACLE_HOME/rdbms/admin/utlchain.sql
      set echoon
      spoolfix_mig
      -- List thechained and migrated rows
      ANALYZETABLE &table_name LIST CHAINED ROWS;
     
      -- Copy thechained/migrated rows to another table
      create tablemigrated_rows as
          SELECT orig.*
          FROM &table_name orig, chained_rows cr
          WHERE orig.rowid = cr.head_rowid
              AND cr.table_name = upper('&table_name');
         
      -- Deletethe chained/migrated rows from the original table
      DELETE FROM&table_name WHERE rowid IN (SELECT head_rowid FROMchained_rows);
 
      -- Copy thechained/migrated rows back into the original table
      INSERT INTO&table_name SELECT * FROM migrated_rows;
 
      spooloff
 

  Alternative methods to eliminate the migratedrows are:
  - alter table move command
  - table level export / import

  Please note that these methods will rebuild theentire table rather then
  re-inserting the migrated rows.

Note
----

When you run the script in the document to clean up the chainedrows if the
table contains long raw columns, the script will fail with ORA-997:Illegal use
of LONG datatype. This error is normal as you cannot do a CTAS whenyou have
long raws. Also, by the nature of the long raw datatype, chainingis
unavoidable.


References
----------

Oracle? Database Administrator's Guide 10g Release 2 (10.2)


Search Words
------------

CHAINING MIGRATION ROW PCTFREE