-
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
-
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
-
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
-
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
-
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
-
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
-
Thanks Padmam,
I sure am interested in knowing more detailed work on row chaining and migration.
will be waiting for ur response.
Anurag
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|