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
|
- Row Migration Larry Elkins
- Re: Row Migration Anand Kumar N
- RE: Row Migration Larry Elkins
- RE: Row Migration Larry Elkins
- Re: Row Migration Mogens Nørgaard
- RE: Row Migration Nick Wagner
- RE: Row Migration John Kanagaraj
- Re: Row Migration Jonathan Lewis
- RE: Row Migration Nick Wagner
- RE: Row Migration Larry Elkins
- RE: Row Migration Larry Elkins
- RE: Row Migration Larry Elkins
- Re: Row Migration Jared Still
- RE: Row Migration Jeremy Pulcifer
- Re: Row Migration Rachel Carmichael
- RE: Row Migration Larry Elkins