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 09:09:05 -0800
Message-ID: <>

RE: datafile sizing question

I don't know if you intended to "shout", but using color for your reply certainly does so...
Can you prove any benefit from the extraordinary actions of overriding LMT extent control and using EXP/IMP, ALTER TABLE ... MOVE, ALTER INDEX ... REBUILD, or whatever, when number of extents is the only criteria?
If so, was it DML or DDL statements which demonstrated improved performance?
Also, if there was any testing performed, then how did you isolate the issue of "number/size of extents" away from the issues of "row-migration" and 'blocks made empty due to deletion activity'?
What you are referring to as "fragmentation" (which has never meant simply "the number of extents") does not equate to "performance degradation" at all.  There is a difference in the possible performance impact of number/size of extents on DML statements as opposed to DDL statements, as previously explained.  Simply put, the "conventional wisdom" left over regarding extents in dictionary-managed tablespaces has little or no application in the world since the introduction of locally-managed tablespaces.
For example, a hundred million extents for a table or index would *not* impact the performance of indexed scans (i.e. DML) in *any* way whatsoever (due to single-block, random-access reads).  However, this situation would be catastrophic to any DDL involving extent allocation/deallocation in a dictionary-managed tablespace (requiring database recreation) as *every* other DDL involving extent allocation/deallocation could be crippled (due to abused cluster tables in the UET$/SEG$ cluster in the data dictionary).  By the same token, dropping or truncating a table with a hundred million extents would take a while even in locally-managed tablespaces, but the impact would not be as globally catastrophic for the rest of the segments in the database or for the database's data dictionary itself.
----- Original Message -----
To: ;
Sent: Monday, September 30, 2002 9:05 AM
Subject: RE: datafile sizing question

If Next Extent Sizing is NON-Uniform for an LMT , will the Larger Number of Extents cause Fragmentation & Performance Degradation ?
If so What Number of Extents may be Considered as a Candidate for DE-Fragmentation ?
NOTE - We have been Manually Specifying the Size of the NEXT_EXTENT of Objects in LMTs by Converting ALLOCATION_TYPE (sys.dba_tablespaces) from "SYSTEM" to "USER"  for respective Tablespaces to Check Growth to Larger Numbers of Extents
100-200 Extents we Consider as a Candidate for DE-Fragmentation using exp/imp OR ALTER TABLE/INDEX ... MOVE with Bigger Extent Sizes
Oracle 8.1.7
-----Original Message-----
From: Tim Gorman []
Sent: Monday, September 30, 2002 7:48 PM
To: Multiple recipients of list ORACLE-L
Subject: 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:
  • performance of SQL statements like SELECT, INSERT, UPDATE, DELETE (i.e. DML)
  • performance of statements like CREATE, ALTER, DROP, and TRUNCATE (i.e. DDL)
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 - 12:09:05 CDT

Original text of this message