RE: Row Migration

Larry,

Don't want to preach to the Guru, but have you checked the values for 'table fetch continued row'?

Statistic                                    Total   per Second    per Trans
--------------------------------- ---------------- ------------ ------------
table fetch by rowid                   577,820,727     40,129.2     61,248.8
table fetch continued row                  137,202          9.5         14.5

This when coming out of V$SESSTAT could give a good indication of number of fetches by migrated as well as chained rows for that session. You could also look at V$SESSION.MAX_WAIT for 'db file sequential read' events...

Let us know what you find!
John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002

What would you see if you were allowed to look back at your life at the end of your journey in this earth?

• The opinions and statements above are entirely my own and not those of my employer or clients **

> -----Original Message-----
> From: Larry Elkins [mailto:elkinsl_at_flash.net]
> Sent: Thursday, December 26, 2002 4:09 PM
> To: Multiple recipients of list ORACLE-L
> 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
> 214.954.1781
>
> --
> 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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Kanagaraj
  INET: john.kanagaraj_at_hds.com

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