|
yes, row migration will
degrade the performance..
----- Original Message -----
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
[EMAIL PROTECTED]
214.954.1781
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Larry
Elkins
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
|
