• Arun Kumar

Row Chaining And Row Migration

In this article we shall be looking at difference between row chaining and row migration. Read along!


Row Chaining

Normally we encounter row chaining when the size of a database row is larger than the size of the database block that is used for storing it. In this situation, the row is split across more than one database block. When you need to access this row, the system access more than one database block, which results in more I/O operations.


Let’s go ahead with a scenario


Let’s assume that default block size in 8kb in this scenario

row-migration-db-block-size

Create the table “DBA_GEN_BIG” with the following command

row-migration-create-table-1

Populate the table with the following command

row-migration-dba-objects

Analyze the table to refresh the statistics with the following command

row-migration-table-analyzed

Check for chained rows with the following command

row-migration-chain-cnt

Now create a tablespace with a different block size with the following command

row-migration-tablespace-created

Move the table DBA_GEN_BIG to newly created tablespace DBAGEN

row-migration-table-altered

Rebuild the indexes because they are unusable after the move with the following

command

row-migration-index-altered

Analyze the table to refresh the statistics with the following command

row-migration-table-analyzed

Check if row chain still exists with the following command

row-migration-chain-cnt
  • After moving a table, such as in the preceding example, you should do an index rebuild. An index contains the row IDs of the table rows, and the row IDs identify the position of the row. The position is composed of the objects, the datafile, the block number, and the slot (row) number. When we move a table, the datafile and the block number changes, so we must rebuild the indexes.

  • Row chaining leads to poor performance because accessing a row in the database requires the system to read more than once DB block, even when accessing the table by the index lookup. When different block sizes are introduced in the database, remember the pros and cons of a larger block size. The larger the block size, the more likely that contention issues occur on the database block.

Chained rows affect index reads and full table scans. Keep the following points in mind

  • Row chaining is typically caused by insert operations.

  • SQL statements that create or query chained rows degrade performance because of the additional I/O operations.

  • To diagnose chained or migrated rows, use the analyze command and query the V$SYSSTAT view.

  • To remove chained rows, set a higher PCTFREE value by using the alter table move command.

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.

  • A migrated row is just 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)

So, when you analyse the table to list chained rows, it includes migrated rows as well.


Listing 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 the UTLCHAIN.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.

row-migration-table-analyzed
row-migration-sql

Avoid Chained and Migrated Rows?

  • Increasing PCTFREE can help to avoid migrated rows. 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.

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

  • Rebuild the Indexes for the Table.

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

Recent Posts

See All

MySQL Installation on Linux

This article demonstrates MySQL installation on Oracle Enterprise Linux 6.5 version. Installation of MySQL Make sure you are able to connect internet via virtual machine. Try to ping google.com and pr

Add ISO image storage repository on XenServer

If you have used VMware ESXI, its very easy to upload ISO files to the data store and create VMs. With XenServer, you must create your own ISO repository to upload ISO files. Create ISO directory Acce

SQL Data-Type conversion functions

SQL conversion functions are single row functions designed to alter the nature of the data type of a column value, expression, or literal. TO_CHAR, TO_NUMBER, and TO_DATE are the three most widely use

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis

Feedback