EXTENTS

1. How do one decide the sizes for initial, next and max extents while creating a tablespace ?
Is there a rule ?
2. What should be PCTINCREASE= 0 or any other value ?
3. How do you DEALLOCATE extents and how to find out if I have too many extents for a objects ?


Thanks
Sonali

1. I like to have really small defaults for initial and next extents. That way, I can tell when my developers sneak a table into production and don't tell me (it will extend really quickly). I usually set maxextents to unlimited.

2. I set the default pctincrease=1 on the tablespace for a couple of reasons. First, if pctincrease=0, then smon won't kick in and coalesce the free extents. Second, it also helps me catch my developers sneaking something into production.

3. via alter table xyz deallocate unused
Check out http://technet.oracle.com/docs/produ...2a.htm#2056065

Initial extent is the number of bytes your object preallocates when it's first created. Next extent is the size of all following initial extent extents. Maxextents is the maximum number of extents you're allowing per object. It could be a set number or unlimited.
The goal is to keep a fewer extents in any object.
I know it is a good practice not to have more than 2 extents for an index. If your index has more than 2 extents you can rebuild it with right parameters.
If you know or can estimate the size of the data in the table make your initial extent equal equal to the number of bytes you're predicting to have. If you don't know what you're going to have make your best estimate and try to keep as little extents as possible.
To find out how many extents are in the object you may run the following query:
select extents
from sys.dba_segments
where segment_name like '%%'
and segment_type='';
As to pct increase, I think it is better to keep it 0.
Hope it brought some light on your question. This would not be a complete answer. May be someone can add more.

select extents
from sys.dba_segments
where segment_name like '%%'
and segment_type='';

strange.... it doesn't post anything inside % or <> or ' '.
anyway, the query should be:
select extents
from sys.dba_segments
where segment_name like your object name
and segment_type=your object type;
subsitute your object name to the name of your object in quotes and your object type to the type of your object in quotes.

I always create as follows

create tablespace XYZ
datafile '/u01/prod/XYZ.DBF' XXMB autoextend on next 1028K maxsize YYMB
minimum extent 128K
default storage(
initial 128K
next 128K
minextents 1
maxextents 4096
pctincrease 0
)

minimum extent will ensure all extents size are multiple of 128K because if I dont set minimum extent even if I set initial 128K extent size will be multiples of data_block_size * 5 and because 128K is normally a good size to optimize I/O.
pctincrease 0 because when I have extents with same multiple sizes (128, 256, 384 etc) I dont have to worry about fragmentation I dont have to coalesce the extents neither because extents with same multiple size ensures that when they are deallocated they can be reused by other segments.

I dont deallocate the extents, Oracle will do itand as long as I keep the extent size uniform everything should go smooth.

You find number of extents of segments in view DBA_SEGMENTS column extents.

ahh, interesting. I didn't know about the "minimum extent" clause.

Do you think that an optimal minimum extent size should be db_block_size * db_multiblock_read_count?

mariest i think so, at least this is how Oracle whitepaper suggests, since 128K is multiple of 64K and if I am correct I think most OS I/Ois 64K.
Oracle suggests db_block_size * db_multiblock_read_count tooptimize full table scans, if that was the case it would be 64K, now this is my personal view i think 64K extents are simply too small in a considerable size database so i set it to 128K. If we use 64K we will have tons of extents (which i dont think is good) even Oracle states that having many extents doesnt affect perfomance (this was said by an Oracle Senior instructor) I do think it affects since the hard drive head would have to move too much in order to retrieve data.

Here you go with white paper on 'how to avoid fragmentation', pando is talking about.

http://metalink.oracle.com/cgi-bin/c..._cr.cgi?239049

This z what our dba group follow in our shop.



[Edited by sreddy on 01-04-2001 at 08:58 PM]

Pando,
would you please explain this statement:
'...initial 128K extent size will be multiples of data_block_size * 5 ...'
What is it trying to accomplish and why.
Thank you.