Re: Detecting xxx fragmentation/corruption?

From: Peter Teoh <>
Date: Sat, 10 May 2008 08:34:19 +0800
Message-ID: <>

On Fri, May 9, 2008 at 11:28 PM, Jared Still <> wrote:
> On Thu, May 8, 2008 at 5:46 PM, Dennis Williams
> <> wrote:
>> Tablespace fragmentation - You might take a look at LMT with uniform
>> extents. My understanding is that this prevents tablespace fragmentation.
>> This might be easier than spending a lot of time yourself, unless you have
>> more free time than most DBAs do.
>> ...
>> If you are fascinated by fragmentation, you might consider looking into
>> TABLE fragmentation.
> Perhaps fragmentation should be defined here for readers that might not be
> familiar with it.
> Tablespace fragmentation:
> When many objects are created in a dictionary managed tablespace, the
> objects can be created
> with arbitrarily sized extents. Over time this can lead to small fragments
> of space in the tablespace
> that are isolated between larger groups of extents. There size is too small
> to be used by any
> object in the tablespace.
> Such space sometimes can be reclaimed by modifying the NEXT size of one or
> more objects,
> manually allocating extents, and then resetting the NEXT size.
> Doing so will lead to the another type of fragmentation.
> This could be alleviated by using the same INITIAL and NEXT sizes on all
> objects in the tablespace,
> but this was difficult to keep up.
> Creating locally managed tablespaces with uniform extent sizes eliminates
> this problem.
> This has been around since 8i (8.0?) and all new tablespaces really should
> be created this way.
> SEGMENT SPACE MANAGEMENT AUTO isn't a bad thing either. There are some bugs
> associated with it, but I haven't run into them myself.

Specifically on this - uniform extent size - it will not prevent or ELIMINATE fragmentation, right? My understanding is that it will only reduced the probability of getting fragmented. The simple reason (*Please correct me!!!*) is because when all the extent sizes are uniformly the same, it is much easier to allocate/deallocate stuff, and thus implement the defragmentation algorithm to reorganize it in contiguous format, correct?

> Table/Index fragmentation:
> When the extents of a table or index are scattered all over a tablespace, it
> is said to be fragmented.

Unlike memory, where access time almost zero, accessing the disk is much slower. So sometimes I thought it will be better to spread out the data - thus enable simultaneous read by the different heads in the disk, just like those RAID design. Ie, fragmentation via distribution the blocks out in the disk can improve performance - can such things happened?

> If the access patterns are strictly OLTP (single row or small/infrequent
> range scan access) it may
> not matter too much.
> If there are full table scans, and or fast full index scans and range scans,
> eliminating the fragmentation
> can be quite beneficial.

For FTS scenario, if the blocks are NOT arranged contiguously, BUT UNIFORMLY sparsed out somehow, (your fragmentation scenario), the disk head may be possible to read ALL THE BLOCKS CONCURRENTLY instead of sequentially as in the contiguous design, thus speeding up FTS. Possible? (Ie, uniform fragmentation is GOOD)

> Of course, poor design often is the cause of this type of access in
> transaction based systems, but as
> noted in another thread, we technical types rarely have a say in changing
> that.
> Getting back to the topic of stored code fragmentation, I wouldn't think it
> terribly important, as it
> _shouldn't_ be read from disk all that often, should it? :)
> There may be some exceptions to that, such as Oracle eBiz apps, which has a
> huge stored
> code base. Even then though, frequently accessed objects should be cached.
> --
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist

Peter Teoh
Received on Fri May 09 2008 - 19:34:19 CDT

Original text of this message