Re: Old Chestnut: Tablespace Fragmentation

Your best best is to quantify this mathematically. Take the following example:
Case 1: 100GB table, one extent
Case 2: 100GB table, 1000 extents

Assume:

1. track to track seeks are 'free'
2. random seeks are 20ms
3. Block size is 16KB
4. db_file_multiblock_read_count=16
5. multiblock read time=8.6ms (29MB/s conservative for 10k drives)
6. total # reads=409600
7. one drive only (a very big one...)

Case1:
Time for FTS= 409600*8.6ms=3522s (~ 1 hour)

Case2:
Time for FTS= 3522s (as above) PLUS 1000*20ms= 20s - TOTAL=3542s

The difference is minor in this case (0.5% greater elapsed time) and 1000 extents would put each at ~100MB in this case. If you had <cue Dr. Evil voice> 1 million extents, it would be a different story - about 668% longer...

Hope that helps - there's an infinite number of shades of grey, so it's important to do the math!
Regards

James

Bill Buchan wrote:

>
>
> I know this one has been done to death: use uniform extents to avoid
> fragmentation; multiple extents don't hurt (within limits).
>
> But what if:
>
> Data Warehouse, one big table on a single disk, full table (batch)
> scan, no concurrent transactions on the database (so no contention for
> the disk), no fragmentation at the file system level, initially empty
> buffer cache (startup), read-only operation so DBWR isn't doing
> anything on this disk. Basically I want to read one data file from
> end to end. Surely it would make sense to have the disk read moving
> smoothly from one end of the disk to the other rather than bouncing
> about all over the place as it may do with multiple extents "randomly"
> allocated.
>
> Any thoughts?
>
> Thanks
> - Bill.
>

-- 
James Morle
Scale Abilities, Ltd
http://www.scaleabilities.co.uk
Author of "Scaling Oracle8i - Building Highly Scalable OLTP System Architectures"



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: James Morle
  INET: James.Morle_at_scaleabilities.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).