Re: ** CPU impact of I/O change
Date: Tue, 15 Apr 2008 14:49:52 -0600
Message-ID: <480514F0.5040304@optimaldba.com>
I had learned this in one of my first DBA courses...circa 1996. So I
double checked the documentation. From the Oracle 10g Concepts doc
"Row Chaining and Migrating
In two circumstances, the data for a row in a table may be too large to fit into a single data block. In the first case, the row is too large to fit into one data block when it is first inserted. In this case, Oracle stores the data for the row in a chain of data blocks (one or more) reserved for that segment. Row chaining most often occurs with large rows, such as rows that contain a column of datatype |LONG| or |LONG| |RAW|. Row chaining in these cases is unavoidable.
However, in the second case, a row that originally fit into one data block is updated so that the overall row length increases, and the block's free space is already completely filled. In this case, Oracle migrates the data for the entire row to a new data block, assuming the entire row can fit in a new block. Oracle preserves the original row piece of a migrated row to point to the new block containing the migrated row. The rowid of a migrated row does not change.
When a row is chained or migrated, I/O performance associated with this row decreases because Oracle must scan more than one data block to retrieve the information for the row."
Regards,
Daniel Fink
-- Daniel Fink Oracle Performance, Diagnosis and Training OptimalDBA http://www.optimaldba.com Oracle Blog http://optimaldba.blogspot.com Baumgartel, Paul wrote:Received on Tue Apr 15 2008 - 15:49:52 CDT
> Interesting...I always believed that row chaining occurred in the
> second case, when an update meant that the entire row would no longer
> fit. So are you saying that row chaining occurs on insert, and
> migration on update?
>
>
> *Paul Baumgartel*
> *CREDIT SUISSE*
> Information Technology
> Prime Services Databases Americas
> One Madison Avenue
> New York, NY 10010
> USA
> Phone 212.538.1143
> paul.baumgartel_at_credit-suisse.com
> www.credit-suisse.com
>
>
>
> ------------------------------------------------------------------------
> *From:* oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] *On Behalf Of *Daniel Fink
> *Sent:* Tuesday, April 15, 2008 8:10 AM
> *To:* ajoshi977_at_yahoo.com
> *Cc:* oracle-l_at_freelists.org
> *Subject:* Re: ** CPU impact of I/O change
>
> The first question is "Why?"
>
> Are you making these changes to address performance issues? If so,
> what is the impact of these chained rows? If you can't quantify the
> performance impact, you can't quantify the improvement.
>
> 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,
> Daniel Fink
>
> --
> Daniel Fink
>
> Oracle Performance, Diagnosis and Training
>
> OptimalDBA http://www.optimaldba.com
> Oracle Blog http://optimaldba.blogspot.com
>
> A Joshi wrote:
>> Hi,
>> I plan to make changes to database like increase SGA, save rows of
>> some tables : then do truncate of table and insert the rows back or
>> move table to reduce HWM and to take care of chained rows. All this
>> will help reduce the I/O done. There are some full table scans and
>> some index accesses. My question is : What impact will this change in
>> I/O have on CPU usage. Improvement, no impact or adverse? Anything
>> else to look at or watch out for? Thanks
>>
>>
>> ------------------------------------------------------------------------
>>
>> No virus found in this incoming message.
>> Checked by AVG.
>> Version: 7.5.519 / Virus Database: 269.22.13/1377 - Release Date: 4/14/2008 9:26 AM
>>
>
> ==============================================================================
> Please access the attached hyperlink for an important electronic communications disclaimer:
>
> http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
> ==============================================================================
>
> ------------------------------------------------------------------------
>
> No virus found in this incoming message.
> Checked by AVG.
> Version: 7.5.519 / Virus Database: 269.22.13/1377 - Release Date: 4/14/2008 9:26 AM
>
-- http://www.freelists.org/webpage/oracle-l