The Secrets of Oracle Row Chaining and Migration（转载）

The Secrets of Oracle Row Chaining and Migration

Martin Zahn, Akadia AG, Information Technology, CH-3672 Oberdiessbach
EMail: martin dot zahn at akadia dot ch

12.09.2007: Updated for Oracle 10.2

声明:本文为转载,如果有侵犯知识版权，请通知本人，本人将即刻停止侵权行为:

转载网址：http://www.akadia.com/services/ora_chained_rows.html

Overview

     If you notice poor performance in your Oracle database Row Chaining and Migration may be one of several reasons, but we can prevent some of them by properly designing and/or diagnosing the database.

    如果发现oracle的性能很差，Row Chaining（行链接）和Row Migration（行转移）可能是其中的一个原因，通过合理的设计和诊断，我们可以规避此类问题

    Row Migration & Row Chaining are two potential problems that can be prevented. By suitably diagnosing, we can improve database performance. The main considerations are:

• What is Row Migration & Row Chaining ?什么是行合并和行链接
• How to identify Row Migration & Row Chaining ?如何识别行合并和行链接
• How to avoid Row Migration & Row Chaining ?如何避免行合并和行链接

    Migrated rows affect OLTP systems which use indexed reads to read singleton rows（影响OLTP系统使用读取单行和多行的数据（会发生扫描无用的块））. In the worst case, you can add an extra I/O to all reads which would be really bad（在最坏的情况下，可能需要额外的扫描所有的块）. Truly chained rows affect index reads and full table scans.（行链接影响索引扫描和全表扫描）

Oracle Block

      The Operating System Block size is the minimum unit of operation (read /write) by the OS and is a property of the OS file system. While creating an Oracle database we have to choose the «Data Base Block Size» as a multiple of the Operating System Block size. The minimum unit of operation (read /write) by the Oracle database would be this «Oracle block», and not the OS block. Once set, the «Data Base Block Size» cannot be changed during the life of the database (except in case of Oracle 9i). To decide on a suitable block size for the database, we take into consideration factors like the size of the database and the concurrent number of transactions expected.

   当创建Oracle数据库的时候，我们需要选择«Data Base Block Size»参数，该参数是多个操作系统块的大小，Oracle数据库读和写操作的最小单元是Oracle数据块，不是系统块。一旦设定了数据块的大小，在数据库的生命周期被，将不允许在被改变（9i可以修改），再确定合适的块大小之前，我们应该根据数据库的大小和每一刻的事务操作量来确定。

The database block has the following structure (within the whole database structure)

 Header
        Header contains the general information about the data i.e. block address, and type of segments (table, index etc). It Also contains the information about table and the actual row (address) which that holds the data.

        头部包含数据块地址，段类型（索引-表……），也包含表和行的信息。

Free Space

      Space allocated for future update/insert operations. Generally affected by the values of PCTFREE and PCTUSEDparameters.

Data

     Actual row data.

FREELIST, PCTFREE and PCTUSED

While creating / altering any table/index, Oracle used two storage parameters for space control.（当创建或者更新表和索引时，Oracle使用如下的两个存储参数控制空间）

• PCTFREE - The percentage of space reserved for future update of existing data.（为未来更新数据使用的参数）
   
• PCTUSED - The percentage of minimum space used for insertion of new row data.（为插入新数据使用的参数）
  This value determines when the block gets back into the FREELISTS structure.
   
• FREELIST - Structure where Oracle maintains a list of all free available blocks.（Oracle将索引空的可用的数据块维护在该集合中）

       Oracle will first search for a free block in the FREELIST and then the data is inserted into that block. The availability of the block in the FREELIST is decided by the PCTFREE value. Initially an empty block will be listed in the FREELIST structure, and it will continue to remain there until the free space reaches the PCTFREE value.

         在插入数据的时候，Oracle将会首先会在FREELIST中搜索空闲的数据块，然后，将数据插入到搜索出的块中，在FREELIST中的数据块是否可用是由PCTFREE参数来决定的，初始化一个空的块将被列入FREELIST结构中，在没有达到PCTFREE的数值前，他将一直在FREELIST中。

       When the free space reach the PCTFREE value the block is removed from the FREELIST, and it is re-listed in the FREELIST table when the volume of data in the block comes below the PCTUSED value.

         当剩余的空间等于PCTFREE的值的时候，该数据块将从FREELIST中移除，当该数据块中数据下降到PCTUSED之下时候，该数据块又重新的列入到FREELIST中。

       Oracle use FREELIST to increase the performance. So for every insert operation, oracle needs to search for the free blocks only from the FREELIST structure instead of searching all blocks.

       Oracle使用FREELIST来提高性能，对于每一次插入操作，Oracle查询在FREELIST中的数据块，而不是扫描整个数据块。

Row Migration

      We will migrate a row when an update to that row would cause it to not fit on the block anymore (with all of the other data that exists there currently).  A migration means that the entire row will move and we just leave behind the «forwarding address». So, the original block just has the rowid of the new block and the entire row is moved.

     当修改一行数据的时候，导入数据块将不能容下改行数据（其他的数据仍然存储在当前的地方），这时候，会发生行合并。合并意味着，整行数据将被移动-在原来的数据块中，只留下新的数据块的地址。索引，原始的数据块知识包含新数据块的rowid，整行数据已经被移动。

      Full Table Scans are not affected by migrated rows

      全表扫描不受合并行的影响。

      The forwarding addresses are ignored. We know that as we continue the full scan, we'll eventually get to that row so we can ignore the forwarding address and just process the row when we get there.  Hence, in a full scan migrated rows don't cause us to really do any extra work -- they are meaningless.

    Index Read will cause additional IO's on migrated rows

    对于合并行，的索引扫描将会造成额外的ID开销

    When we Index Read into a table, then a migrated row will cause additional IO's. That is because the index will tell us «goto file X, block Y, slot Z to find this row». But when we get there we find a message that says «well, really goto file A, block B, slot C to find this row». We have to do another IO (logical or physical) to find the row.

Row Chaining

      A row is too large to fit into a single database block. For example, if you use a 4KB blocksize for your database, and you need to insert a row of 8KB into it, Oracle will use 3 blocks and store the row in pieces. Some conditions that will cause row chaining are: Tables whose rowsize exceeds the blocksize. Tables with LONG and LONG RAW columns are prone to having chained rows. Tables with more then 255 columns will have chained rows as Oracle break wide tables up into pieces. So, instead of just having a forwarding address on one block and the data on another we have data on two or more blocks.

     一行数据太大，而不能存储到单独的一个数据库块中，例如，在数据库指定数据块的大小为4KB，而你需要插入一行数据，他的大小为8KB，那么你需要三个数据块去存储这行数据，以下情况会造成数据库行之间链接：表中的行大小超过了数据库中的块大小，含有LONG和LONG RAW（16进制）的表，更易于发生行的链接，一个表中有超过255行的列也将会发生行的链接，Oralce将把整个表分片存储。相对于行转移在一个块中包含地址，一个块中包含数据，我们将有两个或者更多的数据块包含数据。

        Chained rows affect us differently. Here, it depends on the data we need. If we had a row with two columns that was spread over two blocks, the query:

　　 根据查询的行不同，来确定 «table fetch continued row».参数是否增加　　

SELECT column1 FROM table

      where column1 is in Block 1, would not cause any «table fetch continued row». It would not actually have to get column2, it would not follow the chained row all of the way out. On the other hand, if we ask for:

SELECT column2 FROM table

and column2 is in Block 2 due to row chaining, then you would in fact see a «table fetch continued row»

Example

         The following example was published by Tom Kyte, it will show row migration and chaining. We are using an 4k block size:

SELECT NAME,
       VALUE
  FROM v$parameter
 WHERE NAME = 'db_block_size';

NAME                 VALUE
--------------      ------
db_block_size         4096

Create the following table with CHAR fixed columns:

CREATE TABLE row_mig_chain_demo (
  x int PRIMARY KEY,
  a CHAR(1000),
  b CHAR(1000),
  c CHAR(1000),
  d CHAR(1000),
  e CHAR(1000)
);

       That is our table. The CHAR(1000)'s will let us easily cause rows to migrate or chain. We used 5 columns a,b,c,d,e so that the total rowsize can grow to about 5K, bigger than one block, ensuring we can truly chain a row.

INSERT INTO row_mig_chain_demo (x) VALUES (1);
INSERT INTO row_mig_chain_demo (x) VALUES (2);
INSERT INTO row_mig_chain_demo (x) VALUES (3);
COMMIT;

     We are not interested about seeing a,b,c,d,e - just fetching them. They are really wide so we'll surpress their display.

　　column a noprint
　　column b noprint
　　column c noprint
　　column d noprint
　　column e noprint

SELECT * FROM row_mig_chain_demo;

         X
----------
         1
         2
         3

Check for chained rows:

SELECT a.name, b.value
  FROM v$statname a, v$mystat b
 WHERE a.statistic# = b.statistic#
   AND lower(a.name) = 'table fetch continued row';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
table fetch continued row                                                 0

     Now that is to be expected, the rows came out in the order we put them in (Oracle full scanned this query, it processed the data as it found it). Also expected is the table fetch continued row is zero. This data is so small right now, we know that all three rows fit on a single block. No chaining.三行数据都在一个数据块中

Demonstration of the Row Migration（行移动的证明）

       Now, lets do some updates in a specific way. We want to demonstrate the row migration issue and how it affects the full scan:

UPDATE row_mig_chain_demo
   SET a = 'z1',
       b = 'z2',
       c = 'z3'
 WHERE x = 3;
COMMIT;
UPDATE row_mig_chain_demo
   SET a = 'y1',
       b = 'y2',
       c = 'y3'
 WHERE x = 2;
COMMIT;
UPDATE row_mig_chain_demo
   SET a = 'w1',
       b = 'w2',
       c = 'w3'
 WHERE x = 1;
COMMIT;

     Note the order of updates, we did last row first, first row last.（行的位置发生了颠倒）---

    全表扫描不会发生table fetch continued row参数+1

SELECT * FROM row_mig_chain_demo;

         X
----------
         3
         2
         1

SELECT a.name, b.value
  FROM v$statname a, v$mystat b
 WHERE a.statistic# = b.statistic#
   AND lower(a.name) = 'table fetch continued row';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
table fetch continued row                                                 0

      Interesting, the rows came out «backwards» now. That is because we updated row 3 first. It did not have to migrate, but it filled up block 1. We then updated row 2. It migrated to block 2 with row 3 hogging all of the space, it had to. We then updated row 1, it migrated to block 3. We migrated rows 2 and 1, leaving 3 where it started.

      首先更新第三行数据，不会发生行转移，他填满了第一个数据块，当更新第二行的时候，由于第三行数据已经填满了第一个数据块，第二行链接到第二个数据块，当更新第一行数据的时候，与第三个数据块发生了转移，我们对第二行和第一行进行了转移，第三行作为起始的数据块。

    So, when Oracle full scanned the table, it found row 3 on block 1 first, row 2 on block 2 second and row 1 on block 3 third. It ignored the head rowid piece on block 1 for rows 1 and 2 and just found the rows as it scanned the table. That is why the table fetch continued row is still zero. No chaining.

    当全表扫描的时候，首先在第一个数据块发现第三行数据，其次发现第二行和第三行数据，忽略了在第一个块中存储的关于第一行和第二行的rowid的头信息。当扫描表的时候，仅仅找出行数据。

So, lets see a migrated row affecting the «table fetch continued row»:

SELECT * FROM row_mig_chain_demo WHERE x = 3;

         X
----------
         3

SELECT a.name, b.value
  FROM v$statname a, v$mystat b
 WHERE a.statistic# = b.statistic#
   AND lower(a.name) = 'table fetch continued row';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
table fetch continued row                                                 0

     This was an index range scan / table access by rowid using the primary key.  We didn't increment the «table fetch continued row» yet since row 3 isn't migrated.

SELECT * FROM row_mig_chain_demo WHERE x = 1;

         X
----------
         1

SELECT a.name, b.value
  FROM v$statname a, v$mystat b
 WHERE a.statistic# = b.statistic#
   AND lower(a.name) = 'table fetch continued row';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
table fetch continued row                                                 1

     Row 1 is migrated, using the primary key index, we forced a «table fetch continued row».第一行发生了转移，所以数值会+1

Demonstration of the Row Chaining

UPDATE row_mig_chain_demo
   SET d = 'z4',
       e = 'z5'
 WHERE x = 3;
COMMIT;

Row 3 no longer fits on block 1. With d and e set, the rowsize is about 5k, it is truly chained.总大小超过5k，会发生行链接

SELECT x,a FROM row_mig_chain_demo WHERE x = 3;

         X
----------
         3

SELECT a.name, b.value
  FROM v$statname a, v$mystat b
 WHERE a.statistic# = b.statistic#
   AND lower(a.name) = 'table fetch continued row';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
table fetch continued row                                                 1

     We fetched column «x» and «a» from row 3 which are located on the «head» of the row, it will not cause a «table fetch continued row». No extra I/O to get it.

SELECT x,d,e FROM row_mig_chain_demo WHERE x = 3;

SELECT a.name, b.value
  FROM v$statname a, v$mystat b
 WHERE a.statistic# = b.statistic#
   AND lower(a.name) = 'table fetch continued row';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
table fetch continued row                                                 2

     Now we fetch from the «tail» of the row via the primary key index. This increments the «table fetch continued row» by one to put the row back together from its head to its tail to get that data.（首尾相连查询数据！）

Now let's see a full table scan - it is affected as well:

SELECT * FROM row_mig_chain_demo;

         X
----------
         3
         2
         1

SELECT a.name, b.value
  FROM v$statname a, v$mystat b
 WHERE a.statistic# = b.statistic#
   AND lower(a.name) = 'table fetch continued row';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
table fetch continued row                                                 3

    The «table fetch continued row» was incremented here because of Row 3, we had to assemble it to get the trailing columns.  Rows 1 and 2, even though they are migrated don't increment the «table fetch continued row» since we full scanned.

   该参数由于第三行进行了增加，因为我们必须将第三行的数据进行组合起来（包含首尾相连），当全表扫描的时候，由于即使第一行和第二行发生了行转移，也不会导致参数+1、

SELECT x,a FROM row_mig_chain_demo;

         X
----------
         3
         2
         1

SELECT a.name, b.value
  FROM v$statname a, v$mystat b
 WHERE a.statistic# = b.statistic#
   AND lower(a.name) = 'table fetch continued row';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
table fetch continued row                                                 3

    No «table fetch continued row» since we didn't have to assemble Row 3, we just needed the first two columns.

SELECT x,e FROM row_mig_chain_demo;

         X
----------
         3
         2
         1

SELECT a.name, b.value
  FROM v$statname a, v$mystat b
 WHERE a.statistic# = b.statistic#
   AND lower(a.name) = 'table fetch continued row';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
table fetch continued row                                                 4

      But by fetching for d and e, we incemented the «table fetch continued row». We most likely have only migrated rows but even if they are truly chained, the columns you are selecting are at the front of the table.

    查询数据的时候，发生行链接的数据在前面

So, how can you decide if you have migrated or truly chained?：如何确定是行转移还是行链接呢？

Count the last column in that table. That'll force to construct the entire row.

SELECT count(e) FROM row_mig_chain_demo;

  COUNT(E)
----------
         1

SELECT a.name, b.value
  FROM v$statname a, v$mystat b
 WHERE a.statistic# = b.statistic#
   AND lower(a.name) = 'table fetch continued row';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
table fetch continued row                                                 5

Analyse the table to verify the chain count of the table:

ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS;

SELECT chain_cnt
  FROM user_tables
 WHERE table_name = 'ROW_MIG_CHAIN_DEMO';

 CHAIN_CNT
----------
         3

Three rows that are chained. Apparently, 2 of them are migrated (Rows 1 and 2) and one is truly chained (Row 3).

Total Number of «table fetch continued row» since instance startup?

参数«table fetch continued row»是如何变化的？

     The V$SYSSTAT view tells you how many times, since the system (database) was started you did a «table fetch continued row» over all tables.

     通过查询V$SYSSTAT视图可以查询出从系统启动针对所以的表总共发生了多少次的table fetch continued row

sqlplus system/<password>

SELECT 'Chained or Migrated Rows = '||value
  FROM v$sysstat
 WHERE name = 'table fetch continued row';

Chained or Migrated Rows = 31637

     You could have 1 table with 1 chained row that was fetched 31'637 times. You could have 31'637 tables, each with a chained row, each of which was fetched once. You could have any combination of the above -- any combo.

    可以是一个表有一个链接行执行了31673次，或者存在31673个表，每个有一个链接行，执行了一次，可以是任何组合条件。

Also, 31'637 - maybe that's good, maybe that's bad.it is a function of：一下是判断该参数好坏的情况：

• how long has the database has been up
• how many rows is this as a percentage of total fetched rows.
  For example if 0.001% of your fetched are table fetch continued row, who cares!

Therefore, always compare the total fetched rows against the continued rows.

SELECT name,value FROM v$sysstat WHERE name like '%table%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
table scans (short tables)                                           124338
table scans (long tables)                                              1485
table scans (rowid ranges)                                                0
table scans (cache partitions)                                           10
table scans (direct read)                                                 0
table scan rows gotten                                             20164484
table scan blocks gotten                                            1658293
table fetch by rowid                                                1883112
table fetch continued row                                             31637
table lookup prefetch client count                                        0

How many Rows in a Table are chained?

     The USER_TABLES tells you immediately after an ANALYZE (will be null otherwise) how many rows in the table are chained.

当使用ANALYZE 命令进行表信息统计之后，就可以发现该表中存在多少行数据发生行链接。

ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS;

SELECT chain_cnt,
       round(chain_cnt/num_rows*100,2) pct_chained,
       avg_row_len, pct_free , pct_used
  FROM user_tables
WHERE table_name = 'ROW_MIG_CHAIN_DEMO';

 CHAIN_CNT PCT_CHAINED AVG_ROW_LEN   PCT_FREE   PCT_USED
---------- ----------- ----------- ---------- ----------
         3         100        3691         10         40

PCT_CHAINED shows 100% which means all rows are chained or migrated.

List Chained Rows

      You can look at the chained and migrated rows of a table using the ANALYZE statement with the LIST CHAINED ROWS clause. The results of this statement are stored in a specified table created explicitly to accept the information returned by the LIST CHAINED ROWS clause. These results are useful in determining whether you have enough room for updates to rows.

Creating a CHAINED_ROWS Table

       To create the table to accept data returned by an ANALYZE ... LIST CHAINED ROWS statement, execute theUTLCHAIN.SQL or UTLCHN1.SQL script in $ORACLE_HOME/rdbms/admin. These scripts are provided by the database. They create a table named CHAINED_ROWS in the schema of the user submitting the script.

create table CHAINED_ROWS (
  owner_name         varchar2(30),
  table_name         varchar2(30),
  cluster_name       varchar2(30),
  partition_name     varchar2(30),
  subpartition_name  varchar2(30),
  head_rowid         rowid,
  analyze_timestamp  date
);

    After a CHAINED_ROWS table is created, you specify it in the INTO clause of the ANALYZE statement.

ANALYZE TABLE row_mig_chain_demo LIST CHAINED ROWS;

SELECT owner_name,
       table_name,
       head_rowid
 FROM chained_rows
OWNER_NAME                     TABLE_NAME                     HEAD_ROWID
------------------------------ ------------------------------ ------------------
SCOTT                          ROW_MIG_CHAIN_DEMO             AAAPVIAAFAAAAkiAAA
SCOTT                          ROW_MIG_CHAIN_DEMO             AAAPVIAAFAAAAkiAAB

How to avoid Chained and Migrated Rows?

     Increasing PCTFREE can help to avoid migrated rows. （增加PCTFREE的值）If you leave more free space available in the block, then the row has room to grow. You can also reorganize or re-create tables and indexes that have high deletion rates. If tables frequently have rows deleted, then data blocks can have partially free space in them. If rows are inserted and later expanded, then the inserted rows might land in blocks with deleted rows but still not have enough room to expand. Reorganizing the table ensures that the main free space is totally empty blocks.

      The ALTER TABLE ... MOVE statement enables you to relocate data of a nonpartitioned table or of a partition of a partitioned table into a new segment, and optionally into a different tablespace for which you have quota. This statement also lets you modify any of the storage attributes of the table or partition, including those which cannot be modified using ALTER TABLE. You can also use the ALTER TABLE ... MOVE statement with the COMPRESS keyword to store the new segment using table compression.

1. ALTER TABLE MOVE
   
   First count the number of Rows per Block before the ALTER TABLE MOVE
   
   SELECT dbms_rowid.rowid_block_number(rowid) "Block-Nr", count(*) "Rows"
     FROM row_mig_chain_demo
   GROUP BY dbms_rowid.rowid_block_number(rowid) order by 1;
   
    Block-Nr        Rows
   ---------- ----------
         2066          3
   
   Now, de-chain the table, the ALTER TABLE MOVE rebuilds the row_mig_chain_demo table in a new segment, specifying new storage parameters:
   
   ALTER TABLE row_mig_chain_demo MOVE
      PCTFREE 20
      PCTUSED 40
      STORAGE (INITIAL 20K
               NEXT 40K
               MINEXTENTS 2
               MAXEXTENTS 20
               PCTINCREASE 0);
   
   Table altered.
   
   Again count the number of Rows per Block after the ALTER TABLE MOVE
   
   SELECT dbms_rowid.rowid_block_number(rowid) "Block-Nr", count(*) "Rows"
     FROM row_mig_chain_demo
   GROUP BY dbms_rowid.rowid_block_number(rowid) order by 1;
   
    Block-Nr        Rows
   ---------- ----------
         2322          1
         2324          1
         2325          1
    
2. Rebuild the Indexes for the Table
   
   Moving a table changes the rowids of the rows in the table. This causes indexes on the table to be markedUNUSABLE, and DML accessing the table using these indexes will receive an ORA-01502 error. The indexes on the table must be dropped or rebuilt. Likewise, any statistics for the table become invalid and new statistics should be collected after moving the table.
   移动行数据造成索引的不可用
   ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS;
   
   ERROR at line 1:
   ORA-01502: index 'SCOTT.SYS_C003228' or partition of such index is in unusable
   state
   
   This is the primary key of the table which must be rebuilt.
   
   ALTER INDEX SYS_C003228 REBUILD;
   Index altered.
   
   ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS;
   Table analyzed.
   
   SELECT chain_cnt,
          round(chain_cnt/num_rows*100,2) pct_chained,
          avg_row_len, pct_free , pct_used
     FROM user_tables
    WHERE table_name = 'ROW_MIG_CHAIN_DEMO';
   
    CHAIN_CNT PCT_CHAINED AVG_ROW_LEN   PCT_FREE   PCT_USED
   ---------- ----------- ----------- ---------- ----------
            1       33.33        3687         20         40

   If the table includes LOB column(s), this statement can be used to move the table along with LOB data and LOB index segments (associated with this table) which the user explicitly specifies. If not specified, the default is to not move the LOB data and LOB index segments.

Detect all Tables with Chained and Migrated Rows

Using the CHAINED_ROWS table, you can find out the tables with chained or migrated rows.

将存在行链接和行转移的索引表的相关数据存储到临时表中

1. Create the CHAINED_ROWS table
   
   cd $ORACLE_HOME/rdbms/admin
   sqlplus scott/tiger
   @utlchain.sql
    
2. Analyse all or only your Tables
   
   SELECT 'ANALYZE TABLE '||table_name||' LIST CHAINED ROWS INTO CHAINED_ROWS;'
     FROM user_tables
   /
   
   ANALYZE TABLE ROW_MIG_CHAIN_DEMO LIST CHAINED ROWS INTO CHAINED_ROWS;
   ANALYZE TABLE DEPT LIST CHAINED ROWS INTO CHAINED_ROWS;
   ANALYZE TABLE EMP LIST CHAINED ROWS INTO CHAINED_ROWS;
   ANALYZE TABLE BONUS LIST CHAINED ROWS INTO CHAINED_ROWS;
   ANALYZE TABLE SALGRADE LIST CHAINED ROWS INTO CHAINED_ROWS;
   ANALYZE TABLE DUMMY LIST CHAINED ROWS INTO CHAINED_ROWS;
   
   Table analyzed.
    

3. Show the RowIDs for all chained rows
   
   This will allow you to quickly see how much of a problem chaining is in each table. If chaining is prevalent in a table, then that table should be rebuild with a higher value for PCTFREE
   
   SELECT owner_name,
          table_name,
          count(head_rowid) row_count
     FROM chained_rows
   GROUP BY owner_name,table_name
   /
   
   OWNER_NAME                     TABLE_NAME                      ROW_COUNT
   ------------------------------ ------------------------------ ----------
   SCOTT                          ROW_MIG_CHAIN_DEMO                      1

Conclusion

Migrated rows affect OLTP systems which use indexed reads to read singleton rows. In the worst case, you can add an extra I/O to all reads which would be really bad. Truly chained rows affect index reads and full table scans.

• Row migration is typically caused by UPDATE operation

• Row chaining is typically caused by INSERT operation.

• SQL statements which are creating/querying these chained/migrated rows will degrade the performance due to more I/O work.由于IO降低了系统的性能

• To diagnose chained/migrated rows use ANALYZE command , query V$SYSSTAT view，

  使用ANALYZE命令来诊断表中存在合并行。在通过查询V$SYSSTAT视图中的lower(a.name) = 'table fetch continued row';来确定。

• To remove chained/migrated rows use higher PCTFREE using ALTER TABLE MOVE.

  为了避免行合并和行链接，可以采用增加PCTFREE参数的值和ALTER TABLE MOVE命令。