Re: ** CPU impact of I/O change
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: daniel.fink@xxxxxxxxxxxxxx
- Date: Tue, 15 Apr 2008 17:56:20 -0600
I beg to differ slightly.
a) migrated rows are fixed by a truncate and reload. What Dan
probably meant is that the reload does not prevent the re-occurrence
of row migration unless the root cause is addressed, perhaps by as
little as increasing pctfree.
b) just to prevent the misconception - not that Dan has said anything
to that effect - that chained rows are only created at insert. An
update also can cause a row to chain as well as migrate. Just imagine
a row with 3 varchar2(4000) fields which at insert time are populated
with a single space each. If a later update expands each field to its
4000 byte maximum the resulting row does no longer fit into a single
block and needs to be chained. I don't know if the row at that point
is also migrated. Should be easy to find out.
As for changing the HWM, if you are on 10g+ with ASSM-LMTs then you
can use shrink rather than a reorg to reset the HWM.
At 06:10 AM 4/15/2008, Daniel Fink wrote:
The second question is "Will reorganizing tables fix the problem?".
The answer to that is almost certainly not. Chained rows occur
because the row is too large to fit into a block. Migrated rows
occur when the update to a row needs more space than the available
free space in the block. In the first case, either make the row
smaller or the block (actually the available free space in the
block) larger, two things that a truncate and reload won't help. In
the second case, you have to change the application logic or the
free space setting (pctfree) for the table. Migrated rows are a
symptom of updates that cannot fit into the available free space in
the block, something a truncate and reload won't help either.
Changing the HWM only impacts full table scans. If the HWM has been
set too high because of a one time event (huge delete) and it has a
quantifiable impact, then a reorganization of the table is
appropriate. If the HWM is set too high because large insert/delete
operations are normal, the change will be temporary.
Regards
Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
Other related posts:
