Solved

Row migration and chaining

Posted on 2002-06-05
8
2,631 Views
Last Modified: 2008-02-01
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
Comment
Question by:avokado
  • 3
  • 3
  • 2
8 Comments
 
LVL 9

Accepted Solution

by:
dbalaski earned 100 total points
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..

Hope this answers your question,
dBalaski
0
 
LVL 4

Expert Comment

by:asimkovsky
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

by:dbalaski
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

dBalaski
0
 
LVL 4

Expert Comment

by:asimkovsky
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 9

Expert Comment

by:dbalaski
ID: 7059458
Hi,

Thanks avokado!  Glad I could help.


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.

dBalaski
0
 
LVL 4

Expert Comment

by:asimkovsky
ID: 7059478
dBalaski,

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

by:javeddba
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;

 

set heading off;

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

set heading on;

 

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

by:javeddba
ID: 10475045
Check it & Get Back to me
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now