Re: Detecting xxx fragmentation/corruption?

From: Jared Still <>
Date: Fri, 9 May 2008 08:28:52 -0700
Message-ID: <>

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.

Table/Index fragmentation:
When the extents of a table or index are scattered all over a tablespace, it is said to be fragmented.
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.

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

Received on Fri May 09 2008 - 10:28:52 CDT

Original text of this message