데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

Introduction
------------

This article discusses the difference between row chaining and migration.
Guidelines are given on how to detect and resolve from this situation.

Concepts
--------

There are two circumstances when this can occur, the data for a row in 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 it is first
inserted. In this case, Oracle stores the data for the row in a chain of data
blocks (one or more) reserved for that segment. Row chaining most often occurs
with large rows, such as rows that contain a column of datatype LONG, LONG RAW,
LOB, etc. Row chaining in these cases is unavoidable.

Migration
----------
Occurs when a row that originally fitted into one data block is updated so
that the overall row length increases, and the block's free space is already
completely filled. In this case, Oracle migrates the data for the entire row
to a new data block, assuming the entire row can fit in a new block. Oracle
preserves the original row piece of a migrated row to point to the new block
containing the migrated row: the rowid of a migrated row does not change.

When a row is chained or migrated, performance associated with this row
decreases because Oracle must scan more than one data block to retrieve the
information for that row.

o INSERT and UPDATE statements that cause migration and chaining perform poorly,
   because they perform additional processing.

o SELECTs that use an index to select migrated or chained rows must perform
   additional I/Os.

Detection
---------

Migrated and chained rows in a table or cluster can be identified by using the
ANALYZE command with the LIST CHAINED ROWS option. This command collects
information about each migrated or chained row and places this information into
a specified output table. To create the table that holds the chained rows,
execute script UTLCHAIN.SQL.

SQL> ANALYZE TABLE scott.emp LIST CHAINED ROWS;
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.

SQL> SELECT name, value FROM v$sysstat WHERE name = 'table fetch continued row';

NAME                                                                 VALUE
---------------------------------------------------------------- ---------
table fetch continued row                                              308

Although migration and chaining are two different things, internally they are
represented by Oracle as one. When detecting migration and chaining of rows
you should analyze carrefully what you are dealing with.

Resolving
---------

o In most cases chaining is unavoidable, especially when this involves tables
with large columns such as LONGS, LOBs, etc. When you have a lot of chained
rows in different tables and the average row length of these tables is not
that large, then you might consider rebuilding the database with a larger
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 than 2K. Then this
        means that you will have a lot of chained rows because you block size is
        too small. Rebuilding the database with a larger block size can give you
        a significant performance benefit.

o Migration is caused by PCTFREE being set too low, there is not enough room in
the block for updates. To avoid migration, all tables that are updated should
have their PCTFREE set so that there is enough space within the block for updates.
You need to increase PCTFREE to avoid migrated rows. If you leave more free
space available in the block for updates, then the row will have more room to
grow.

SQL Script to eliminate row migration :

-- Get the name of the table with migrated rows:
ACCEPT table_name PROMPT 'Enter the name of the table with migrated rows: '

-- Clean up from last execution
set echo off
DROP TABLE migrated_rows;
DROP TABLE chained_rows;

-- Create the CHAINED_ROWS table
@.../rdbms/admin/utlchain.sql
set echo on
spool fix_mig
-- List the chained and migrated rows
ANALYZE TABLE &table_name LIST CHAINED ROWS;

-- Copy the chained/migrated rows to another table
create table migrated_rows as
    SELECT orig.*
    FROM &table_name orig, chained_rows cr
    WHERE orig.rowid = cr.head_rowid
      AND cr.table_name = upper('&table_name');

-- Delete the chained/migrated rows from the original table
DELETE FROM &table_name WHERE rowid IN (SELECT head_rowid FROM chained_rows);

-- Copy the chained/migrated rows back into the original table
INSERT INTO &table_name SELECT * FROM migrated_rows;

spool off

Note
----

When you run the script in the document to clean up the chained rows 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 when you have
long raws. Also, by the nature of the long raw datatype, chaining is
unavoidable.

최기국님이 2006-05-08 17:03에 작성한 댓글입니다.
이 댓글은 2006-05-08 17:05에 마지막으로 수정되었습니다. Edit Ｘ