Re: Detecting xxx fragmentation/corruption?
Date: Fri, 9 May 2008 08:28:52 -0700
On Thu, May 8, 2008 at 5:46 PM, Dennis Williams < oracledba.williams_at_gmail.com> 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.
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
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 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.
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
code base. Even then though, frequently accessed objects should be cached.
-- Jared Still Certifiable Oracle DBA and Part Time Perl Evangelist -- http://www.freelists.org/webpage/oracle-lReceived on Fri May 09 2008 - 10:28:52 CDT