Re: Detecting xxx fragmentation/corruption?

From: Jared Still <>
Date: Fri, 9 May 2008 17:57:15 -0700
Message-ID: <>

On Fri, May 9, 2008 at 5:34 PM, Peter Teoh <> wrote:

> 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?

It eliminates tablespace fragmentation.

> > 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?

You don't have any control over which track on the disk your data is put. In a carefully crafted test environment you could have this sort of control, but not normally in a production system.

If your data is on RAID, then it is separated across several disks already.

> 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)

If you had that level of control, there was only 1 session on the database, you might be able to do something like that.

Personally, I think you're overthinking this.

Do you have a specific performance problem targeted?

Or are you trying to determine if you have one?

If the the latter, just ask the users, they will tell you.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

Received on Fri May 09 2008 - 19:57:15 CDT

Original text of this message