Re: Segment Fragmentation and Performance

From: John P. Higgins <jh33378_at_deere.com>
Date: Fri, 18 Sep 1998 21:56:24 -0500
Message-ID: <36031D58.367422FF@deere.com>

Craig is a true guru of Oracle !!!

If you would like to defrag, go ahead and ahead and ahead and .....

It will work each time. And soon, you'll need to do it again.

If you adjust your pctfree to allow enough space for rows to expand through updates, your next reorg will be a long time coming.

We have numerous SAP tables over 300 extents. We are reorging these when possible because we might run out (505 extent max on 7.2.2), but extents have zero performance impact.

garysadler@my-dejanews.com wrote:

I am a new Oracle DBA and would like to solicit opinions concerning the
affect
 of segment fragmentation on overall performance.  Most of what I have
read
indicates that segments should ideally reside in only one extent, and
that too
many extents will have an adverse affect on performance.  But then
I read a
whitepaper written by Craig Shallahamer of Oracle Services that
disputes that
claim.  He argues convincingly that in a production
environment, because
Oracle retrieves data in blocks, queues relatively
small requests, and services
many competing users simultaneously, the
diskhead is going to be moving all
over the place anyway.  Since only
full-table scans would be affected in the first
place, what are the odds
that the disk controller wouldn't be interrupted
numerous times before
completing that scan.  So while it can be argued that it's
good practice to
confine a segment to relatively few extents, there's no reason
why a segment
with 50 extents shouldn't perform about as well as a segment in
only one
extent.

Shallahamer's contention makes perfect sense to me, but does not
seem to be
commonly upheld.  He states that this could be because DBA's will
rebuild a
segment upon detection of fragmentation and notice an improvement
in
performance.  This practice inadvertently corrects the very real problem
of row
migration.  So while the DBA thinks the reduction in segment
fragmentation has
improved performance, it was actually the elimination of
row migration that did
the trick.  So the argument goes.

Does anyone out
there have a bone to pick with this stance?  I am curious
about where to
focus my attention, primarily on minimal extents, or on fine tuning
the
pctfree attributes, and whether I should avoid wasting disk space assuring
that a segment remains in one extent, in favor of increasing the pctfree
value
and using that disk space within each data block.  Won't spreading a
segment
over too many data blocks also affect performance adversely?

-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/rg_mkgrp.xp   Create Your Own Free Member Forum

  Received on Fri Sep 18 1998 - 21:56:24 CDT