Tianhua, July 25, 2001 - 2:06 pm UTC
Mahendra Jadav, November 04, 2001 - 3:19 am UTC
Minimum Extent
a reader, January 28, 2004 - 3:50 am UTC
Hi,
The clarification of initial and next extent was very helpful as usual.
I've a confusion (sorry if its silly one).
Biju Thomas says 'MINIMUM EXTENT controls frgmentation in the tablespace by allocating extents of at least the size specified. The INITIAL and NEXT extent sizes u specify should be a multiple of MINIMUM EXTENT'. He gave an example:
Create tablespace.....
Default storage(
Initial 256k
Next 256k
Minextents 2
Pctincrease 0
Maxextents 4096
)
Minimum Extent 256k ;
I don't understand What is multiple of what??
Please help.
Thanks for your time.
January 28, 2004 - 8:37 am UTC
256 is a multiple of 256 as would be 512, 1024, and so on.
N*256 in his example, for any value of N would be a "multiple"
Regarding Minimum Extent
a reader, January 29, 2004 - 3:08 am UTC
U mean the following statement
create tablespace..
default storage( initial 265
next 256
pctincrease 0)
minimum extent 512;
Is fine?? Is N=initial/next extent?
If I don't specify minimum extent it'd take 1 and work perfectly.
But what would happen if the above tablespace is created with minimum extent 512??
sorry to bother u once again.
Thanks for ur help.
January 29, 2004 - 8:05 am UTC
well, it is a little silly since you are saying "use 256 but at a minimum use 512"
the only way you should do this would be:
create tablespace t extent management local .......
and forget about storage clauses forever more.
Regarding Minimum Extent
A reader, January 29, 2004 - 8:04 am UTC
Oops! sorry by mistaken I wrote minimum extent instead of minextent. Pls ignore the above one. Hope u won't mind since I'm new to ur website.
My questions:
Do u mean initial and/or next extent size is multiple of minimum extent?
Is the following statement correct?
create tablespace..
default storage( initial 265
next 256
pctincrease 0)
minimum extent 512;
Is N=initial/next extent?
If I don't specify minimum extent it'd take how much?
What would happen if the above tablespace is created
with minimum extent 512??
sorry to bother u once again.
Thanks for ur help.
January 29, 2004 - 8:14 am UTC
it would NOT make sense to have initial and next less than the minimum extent size.
it would only make sense to have them be N*minimum extent size where N in ( 1, 2, 3, 4, ...... )
Re: minimum extent
reader, January 29, 2004 - 8:28 am UTC
Thanx a lot for your help and time. I know it was a silly one. I just got nervous while asking u.
LMT extent size
Deepak, November 29, 2006 - 5:29 am UTC
Hi Tom,
In your answer you have quoted...
"Small objects (lookup tables and such) go into a tablespace where all objects use 64k extents. Slightly larger objects might go into a tablespace that uses 256 or
512k extents. Medium sized objects go into a tablespace with 1meg extents. Truely large objects go into appropriately sized tablespaces."
I have few questions on the above...
1> What is criteria of measurement for SMALL, MEDIUM, LARGE and VERY LARGE objects?
2> The default uniform extent size for LMT is 1M. So Will it be a bad idea to keep it as is even for small objects like look up table. If we do so what will be the impact on performance and space utilization?
I really need some hint in deciding about the extent sizes for various sized objects. Please help me in this regard.
November 30, 2006 - 8:59 am UTC
Actually, over the years since this was published - I've come to recognize that system allocated extents are very nice.
I've changed my position on this entirely. Do not use uniform - just use system allocated extent sizes. Segments will start small and grow in extent size as needed.
System Allocated Extents
Deepak, November 30, 2006 - 11:36 am UTC
Hi Tom,
Thanks a lot for your nice explanation. But just a bit curious to know how does Oracle determine the NEXT extent size in case of system allocated extents.
November 30, 2006 - 1:58 pm UTC
by magic, using an internal algorithm of its choosing.
Marc, August 11, 2009 - 1:01 am UTC
Tom,
I've been reading lots about extents and there are many different views, even within Oracle. Should we let Oracle auto allocate extent sizes or should we use a uniform size?
I'm currently working on a Siebel project and have been asked by a consulting firm that I need to create tablespaces with uniformed extents. However I questioned why they wanted to follow this approach, I was told it is because of fragmentation.
As an example, they want me to create the following:
Name Size Initial Extent Next Extent
TABLESPACE NAME 25G 1G 1G
Not agreeing with their approach, I then started to read some articles (from your site and others) and even asked Oracle for their view. I was told by an Oracle Architect (without mentioning any names) that Oracle recommend the following;
1. Segments smaller than 128 MB should be placed in 128KB extent tablespaces
2. Segments between 128 MB and 4 GB should be placed in 4 MB extent tablespaces.
3. Segment larger than 4 GB should be placed in 128 MB extent tablespace
All segments in a tablespace should have exactly the same extent size. This insures that any free extent in a tablespace can always be used for any segment in the tablespace. It is recommended that the extents are set to UNIFORM.
Some believe we should use UNIFORM extent sizes and others believe let the database worry about it.
Can you please clear up my confusion?
August 13, 2009 - 9:03 am UTC
My advice in the year 2009 is
use autoallocate. period. A 10gb segment will have a few hundred at most extents and that is great.
... I was told it is because of fragmentation. ...
ask them for the science behind that. autoallocate is designed to avoid that as much as possible.
Table 20gb Extents Sizes
Mohan, June 06, 2012 - 3:28 pm UTC
Tom,
we run 11g, and for the data loading efforts we have the staging table that gets loaded about 140 million rows daily and everyday we truncate it before a load. What should we do with initial extent and next extent sizes ? does it even matter ? does the free space after truncate gets into the free area ?
June 06, 2012 - 5:26 pm UTC
if you are using locally managed tablespaces - it does not matter. You don't set them
if you are using dictionary managed, switch to locally managed.
Eriks Mierins, July 26, 2012 - 12:21 pm UTC
I would like to answer on Your question "what science behind that"
when UNIFORM is recommended instead of SYSTEM.
I had a case when on one DB FullScans of large objects (db file scattered read
wait time per wait) were 5 times slower than on other DB. No difference in server
hardware, parameters, disks, etc.
Only difference found was allocation type SYSTEM on slower DB and UNIFORM on
faster DB. And the real cause of performance difference was found on disk array
controller prefetch statistics:
- more that 90% hit with UNIFORM;
- and less than 10% hit with SYSTEM.
At the moment any disk array controller and any file system have sophisticated
read-ahead cache algorithms. And those algorithms better work when large
uniform extents are read from file. They can not understood how to prefetch data
from disk when 16 times 64K extent read, then 63 times 1M extent read,
then 8M extents and finally 64M extents read.
My recommendation would be:
- if file system or disk array controller will be used;
- and FullScans on large objects will happen;
then large uniform extents are best choice for performance.
It will take full benefit from read-ahead caching and that can improve FullScan
performance even 5-10 times.