| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Row Migration
Thanks for those comments, but that's a little down the road for what I'm
looking at right now -- trying to determine the overhead associated with
updates and the update causing a row to migrate. We don't intend to let the
chaining actually make it into the DM. But it's good to see someone put some
numbers on it, and something I would be interested in repeating at some time
in the future should migration/chaining occur in the target table.
Regards,
Larry G. Elkins
elkinsl_at_flash.net
214.954.1781
-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Nick Wagner
Sent: Friday, December 27, 2002 11:39 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Row Migration
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:elkinsl_at_flash.net]
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
elkinsl_at_flash.net
214.954.1781
-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]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..
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Larry Elkins
INET: elkinsl_at_flash.net
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services ---------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Fri Dec 27 2002 - 18:33:41 CST
 |
 |