Date: Tue, 15 Apr 2008 17:56:20 -0600
To: daniel.fink@optimaldba.com
From: Wolfgang Breitling <breitliw@centrexcc.com>
Subject: Re: ** CPU impact of I/O change
Cc: ajoshi977@yahoo.com,oracle-l@freelists.org
In-Reply-To: <48049B2C.4070507@optimaldba.com>
References: <369294.45225.qm@web58014.mail.re3.yahoo.com>
 <48049B2C.4070507@optimaldba.com>
Mime-Version: 1.0
Content-Type: multipart/alternative; boundary="=====================_47656093==.ALT"
Message-Id: <20080415235640.71CA1840218@turing.freelists.org>
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: breitliw@centrexcc.com
--=====================_47656093==.ALT
Content-Type: text/plain; charset="us-ascii"; format=flowed

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 
--=====================_47656093==.ALT
Content-Type: text/html; charset="us-ascii"

<html>
<body>
I beg to differ slightly.<br><br>
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.<br><br>
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.<br><br>
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.<br><br>
At 06:10 AM 4/15/2008, Daniel Fink wrote:<br><br>
<blockquote type=cite class=cite cite="">The second question is
&quot;Will reorganizing tables fix the problem?&quot;. 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.<br><br>
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.<br>
</blockquote>
<x-sigsep><p></x-sigsep>
Regards<br><br>
Wolfgang Breitling<br>
Centrex Consulting Corporation<br>
<a href="http://www.centrexcc.com/" eudora="autourl">www.centrexcc.com</a>
</body>
</html>

--=====================_47656093==.ALT--


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