Re: ** CPU impact of I/O change

I beg to differ slightly.

1. 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.
2. 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

--
http://www.freelists.org/webpage/oracle-l