Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: datafile sizing question

Re: datafile sizing question

From: Tim Gorman <>
Date: Mon, 30 Sep 2002 06:18:23 -0800
Message-ID: <>

RE: datafile sizing question

Do your own testing.  Don't rely on papers.  Prove it yourself.  It's easy.
There are two types of "performance" implied in this discussion about extent allocation and deallocation:
There is no reason to suggest that the performance of DML might be affected by the number of extents, whether 1 extent or 500,000 extents.  Think about it.  Random, single-block reads (i.e. indexed scans) are completely unaffected by Oracle extent size and number;  they are block-level accesses, after all.  They care nothing about the concept of extent.  Sequential, multi-block reads (i.e. full table scans, fast full index scans) can only be affected if the extent size is extremely small but is completely unaffected by the number of extents.  Extremely small extents can obviously affect a multi-block read if they consistently limit the number of blocks that can be read.
Since testing this requires some non-trivial resources (i.e. test data and disk space) to prove, I'll leave the proving to those who have both (in addition to time).
This leaves DDL, which is mercifully easy to test on any environment using locally-managed tablespaces.  Do *not* do this type of testing in dictionary-managed tablespaces, as there is no point.  LMTs were created to alleviate the problems you'd be experiencing with DMTs...
Try an exercise like the following in SQL*Plus:
set timing on
create table bumpf (xxx number) tablespace <LMT-tsname>;
    for i in 1..<COUNTER> loop
        execute immediate 'alter table bumpf allocate extent';
    end loop;
end loop;
drop table bumpf;
Re-run the test for different values of <COUNTER>, all the way up to values like 250,000 or 500,000, if you like.  The timings for CREATE TABLE should be consistent, of course, as it is the exact same command each time.  The time spent in the PL/SQL loop should be roughly linear with the value of <COUNTER>, the point being that each ALLOCATE EXTENT takes roughly the same amount of time.  You might observe an "elbow" in the plotted curve of timings at some point which Rachel suggested at 4000 but I think will vary depending on your environment.  On my laptop, I've seen the curve stay linear up into the 100,000s.  The time spent in DROP may not vary a great deal;  it should be roughly linear with the value of COUNTER but I find that it is much better than linear, which leads me to believe that some parts of a DROP/TRUNCATE operation are asynchronous.
Try it out!
----- Original Message -----
From: Jamadagni, Rajendra
To: Multiple recipients of list ORACLE-L
Sent: Monday, September 30, 2002 6:33 AM
Subject: RE: datafile sizing question


Are there any studies or papers that test and explain this new magic 4000 extents number? My manager is excited about LMT, but no so excited about number of extents. So, if there is a good paper, I can make him feel happy about this ...

Thanks in advance
Rajendra Jamadagni              MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!

-----Original Message-----
From: Rachel Carmichael []
Sent: Monday, September 30, 2002 7:03 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: datafile sizing question

with evenly sized extents, there is no such thing as fragmentation anymore and Oracle can deal with objects with numbers of extents up to about 4000 before it starts to slow down a bit.

Please see the official ORACLE-L FAQ:
Author: Tim Gorman

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (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).
Received on Mon Sep 30 2002 - 09:18:23 CDT

Original text of this message