Re: Row Migration

Row migration means extra IO's. If IO is taking up any significant part of your response time, then you don't want extra IO, of course. And the IO will be single-block IO (sequential reads) because a stub is left in the originating block pointing to the new block where the row migrates to - and that requires a single-block IO.

Your test with pctfree 10, then collect stats, etc., then repeat the test with pctfree 50 sounds fine to me. Good luck.

Mogens

Anand Kumar N wrote:

> yes, row migration will degrade the performance..
>
>
> ----- Original Message -----
> From: Larry Elkins <mailto:elkinsl_at_flash.net>
> To: Multiple recipients of list ORACLE-L
> <mailto:ORACLE-L_at_fatcity.com>
> Sent: Friday, December 27, 2002 5:38 AM
> Subject: Row Migration
>
> Listers,
>
> 8.1.7.4 64 Bit Solaris
>
> Does row migration utilize DB File Sequential Reads on the table?
> Off the
> top of my head I would expect so, but I've never tested something
> like that
> before.
>
> Trying to figure out if row migration is the cause of the slowdown
> in a
> package (well, it's probably slowing it down, just trying to gauge the
> impact). PctFree is 10, and new feeds contain lots of elements
> that had been
> empty before. As a result, a very large number of rows are being
> updated
> with the new info being applied, effectively doubling the row
> length. Would
> certainly expect row migration to occur. When running, execution
> time has
> quadrupled, and we see significant waits on DB File Sequential
> Reads, with
> the file/block values and dba_extents indicating the table, not an
> index.
> The working idea at this point is that all those DB File
> Sequential Read
> waits on the table are possibly related to rows being migrated. Anyone
> tested for this?
>
> We will be building a test case on Friday. One with PctFree 10 and the
> columns being updated having nulls. Will gather the waits, before
> and after
> sesstat's, analyze list chained rows, both before and after, total
> blocks,
> rows per block, etc. Then rebuild the test having a PCTFREE of 50
> and do the
> same thing. Some wildcards -- with the blocks less tightly packed,
> we will
> have to visit nearly double the number of blocks (maybe offset by
> migration), contention, and various other things to take into
> account. But
> the main thing we are focusing in on is if we continue to see the
> db file
> sequential read waits on the table. I guess the fact that we are
> seeing
> waits is indicative of some I/O contention, but trying to
> determine if, and
> how much, of that I/O is due to row migration, in which case a larger
> PCTFREE could provide some more immediate relief. No FK/PK stuff,
> unique
> index is there, but it should resolve uniqueness using the index,
> not the
> table. Maybe have left some things out. This came up a few days
> ago, but
> just really started thinking about it and digging into it. And the end
> result is we don't want migrated rows, just looking to see if the row
> migration is the primary cause of the performance downturn.
>
> Regards,
>
> Larry G. Elkins
> elkinsl_at_flash.net <mailto:elkinsl_at_flash.net>
> 214.954.1781
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Larry Elkins
> INET: elkinsl_at_flash.net <mailto: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 <mailto: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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?=
  INET: mln_at_miracleas.dk

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