Re: segment fragmentation

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_at_freelists.org&subject=unsubscribe 
To search the archives - http://www.freelists.org/archives/oracle-l/