x
Solved

# Row migration and chaining

Posted on 2002-06-05
Medium Priority
2,662 Views
Is there any way to differentiate between chained and migrated rows in a table ?
i.e., know how many chained rows and how many migrated rows there are, not just the total of the both.
0
• 3
• 3
• 2

LVL 9

Accepted Solution

ID: 7056345
Yes, Technically there is a way to differentiate this.  The way is knowing this is by examining the definations of each:

1) row chaining is when the row is too large to fit into one data block when it is first inserted

2) row migration is when a row that originally fit into one data block is updated so that the overall row length increases, and the block's free space is already completely filled.

So, to determine if the row is chained or migrated, the algorithm is:

1) sum up the size of the row in question
(this is easily done with sql queries)
2) if the row size is greater than the block size
then the row is chained and will be chained
3) if the rowsize is less than the block size
the issue is that the row has been migrated.
(thus simple cleanup)

this can be automated easily..

0

LVL 4

Expert Comment

ID: 7056534
I beg to differ about the definition of row chaining and migration.

ON INSERT:
When a row is inserted into a table, Oracle will look on the free list for the first available block and attempt to insert the row there. If the row will not fit in the block, Oracle will attempt to migrate the row to the next available block.  If Oracle cannot find another available block, it will break the row up into pieces and chain it across several blocks.

ON UPDATE:
When a row is updated in such a way that its physical size will no longer fit within the original block, Oracle will attempt to migrate the row to the next available block on the free list. If no available block is found, Oracle will break up the row into pieces and chain it across several blocks.

So both chaining and migration can occur either on insert or on update.  Quite frankly, there is very little difference between the two, but as dbalaski has pointed out, it may be much easier to fix migrated rows than chained rows since the possibility exists that the row might actually fit within a single datablock, whereas a chained row has a higher probability of not fitting within ANY datablock.

All this is pertinent to 8i and below only. In 9i, with automatic segment space management, it's handled quite differently.

Andrew
0

LVL 9

Expert Comment

ID: 7057118
Hi,

i disagree with asimkovsky..

There is a difference between  CHAINING and MIGRATION and how the issues can be addressed --  you really need to understand the definition of the two,  and why being able to determine which has occurred is a good thing.

With CHAINING, the row size would never be able to fit in a BLOCK (assume an empty block),  because by the rowsize is physically greater than the blocksize.  (doesn't matter if it is an insert or an update -- if you increase the rowsize > blocksize,  the row will ALWAYS BE CHAINED -- PERIOD).

With Migration, the row size is smaller than the block (assuming an empty block).  So in theory,  you should be able to correct a MIGRATION problem by a re-org of the object (ie: table)

Granted:  during an update,  a row can be both chained & migrated.   But again, if you examine the basics,  rowsize > blocksize  and thus,  this row will always be chained with this blocksize.   PERIOD.

Chaining and migration can both happen,   but in the strict sense of the word, you can do something about the migrated rows,   but you can do very little about chained rows  (with the exception of utilizing the new  9i feature of multiple block sizes in a single database)..

So -- knowing the difference is important.
Please verify this is the the following manuals:
Oracle9i Database Master Glossary
Oracle9i Concepts

0

LVL 4

Expert Comment

ID: 7057154
***
With CHAINING, the row size would never be able to fit in a BLOCK (assume an empty block),  because
by the rowsize is physically greater than the blocksize.  (doesn't matter if it is an insert or an update
-- if you increase the rowsize > blocksize,  the row will ALWAYS BE CHAINED -- PERIOD).

With Migration, the row size is smaller than the block (assuming an empty block).  So in theory,  you
should be able to correct a MIGRATION problem by a re-org of the object (ie: table)

Granted:  during an update,  a row can be both chained & migrated.   But again, if you examine the basics,
rowsize > blocksize  and thus,  this row will always be chained with this blocksize.   PERIOD.
***

I completely agree with all these points. I was only pointing out exactly how Oracle makes the decision to do either one or both.

However, as far as the performance impact, depending on the actual size of the row, and how many blocks are involved, generally row chaining will hurt performance slightly more, but only slightly.

The method you would use to repair both chained and migrated rows are the same, however, you cannot repair chained rows if the row size is larger than the block size.

So I think we both agree on these points, with the exception of the steps that occur when a row is inserted or updated that causes the row to be too large for the available space.

Andrew
0

LVL 9

Expert Comment

ID: 7059458
Hi,

asimkovsky:  my apologies, I re-read MY comments, and they seemed a little harsh --  that was not the intention
I hope I did not offend you.

0

LVL 4

Expert Comment

ID: 7059478

Don't apologize. I'm not offended at all.  I always enjoy a sharp exchange of ideas and a difference in perspective.

(In English:  I like to argue about techie stuff, even if I'm proven wrong!)  :-)

Andrew

0

Expert Comment

ID: 10475043
Sue This Script to Analyze

******************************************
spool chain.lst;

set pages 9999;

column c1 heading "Owner"   format a9;

column c2 heading "Table"   format a12;

column c3 heading "PCTFREE" format 99;

column c4 heading "PCTUSED" format 99;

column c5 heading "avg row" format 99,999;

column c6 heading "Rows"    format 999,999,999;

column c7 heading "Chains"  format 999,999,999;

column c8 heading "Pct"     format .99;

select 'Tables with chained rows and no RAW columns.' from dual;

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

;

This will produce a nice report that shows tables that you will want to reorganize:

Owner  Table     PCTFREE PCTUSED avg row      Rows    Chains  Pct

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

SAPR3  ZG_TAB         10      40      80     5,003     1,487  .30

SAPR3  ZMM            10      40     422    18,309       509  .03

SAPR3  Z_Z_TBLS       10      40      43       458        53  .12

SAPR3  USR03          10      40     101       327        46  .14

SAPR3  Z_BURL         10      40     116     1,802        25  .01

SAPR3  ZGO_CITY       10      40      56     1,133        10  .01

6 rows selected.
0

Expert Comment

ID: 10475045
Check it & Get Back to me
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.