PCT INCREASE

[farrokhp]

Our Data and index Tablespace has PCT-INCREASE = 25 is that good or not? What's the rule of thumbs ?

Thanks

[julian]

A simple answer IMHO would be "not good". And my rule of the thumb is to use LMTs, not DMTs:

CREATE TABLESPACE ALPHA_DATA DATAFILE '/datadg/disk21/ads_data_01.dbf' 
SIZE 512M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

[farrokhp]

Can you explain more for DMT, we can't move to LMT

Regards

[julian]

Originally posted by farrokhp
Can you explain more for DMT, we can't move to LMT

Regards

Whay not? What version of Oracle do you use?

[grjohnson]

PCTINCREASE = >0 wil result in fragmentation. It's best left at zero.

[mrvajrala]

This question is to Julian

My database is running on 8.1.7 version, And i did see all i\of the tablespaces are DMT and not LMT,, so what all teh adavanrages i am going to get by converting the DMT to LMT..

would take some time for me,

Thanks,
Madhu

[farrokhp]

We run Oracle 8.0.5 . Is thar true that PCTINCREASE > 0 always create Fragmentation???? Can you explain a little why?

What I ahve to do now? ALTER TABLESPACE PCTINCREASE TO 0 , OR ALTER TABLESPACE FROM DMT TO LMT ?? Can you send me the script , Thanks man

[grjohnson]

farrokhp, Saying that it'll cause fragmentation ALWAYS, was probably a little quick fire on my behalf. If you NEVER have to recreate objects then you may never have fragmentation. (Very unlikely though) Additionally, having PCTINCREASE > 0 makes administration more difficult, ie. segment sizing because, really you don't know what the object NEXT extent size will be without some serious cacluations on your behalf. If you have large tables, it won't be long before your table is looking for a 2GB NEXT extents when the table stated out at 1 MG and had a 50% increase.

I would make at segments PCTINCREASE = 0 . I haven't come across a situation where I've need to have PCTINCREASE > 0 yet. Although, your situation might be different.

To change this, you'd have to change all the segments that have PCTINCREASE > 0. Just because your TABLESPACE has PCTINCREASE > 0 doesn't mean any segments have been created with this. (Tablespace params only get used when a segment is created in the tablespace WITHOUT the specific storage parms set).

If you said

CREATE TABLE TEST (a number);

Then it would use the TABLESPACE defaults to create the segment.

as opposed to

CREATE TABLE TEST (a number) STORAGE (INITIAL 1m NEXT 1m PCTINCREASE 0);

Which WON'T use tablespace defaults cause you've specifed them yourself.

mrvajrala - The advantages of using LMT far outweigh any adantage of using DMT's.

Cheers

[julian]

Originally posted by mrvajrala
This question is to Julian

My database is running on 8.1.7 version, And i did see all i\of the tablespaces are DMT and not LMT,, so what all teh adavanrages i am going to get by converting the DMT to LMT..

would take some time for me,

Thanks,
Madhu

I suggest that after using 8.1.7, you convert to LMTs. There are several advantages of LMTs to DMTs, make a search in this forum with keywords like LMT, advantage, etc. You will find a lot of information!

[rswamin]

when pctincrease is equal to zero oracle will not do a coalescing when fragmentation occurs. we have to coalesce explicitly by ourselves. but if the pctincrease is greater than zero oracle(SMON) will take care of coalescing automatically.
but managing the extent size becomes a real problem.so it is better to have the pctincreae equal to zero or a value closer to zero(say 1% or 2%).

regards

swaminathan