Migration and Chaining

Migration and chaining

In two circumstances, accessing a row requires more than one block to be read. This is caused by:

Migration:
An UPDATE statement increases the amount of data in a row so that the row no longer fits in its data block. The server process tries to find another block with enough free space to hold the entire row. If such a block is available, the server process moves the entire row to the new block. The server process keeps the original row piece of a migrated row to point to the new block containing the actual row; the ROWID of a migrated row does not change. Indexes are not updated; they continue to point to the original row location. During an index access of the row, the original row piece is found, and then another read to retrieve the row is required.

Chaining:
The row is too large to fit into an empty data block. The server process stores the data for the row in a chain of two or more data blocks. Chaining can occur when the row is inserted or updated. Row chaining usually occurs with large rows, such as rows that contain a large object (LOB). Row chaining in these cases is unavoidable, unless a larger block size is used.

 Migration and chaining have a negative affect on performance:   
INSERT and UPDATE statements that cause migration and chaining perform poorly because they perform additional processing. Queries that use an index to select migrated or chained rows must perform additional I/Os.  

Detection migration and chaining

The ANALYZE Command

You can identify the existence of migrated and chained rows in a table or cluster by using the ANALYZE command. This command counts the number of migrated and chained rows and places this information into the CHAIN_CNT column of DBA_TABLES.

The NUM_ROWS column provides the number of rows stored in the analyzed table or cluster. Compute the ratio of chained and migrated rows to the number of rows to decide whether migrated rows need to be eliminated.

The AVG_ROW_LEN column gives an indication of whether the chain count is due to migrated rows or rows that are too long for any block.


SQL> ANALYZE TABLE oe.orders COMPUTE STATISTICS;

Table Analyzed.

SQL> SELECT num_rows, avg_row_len, chain_cnt

  2     FROM DBA_TABLES

  3     WHERE table_name='ORDERS';

  NUM_ROWS AVG_ROW_LEN  CHAIN_CNT

---------- ----------- ----------

      1171          67         83
Een ander handig commando

SELECT owner, table_name, num_rows, chain_cnt, round((chain_cnt * 100 / num_rows),2) pct, pct_free, pct_used FROM dba_tables WHERE chain_cnt > 0 AND owner NOT IN ('SYS', 'SYSTEM')

ORDER BY pct desc ;

 The Table Fetch Continued Row Statistic

You can also detect migrated or chained rows by checking the Table Fetch Continued Row statistic in V$SYSSTAT or in the Statspack report under “Instance Activity Stats for DB.”

Selecting Migrated Rows

You can identify migrated and chained rows in a table or cluster 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 the utlchain.sql script:

SQL> CREATE TABLE chained_rows (
  2  owner_name         VARCHAR2(30),
  3  table_name         VARCHAR2(30),
  4  cluster_name       VARCHAR2(30),
  5  partition_name     VARCHAR2(30),
  6  head_rowid         ROWID,
  7  analyze_timestamp  DATE );

If you create this table manually, it must have the same column names, data types, and sizes as the CHAINED_ROWS table.

SQL> ANALYZE TABLE oe.orders LIST CHAINED ROWS;

Table analyzed.

SQL> SELECT  owner_name, table_name, head_rowid

  2    FROM  chained_rows

  3    WHERE table_name = 'ORDERS';

OWNER_NAME  TABLE_NAME  HEAD_ROWID      

----------  ----------  ------------------

SALES       ORDER_HIST  AAAAluAAHAAAAA1AAA

SALES       ORDER_HIST  AAAAluAAHAAAAA1AAB

...

Eliminating Migrated Row

Any procedure that inserts the rows into a table removes migrated rows.

export import

Export and import eliminate migrated rows by inserting all the rows into a re-created table, but the table is unavailable for the duration of the operation.

Move table command

The Move table command is faster than export and import. It requires two times the space during the operation, and the table is unavailable. All indexes will be unusable and must be rebuilt after the move operation.    command --->   alter table employees move

Online Redefinition

Online redefinition allows the table to be available for DML and select for the duration of the operation, and indexes are maintained. This method requires the use of the DBMS_REDEFINITION package. This method requires the most space, because there are two copies of the table, a journal table, and multiple materialized views.

Copy migrated rows

The copy method only locks the rows being moved and uses additional space only for the chained rows. You can eliminate migrated rows by using this SQL*Plus script:

/* 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

SET ECHO ON

SPOOL fix_mig

/* List the chained & 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

When using this script, you must disable any foreign key constraints that would be violated when the rows are deleted. Additional considerations are the implications of insert and delete triggers, row-level security, and auditing.