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

Other related posts: