DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Row Chaining and Row Migration

  1. #1
    Join Date
    Sep 2000
    Posts
    362

    Unhappy

    Hi,
    I am a little confused about row chaining and row migration.

    I know that row chaining occurs when pieces of a single row are stored in more than one block and row migration occurs when the row itself is moved to another block.

    What I am looking for is an example when these can happen and what can be done to prevent both row chaining and row migration.

    Will setting of high pctfree prevent both row chaining and row migration or i have to set a larger block size to prevent row chaining. Why will larger block size prevent row chaining and not row migration and having a larger pctfree prevent row migration and not row chaining.

    If somebody can help me to remove these cobwebs from my mind it will be great.

    Thanks
    Anurag

  2. #2
    Join Date
    Nov 2000
    Posts
    344
    A large pctfree will prevent the case where update statements cause a row to grow and then chain to another block because all of the other rows in the block are taking up the rest of the room in that block, so the growing row has to grow into another block.


    It will NOT prevent the case where row migration happnes due to the size of 1 row being greater than the size of one block. (like when you have LONG columns). In this case the row won't fit in a single block when it is first inserted so the row is spread out over multiple blocks.

    In short, don't kill yourself over chained/migrated rows when the size of the rows themselves are bigger than your block size. In this case, if it is practical, you can rebuild your DB with a bigger block size but that is all you can do.

    -John

  3. #3
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    Chaining happens when a row is INSERTED and it is too big for the database block.

    Migration happens when a row is UPDATED and the Oracle moves the row to a new block because the old block is not large enough for the longer updated row.

    Fix row chaining with a larger block size.

    Fix row migration by deleting and reinserting the offending data.

    Prevent row migration with a higher PCTFREE setting.

    Hope that helps.
    David Knight
    OCP DBA 8i, 9i, 10g

  4. #4
    Join Date
    Sep 2000
    Posts
    362
    What I am understanding is that a larger block size will prevent only in case where the row itself is larger than the block size.

    But that can happen in case of updates and inserts both.

    I am still confused.... Can only inserts cause row chaining and not updates. what if the update you are doing makes the row size larger than the block size. Then what will happen because according to the explanations the row should chain and not migrate.

    Does the pctfree parameter affect only row migration and not row chaining.

    Anurag

  5. #5
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    If your rows are consistently larger than your block size, it is time to either resize the block, or redesign the table structure.

    It doesn't matter that the cause is insert or update.

    PCTFREE is the amount of space left empty in a block in order to accomodate updates.

    David Knight
    OCP DBA 8i, 9i, 10g

  6. #6
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658

    solution

    Hi, 7th June 2001 20:44 hrs chennai

    Row chaining
    ---------------
    The row chaining occurs due to all of the data for a row in a table cannot fit within one block.hence the row will be stored in chain of blocks.Say for eg Long columns.

    Row Migration
    ----------------
    Row migration occurs when the free available is less for the existing records to be fit within the same block to hold the existing records modification( say an update).

    In this case Oracle will Shift the entire row and place it into another block . This is called row migration. Instead of chaining a row across blocks, the entire row is migrated completely to another block.

    Excessive row chaining or Row Migartion hits on the performance of the DB.

    Since there is more than one reference to the location for a chained or migrated row, Oracle must perform more work to scan a row and, therefore, performance can degrade.

    How to avoid that
    ---------------------

    In the first case the the block size should be bigger whereas in the second case the High pctfree should be set.

    There is a detailed works to find the row migration and chaining in oracle and how to avoid and solve that to size the DB.If interested in knowing the same i will Ellobrate on that tommorow.

    cheers

    padmam


    Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it

  7. #7
    Join Date
    Sep 2000
    Posts
    362
    Thanks Padmam,
    I sure am interested in knowing more detailed work on row chaining and migration.

    will be waiting for ur response.


    Anurag

  8. #8
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658

    Solution

    Hi Anuragmin, 8th June 2001 12:1o hrs chennai

    here goes the Diagnostic solution for the problem on this link

    http://www.dbasupport.com/forums/sho...threadid=11772

    The rolling head is really lovely.We should really thank Sambavan for all this.

    cheers

    Padmam

    [Edited by padmam on 06-08-2001 at 12:23 PM]
    Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width