In this page, we discuss about row migration and row chaining in oracle
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