RE: Row Migration

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

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Jonathan
> Lewis
> Sent: Friday, December 27, 2002 2:59 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Row Migration
>
>
>
> When you do your testing, don't forget to keep an
> eye on the change in dependent logical I/O and latching.
>
> Fetching a migrated row will require an extra buffer
> visit to find the row data. This MAY turn into an
> extra disk read but at the least it IS another
> buffer visit, which means another hit on the
> cache-buffers-chains latch, and may mean further
> work done getting another buffered block to the
> correct read-consistent state.
>
> I think you'll have to model your test very carefully -
> it wouldn't be too hard to produce two different models
> with totally contradictory results - one based on the
> migration going to a relatively nearby block, the other
> based on the update and migration taking place in
> a way that ensures maximum scatter of the migrated
> row piece.
>
> The former may hide I/O problems, the latter may exaggerate
> the I/O problems and hide the latch issues; and in either
> case you may fail to emulate the read-consistency issue
> properly.
>
>
> 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 )
>

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