| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Segment Fragmentation and Performance
garysadler_at_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
I thoroughly agree - make your extents a multiple of blocksize * multiblock read count and you are a hell of a long way toward things (ie table scans) running just dandy in (almost) any number of extents...
Have fun
 |
 |