row migration and row chaining in oracle

In this page, we discuss about row migration and row chaining in oracle

Table of Contents

What is 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). 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 whole row is moved.

Impact of row migration

There is no impact on the Full table scan as forwarding addresses are ignored as we know that we will read all the blocks and will eventually read the row

Index Read on a migrated row will cause additional IO’s as the index will say to go to a particular rowid which will forward you to the exact row resulting in extra I/O

What is Row chaining in Oracle?

Row chaining happens when a row is too large to fit into a single database block. For example, if you use a 4KB block size for your database, and you need to insert a row of 8KB into it, Oracle will use 3 blocks and store the row in pieces. Some conditions that will cause row chaining are Tables whose row size exceeds the block size. Tables with LONG and LONG RAW columns are prone to having chained rows. Tables with more than 255 columns will have chained rows as Oracle break wide tables up into pieces. So, instead of just having a forwarding address on one block and the data on another we have data on two or more blocks

Impact of row chaining

It impacts both the full table scan and index scan but it depends on the data required.

Impact of Both Row chaining and Row Migration

Both the row migration and row chaining impact performance as extra I/O need to be performed.
The statistics for row migration and row chaining is counted through “table fetch continued row”

SELECT a.name, b.value
FROM v$statname a, v$mystat b
WHERE a.statistic# = b.statistic#
AND lower(a.name) = ‘table fetch continued row’;

How many Rows in a Table are chained?

The USER_TABLES tells you immediately after an 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 = ‘TEST_CHAIN’;

CHAIN_CNT PCT_CHAINED AVG_ROW_LEN   PCT_FREE   PCT_USED
———- ———– ———– ———- ———-
3         100        3691         10         40

PCT_CHAINED shows 100% which means all rows are chained or migrated.

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

ANALYZE TABLE row_mig_chain_demo LIST CHAINED ROWS;
SELECT owner_name,
table_name,
head_rowid
FROM chained_rows
OWNER_NAME  TABLE_NAME HEAD_ROWID
—————————— —————————— ——————
SCOTT       TEST_CHAIN             AAAPVIAAFAAAAkiAAA
SCOTT       TEST_CHAIN             AAAPVIAAFAAAAkiAAB

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

If tables frequently have rows deleted, then data blocks can have partially free space in them.
If rows are inserted and later expanded, then the inserted rows might land in blocks with deleted rows but still not have enough room to expand.
Reorganizing the table ensures that the main free space is totally empty blocks.

Hope you like this post on Row chaining and Row migration in oracle

Related Articles

oracle create tablespace: This article on how to create tablespace in oracle, various characteristics associated with it and different create tablespace statements
ORA-01652: ORA-01652 error usually because when the tablespace does not have free space in Permanent and Temporary tablespace in oracle database. Check out how to resolve it
shrink datafile in Oracle: Check out how to shrink the datafile and reclaim space on the filesystem. How to resolve ORA-03297
Oracle database administration tutorial: This lists all the Oracle DBA-related stuff. Very helpful for administration
how to change default tablespace in oracle: Default tablespace is the tablespace where the objects are created when no tablespace name is specified by users. Find out how to check default tablespace
How to check temp tablespace in Oracle: This article is about temp tablespace in Oracle, resize the tempfile, drop the temp file, find the temp usage by Session
alter tablespace add datafile: Check out this post on How to add a datafile to tablespace in Oracle, add tempfile to the temporary tablespace, how to add datafile in ASM