RE: ** CPU impact of I/O change
- From: "Baumgartel, Paul" <paul.baumgartel@xxxxxxxxxxxxxxxxx>
- To: daniel.fink@xxxxxxxxxxxxxx
- Date: Tue, 15 Apr 2008 09:04:26 -0400
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@xxxxxxxxxxxxxxxxx
www.credit-suisse.com
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Daniel Fink
Sent: Tuesday, April 15, 2008 8:10 AM
To: ajoshi977@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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
==============================================================================
Other related posts:
