RE: Row Migration

[Nick Wagner]

We've done a few tests here with chained vs. unchained rows, and the impact is anywhere from 50-200% overhead.   So if it took about 10 seconds to do a query it will now take 15 to 30 seconds.   It seamed to depend most on which rows we were returning... not hitting the chained rows as much helped speed it up.     For each row operation, Oracle must read the block that contains the data, and the last piece of information in each block contains a null/not null rowid pointer to the next row piece.  In a spanned row (one inserted that is too big for a single DB_BLOCK) the pointer (usually) points to the next physical block in the DB, and it goes pretty fast.  In a chained row (one where someone has done an update, and the new information put into the row does not fit into the rest of the block -- which sounds like your case) the pointer (usually) points to a block at the end of the physical table in the DB file that contains the rest of the information.   And it goes very slowly.   Chaining can really grow to be progressively worse, if you continually update a column who spans the two blocks, oracle will not update the first block or the last block and instead create another new block at the end of the table for those new characters.  So a read of that column now takes in 3 blocks, potentially spanned over the entire datafile.   In Oracle 9i we've seen some really strange behavior too... when doing an import, or direct load Oracle will actually chain a row inside of a block, and none of the analyze for chained row commands will pick it up.  It still causes the slow down, but you cannot fix it.     It's actually been a while since I've really been able to look at this stuff, so if anyone has any clarifications or things they want to add, please do so.    Nick     -----Original Message----- From: Larry Elkins [mailto:[EMAIL PROTECTED]] Sent: Friday, December 27, 2002 3:19 AM To: Multiple recipients of list ORACLE-L Subject: RE: Row Migration Well, yes, I would agree with that ;-)   What we are trying to determine here in this particular case is how much or what percentage of the slowdown in the process is due to the migration of rows. We aren't ready (until we do some testing) to make a blanket statement that row migration *alone* is the cause of the significant slowdown. In other words, I'm not willing to make a statement to the powers that be that simply increasing the pctfree is going to make things normal again until we have a chance to do some more detailed monitoring and testing.   Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Anand Kumar N Sent: Friday, December 27, 2002 2:09 AM To: Multiple recipients of list ORACLE-L Subject: Re: Row Migration yes, row migration will degrade the performance..