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:
a) track to track seeks are 'free'
b) random seeks are 20ms
c) Block size is 16KB
d) db_file_multiblock_read_count=16
e) multiblock read time=8.6ms (29MB/s conservative for 10k drives)
f) total # reads=409600
g) 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
