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.

I couldn't agree more with what pando (and some not_enough_well_known_whitepapers) say about fragmentation and extent sizing.

This fragmentation question is an old and ever-lasting topic among DBA comunity, yet still so many DBAs spend so much energy and time (and money) in defragmenting their tablespaces and belivs in mith that "fewer extents is better for performance, the above limit for number of extents should be 5/10/20....".

I also like the way how marist89 catches his developers when they sneak some segments into production database. I've heard even better and nasties one. Suppose your largest datafile in a tablespace is 500M. Set the default tablespace storage parameters so that INITIAL and NEXT are larger than this, say 600M. That way developer will not be able to create segment in a tablespace unless he set the storage parameters at segment level which forces him to think about this stuff and maybe even let his DBA to explain how storage parameters work and why are they used ;-)).

'...initial 128K extent size will be multiples of data_block_size * 5 ...'

Assume db_block_size is 8K

when we set initial 128K without using minimum extent clause even we have set size to 128K, if you check the actual size it will say 160K, the reason is because by default Oracle allocates data_block_size * 5 which would be 40K in this case but since we requested an initial of 128 it will allocate multiple of 40K round up which would be 160K

As for number of extents issue I dont think you yield same perfomance with a table of 10 extents with another same table of 1000 extents since the data in 1000 extents would be so dispersed physically. I think this is why cluster yields better perfomance in some cases no? Because the data are physically stored together.
However I do think that as long as extent size are uniform I wouldnt worry too much about number of extents. Priority one would be to achieve uniform extent sizes.

I do agree with Pando. As for as Production databases, I would prefer to take off table level stroage clause, which defines the objects @ tablespace storage clause and go with
small(ext size 128K),Medium(ext size 4M) and large(ext size 128M) tablespaces sa suggested in that tech white paper. Pesonally this is how I will set up our databases.

I enjoyed the document about SAFE (Simple Algorithm for Frag Elimination)... mentioned earlier in the thread.

I think that I will try to incorporate those rules in my shop as well (if possible).

Great document..

Does everyone agree that 128k should be (in theory) the smallest size extent used?