Row chaining Vs Row migration

Row chaining Vs Row migration

Chained rows:

- Chained rows are records stored over multiple data blocks due to their excessive size. 

- Row piece size is grater than DB_BLOCK_SIZE then row piece will be spitted and spead accross the multiple blicks.

- Mainly caused due to INSERT statement

Migrated rows:

- Migrated rows occur when an UPDATE DML causes the rows to expand onto another data block.

- Mainly caused due to UPDATE statement

A migrated row is a special case of a chained row. 

A migrated row is a chained row, a chained row may or may not be a migrated row.

Chained rows:

Questions:

1. What is row chaining?

2. What is row migration?

3. How to identify the row chaining and row migration?

4. How to fix this row chaining and row migration?

Analyze the table to refresh the statistics:

analyze table EMP compute statistics;

This query will show how many chained (and migrated) rows each table has:

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

Check for chained rows for perticuler table:

select chain_cnt from all_tables where owner='MALLIK' and TABLE_NAME='EMP';

In order to a avoide the row chaining we need to create a tablespace with bigger block size and move the table:

SQL> select TABLESPACE_NAME,BLOCK_SIZE from dba_tablespaces;

TABLESPACE_NAME  BLOCK_SIZE

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

SYSTEM                 8192

SYSAUX                 8192

UNDOTBS1           8192

TEMP                    8192

USERS                  8192

TEST_16K_TS      16384 >>> 16K block size

alter table MALLIK.EMP move tablespace TEST_16K_TS;

After table move we should rebuild the index since index become unusable:

alter index EMP.PK_EMPID rebuild;

After moved table analyze the table to refresh the statistics and and check for row chaining which will be resolved. 

analyze table EMP compute statistics;

select chain_cnt from all_tables where owner='MALLIK' and TABLE_NAME='EMP';

Migrated rows:

This will put the rows into the CHAINED_ROWS table which is created by the utlchain.sql script 

($ORACLE_HOME/rdbms/admin).

They create a table named CHAINED_ROWS in the schema of the user submitting the script.

SELECT * FROM chained_rows;

Example:

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

);

To see which rows are chained:

ANALYZE TABLE EMP LIST CHAINED ROWS;

select owner_name, table_name, head_rowid from chained_rows;

The following query can be used to identify tables with chaining problems:

TTITLE 'Tables Experiencing Chaining'

SELECT owner, table_name,

       NVL(chain_cnt,0) "Chained Rows"

  FROM all_tables

 WHERE owner NOT IN ('SYS', 'SYSTEM')

       AND NVL(chain_cnt,0) > 0

ORDER BY owner, table_name;

Conclusion

- 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.

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

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

Regards,

Mallik