Row Migration and Row Chaining

Basic definitions for the impatient:

Row Chaining: Distribution of a single table row across multiple data blocks.

Row Migration: Relocation of an entire table row to a new place, without updating the indexes.

Row movement: Relocation of an entire table row to a new place and updating the indexes.

Please note: We are going to discuss only about Row Migration and Chaining and will discuss Row movement in other articles.

What is Row Migration and Row chaining?

Row Migration

·         Row migration refers to rows that were moved to another block due to an update making them too large to fit into their original blocks. Oracle will leave a forwarding pointer in the original block so indexes will still be able to find the row.

·         The rowid of the migrated row does not change. Thus, ROWID that is stored in the index still refers to the old location of the row. An additional block, the new location of the row, must be read to fetch the required data.

Row Chaining:

A chained row is a row that is too large to fit into a single database data block.

For example, if you use a 4KB blocksize for your database, and you need to insert a row if 8KB into it, Oracle will use 3 blocks and store the row in pieces.

Conditions that cause row chaining are:

•          Tables whose row size exceeds the blocksize.
•          Tables with long and long raw columns are prone to having chained rows.
•          Tables with more than 225 columns will have chained rows as Oracle break wide tables up into pieces.

Row chaining in such cases is unavoidable.

Few points about Row Migration and Row chaining.

• Oracle does not discriminate between chained and migrated rows, even though they have different causes.
•  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.
• SELECTs that use an index to select migrated and chained rows must perform additional I/Os.

How do you detect Row Chaining and Row Migration?

Oracle has provided three ways to detect the above.

1. .       The ANALYZE command
2. .       The dynamic views
3. .       Report.txt method

The ANALYZE command

1.       Before doing this ANALYZE, create a table that can hold the chained rows.

2.       Execute UTLCHAIN.sql or UTLCHN1.sql  script found in $ORACLE_HOME/rdbms/admin/ directory. This creates the CHAINED_ROWS table.

cd $ORACLE_HOME/rdbms/admin

sqlplus scott/tiger

@utlchain.sql

3.       Else, create a similar table with same column and data types and sizes as the CHAINED_ROWS table.

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

);

4.       Analyze the table now as below:

ANALYZE TABLE <table_name> LIST CHAINED ROWS [INTO CHAINED_ROWS];

5.       View the Chained rows using the SQL statement as below:

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

Using the Dynamic Views

1.       V$sysstat tells how many times, since the system was started the rows are chained or migrated.

sqlplus system/<password>

SELECT 'Chained or Migrated Rows = '||value

  FROM v$sysstat

 WHERE name = 'table fetch continued row';

Chained or Migrated Rows = 31637

Note: You could have 1 table with 1 chained row that was fetched 31637 times or you could have 31637 tables, each with a chained row, each of which was fetched once. Any combo can work.

2.       User_Tables tells immediately after ANALYZE (will be null otherwise) how many rows in the table are chained.

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

3.       Dba_tables show how many chained (and migrated) rows each table has:

SELECT owner, table_name, chain_cnt FROM dba_tables WHERE chain_cnt>0;

Using the REPORT.txt

1.       We can create a report.txt file using the utlbstat.sql and utlestat.sql scripts.

2.       Now, check the statistic named ‘table fetch continuous row’ in the report.txt file.

Simply, How to check the number of rows chained or migrated in a 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

How to avoid row chaining and migration?

Increasing PCTFREE can help to avoid migrated rows.

ALTER TABLE …. MOVE

Enables you to relocate data 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 can not be modified using ALTER TABLE.

Procedure:

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 rebuild the table in a new segment.

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.

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

Now, Rebuild the indexes for the Table.

Moving a table changes the row ids of the rows in the table. This causes indexes on the table to be marked UNUSABLE, and DML accessing the table using these indexes will receive an ORA-01502 error.

The indexes on the table must be dropped or re-built..

Likewise, any statistics become invalid and new statistics should be collected.

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

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

A temporary solution which can avoid the migrated rows is as below:

1.       Analyze the table to get the row id.

2.       Copy those rows to a temporary table.

3.       Delete the rows from the original table.

4.       Insert into rows from step2 back to the original table.

  

partial content from : http://www.akadia.com/services/ora_chained_rows.html