Re: Row Migration

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 <ORACLE-L_at_fatcity.com> 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
>elkinsl_at_flash.net
>214.954.1781

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

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).