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
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
yes, row migration
will degrade the performance..
