Re: Segment Fragmentation and Performance

Another reason why this view is not commonly held is the hangover from
Oracle 5 where the manuals gave the 'one extent - one segment - avoid recursive SQL' as a dictat.

The optimum strategy for almost all scenarios is to aim for all extents
in a given tablespace to be the same size so that dropping and recreating
objects does not result in unusable 'bubbles' of space.

The only reason for avoiding too many extents in a single segment is to keep the fet$ (free space) entries in the data dictionary within a

single block. fet$ can hold about 250 entries per 2K of database block size.

Personally I aim for around 20 extents for large objects, and consider
doing something about it if the number goes over 50. The only things that get into a single extent are the ones that are less than about 64K,
and then I'm quite happy to waste space a couple of megabytes on them if it happens to fit in with my select sizings for tablespaces.

Jonathan Lewis

garysadler_at_my-dejanews.com wrote in article <6tuknd$ohv$1_at_nnrp1.dejanews.com>...

> extent.

does not
> seem to be
Received on Sat Sep 19 1998 - 02:39:08 CDT