Row Chaining

ROW CHAINING

The PCTFREE parameter is used to specify the amount of free space on a data block to reserve for future row expansion. If PCTFREE is set improperly, SQL update statements can cause a huge amount of row fragmentation and chaining.

Improper settings for PCTUSED (e.g., set too small) can cause huge degradations in the performance of SQL insert statements. If a data block is not largely empty, excessive I/O will happen during SQL inserts because the reused Oracle data blocks will become full quickly. Taken to the extreme, improper settings for PCTUSED can create a situation where the free space on the data block is smaller than the average row length for the table. In these cases, Oracle will try five times to fetch a block from the freelist chain. After five attempts, Oracle will raise the high-water mark for the table and grab five fresh data blocks for the insert.

With a true "chained row", part of the DATA of a row is on one block and part of it is on another block.  So, instead of just having a forwarding address on one block -- and the data on another (thats a migrated row), we have data on two or more blocks....

In the case of Row Chaining, Oracle may hinder performance issues only when the requested data is not present in the current block and Oracle has to fetch the requested data from the chained block.

select * from chained_rows  gives information on the chained rows. Even DBA_Tables gives information on chained_cnt.

Any table with a long/long raw will have chained rows.When a table has more than 255 columns, rows that have data after the 255th column are likely to
be chained within the same block. This is called intra-block chaining.With intra-block chaining, users receive all the data in the same block. If the row fits in the block, users do not see an effect in I/O performance, because no extra I/O operation is required to retrieve the rest of the row.


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.

When we FULL SCAN a table, we actually *ignore* the forwarding addresses (the head row piece we call it for a row).  We know that as we continue the full scan, we'll eventually get to that row so we can ignore the forwarding address and just process the row when we get there.  Hence, in a full scan migrated rows don't cause us to really do any extra work -- they are meaningless.  Oh sure, the forwarding address is consuming a couple of bytes on the block -- it is overhead -- but frankly, it is meaningless.

When we INDEX READ into a table -- then a migrated row will cause additional IO's.  That is because the index will tell us "goto file X, block Y, slot Z to find this row".  But when we get there we find a message that says "well, really goto file A, block B, slot C to find this row".  We have to do another IO (logical or physical) to find the row.

HOW TO IDENTIFY ROW CHAINING AND ROW MIGRATION

we can detect the Migrated or chained rows in a TABLE or CLUSTER by using the following methods.

Using ANALYZE,REPORT.TXT and V$VIEWS.

Analyze

Before doing this Analyze create the table that can hold chained rows.Execute UTLCHAIN.SQL script found in the ($ORACLE_HOME)/rdbms/admin directory, this can be run to create the CHAINED_ROWS table or else create a similar table with same column and datatypes and sizes as the CHAINED_ROWS table.

SQL>ANALYZE TABLE SCHEMA_NAME.TABLE_NAME LIST CHAINED ROWS;

SQL>SELECT owner_name,table_name,head_rowid from chained_rows where TABLE_NAME = 'YOUR_TABLE_NAME';

REPORT.TXT and V$VIEWS

The Migrated or chained rows can be detected by checking the "table fetch continued row" statistic in V$SYSSTAT or in REPORT.TXT.

Note:REPORT.TXT is a file created during UTLBSTAT and UTLESTAT analyze.

HOW TO ELIMINATE MIGRATED AND CHAINED ROWS

It is VERY important to understand the distinction between migrated and chained rows. Row chaining is UNAVOIDABLE but row migration can be MANAGED and resolved through reorganization.

Note:Chaining if its going to be in all Tables then it becomes design issue and properly the DBlock size is done.

Else to avoid row chaining for very few occasions if you increase the DBlock size the effects of it as follows.

For Eg:

Space in the Buffer cache will be wasted if you are doing random access to small rows and have a large block size .For Eg an Block size of 8 KB and a 50 byte row size the wasted space will be 7950 bytes in the buffer cache in an random access.

The steps involved are:

1)Analyze the table ....list chained rows
2)Copy the rows to another table
3)Delete the rows from the original table
4)insert the rows from step 2 back to original table.

Step 4 eliminates the Migrated rows because Migration only occurs during an UPDATE.

QUERY TO DETECT TABLES WITH MIGRATED OR CHAINED ROWS


select
   owner              c1,
   table_name         c2,
   pct_free           c3,
   pct_used           c4,
   avg_row_len        c5,
   num_rows           c6,
   chain_cnt          c7,
   chain_cnt/num_rows c8
from dba_tables
where
owner not in ('SYS','SYSTEM')
and
table_name not in
 (select table_name from dba_tab_columns
   where
 data_type in ('RAW','LONG RAW')
 )
and
chain_cnt > 0
order by chain_cnt desc
;