Re: Detecting xxx fragmentation/corruption?

  • From: "Jared Still" <jkstill@xxxxxxxxx>
  • To: oracledba.williams@xxxxxxxxx
  • Date: Fri, 9 May 2008 08:28:52 -0700

On Thu, May 8, 2008 at 5:46 PM, Dennis Williams <
oracledba.williams@xxxxxxxxx> 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

Other related posts: