Re: segment fragmentation
- From: "Daniel W. Fink" <Daniel.Fink@xxxxxxx>
- To: thump@xxxxxxxxxxxxxxxx
- Date: Wed, 08 Sep 2004 16:26:34 -0600
David,
After a mass delete, you will have empty space in your segments
(sparsely populated or even empty blocks). A main consideration is how
and when will this space be reused? Oracle is pretty efficient at
reusing space, so the holes may be filled up fairly quickly. If you
reorg the segments to remove the fragmentation, then 'new' blocks will
be used. If you purge 25% of the rows, but will reuse the space within a
few weeks, it *may* not be worth the hassle of 'defragmenting'. If you
are purging 75% of the rows and the space won't be reused for several
months, it *may* be worth it.
In terms of performance and execution plans, a 'high' highwater mark
causes more i/os than are really needed and causes the CBO to calculate
the cost of a FTS at a higher value in comparison to a table that has
packed blocks and a reasonable HWM.
If you reorg the tables and rebuild the indexes, you are going to see
changes in how the CBO calculates cost and selects an execution plan.
This should be a good thing. Pay careful attention to index range scans
as the clustering_factor will change. Not because of the index rebuild,
but because of the table rebuild. If someone tells you differently ask
for proof (I'll be glad to prove mine assertion if you want).
Regards,
Daniel
David wrote:
>I have been asked to determine segment/object fragmentation levels after a
>mass delete has been performed.
>
>Do LMT only negate fragmentation occuring at a tablespace level as an
>issue and segment fragmentation and high water mark issues are still
>relevant after a mass purge?
>
>How does one check for this type of fragmentation?
>
>It has been requested I perform an export/import of said schema after
>verifying segment fragmentation exists.
>
>By the way, I have finally purchased Optimizing Oracle Performance and
>have begun the reading.
>
>Regards,
>
>
--
To unsubscribe - mailto:oracle-l-request@xxxxxxxxxxxxx&subject=unsubscribe
To search the archives - //www.freelists.org/archives/oracle-l/
Other related posts:
