Oracle-L: Re: Row Migration

From: Anand Kumar N <anand_at_fps.itwsignode.co.in>
Date: Fri, 27 Dec 2002 00:08:48 -0800
Message-ID: <F001.00522DE0.20021227000848@fatcity.com>

yes, row migration will degrade the performance..

Original Message ----- From: Larry Elkins To: Multiple recipients of list ORACLE-L 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
  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: Anand Kumar N
INET: anand_at_fps.itwsignode.co.in

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------