Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2658
  • Last Modified:

Row migration and chaining

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
avokado
Asked:
avokado
  • 3
  • 3
  • 2
1 Solution
 
dbalaskiCommented:
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
 
asimkovskyCommented:
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
 
dbalaskiCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
asimkovskyCommented:
***
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
 
dbalaskiCommented:
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
 
asimkovskyCommented:
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
 
javeddbaCommented:
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
 
javeddbaCommented:
Check it & Get Back to me
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now