I managed to miss the point that you were
concerned with work done and time lost on
the update rather than the subsequent
retrieval. Concerns about scatter are pretty
irrelevant at this point.
To address the issues of row migration (and
strictly ignoring row chaining), I would start
by building a very simple test case with
a couple of blocks of data, rigged so that
I could update one row without it chaining,
and update another so it chains. In both
cases I would dump the data blocks, the
undo blocks and the redo log immediately
after the update, whilst taking snapshots
of session stats, undo stats, v$transaction,
and latches.
The primary issue (in the simplest, most naked
case) is that instead of a single undo record
and a single redo record being, you get three
undo and three redo records (which means three
calls on the redo latches). The sequence seems to be:
lock row in original block
insert migrated copy of row in next freelist block
replace row data in original block with forward pointer
This is ignoring all the costs of starting a transaction, and
any index modifications, and any need to allocate a new
block and move the HWM. But under these conditions,
the cost of migration due to update is roughly three times
the cost of a simple update in terms of undo, redo and
latching.
Given this as a basis for consideration, there are two
extremes:
If you already have a wreck of a batch application
which runs a pl/sql loop to update and commit on
each row, whilst updating half a dozen indexes,
and every row gets several updates, of which an
average of one per row will cause a migration, then
you will hardly notice the difference.
If you have an array-based update, or at least
don't commit every row, and you aren't updating
loads of indexes, you will notice a significant
lift in undo and redo. You may experience extra
buffer activity because of the extra UNDO action
(more dirty blocks means faster ageing and therefore
potential re-reading of useful cached blocks).
Things still to test:
What exactly happens to the indexes - I believe
that indexes will ALWAYS point to the head
rowid, even if a particular index entry comes
into existence as a result of an update that
causes a row to migrate. But I haven't
checked that in detail for at least 3 years
What happens if you have multiple concurrent
updates running. I believe the quantity of migration
can be exacerbated. When a row migrates, it
creates more space in the block it is migrating
from, which can be used by other rows in that
block. However if a second process grows a
row, it cannot use the space left by the migration
of a row that has been updated by the first
process until the first process commits. QED
(Again - to be checked).
What happens when you update the migrated
row. If the update would cause it to migrate
from the second block, it MAY migrate back
to the first block if it now fits - but it may have
to migrate to a completely different block. So
for each scenario, how much excess undo and
redo get generated.
And, of course, there is the quantitative effect to look at,
because you need to emulate your system.
lock row in original block
insert migrated copy of row in next freelist block
replace row data in original block with forward pointer
How many of the undo and redo records will be small,
how many will be large ? It depends on the starting
and finishing size of the row.
BTW - one of the joys of block dumps: if you do this
on Oracle 9, you may find that performance is affected
quite severely. Steve Adams recently dropped me a note
pointing out that 9.2 has a clever little trick built into it
that makes it very keen to add ITL entries to the target
ITL list when a row is migrated into a block. In his example
he managed to end up with 169 ITL entries in an 8K block
even though MAXTRANS was set to 5.
The purpose of the exercise, apparently, it to avoid an internal
deadlock when using parallel DML. The upshot of the exercise
in your pre-40% case might be to waste at least 25% of every
block in the table. It is an unfortunate coincidence that if you
write the update in the worst possible way (single row commits)
you will probably suffer the least damage.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )
Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )
____England______January 21/23
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
-----Original Message-----
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 28 December 2002 01:49
>So I'm doomed? ;-)
>
>Ok, so how am I going to know which block it went to, the first step
towards
>seeing if it was relatively nearby or maximum scatter? I'm guessing I
would
>have to dump a block and look at the "placeholder" or "stub" in the
original
>location and see where it points (I'm assuming it has to)? Just
conjecture
>and the first thing I would think of since I can't think of any DD
view or
>X$ that would tell me where a row migrated from/to.
>
>And I'm not so much concerned about the extra LIO's and latching at
this
>point since I'm focused on the impact of a row migrating during an
update.
>And don't think we will allow migrated rows in the table (though one
might
>make a case for eating a few migrated rows for the sake of a
significantly
>reduced number of blocks). But over time, this sort of update *will*
>eventually happen to all the rows anyway, so we would be looking at
the
>higher number of blocks somewhere down the road. But it's all
irrelevant now
>anyway since both the staging table and it's "real" counterpart in
the DM
>were both re-orged with a pctfree of 40 (found that out this
morning). I'll
>still need to keep an eye on migrating rows, but I'm not going to
allow a
>handful of them make us go overboard on pctfree and "wasting" a lot
of
>space.
>
>Not that I'm asking you to do our work, but curious what are the
things and
>considerations *you* would consider in building such a test case?
>
>Regards,
>
>Larry G. Elkins
>[EMAIL PROTECTED]
>214.954.1781
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jonathan Lewis
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
