PCT INCREASE

Printable View

03-04-2002, 11:52 PM
farrokhp

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

Thanks
03-05-2002, 03:08 AM
julian

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

Code:

CREATE TABLESPACE ALPHA_DATA DATAFILE '/datadg/disk21/ads_data_01.dbf' SIZE 512M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
03-05-2002, 07:52 AM
farrokhp

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

Regards
03-05-2002, 08:20 AM
julian

Quote:

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?
03-05-2002, 06:39 PM
grjohnson

PCTINCREASE = >0 wil result in fragmentation. It's best left at zero.
03-05-2002, 07:41 PM
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
03-05-2002, 11:52 PM
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
03-06-2002, 01:31 AM
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
03-06-2002, 02:41 AM
julian

Quote:

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!
03-06-2002, 07:48 AM
rswamin

coalescing

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
03-06-2002, 07:58 AM
Sureshy

farrokhp - get with the times

http://www.oracle.com/oramag/oracle/...ml?o60o8i.html
03-08-2002, 10:29 AM
jacoch

For a good discussion on Fragmentation look at the

All About ORacle Database Fragmentation article on

http://www.orapub.com.

I think that if you set PCTINCREASE to 1 on the Tablespace but to 0 at the tables you will still get automatic coallision but less fragmentation.
03-08-2002, 02:34 PM
ocpwannabe

Re: coalescing

Quote:

Originally posted by 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

if u set pctincrease=0, you won't ever have to coalesce...
03-10-2002, 07:32 PM
grjohnson

ocpwannabe - You'll still have to coalesce if your segments are of different sizes (especially different sized Index segments int he same tablespace).

Additionally, I suppose to make SMON colaesce automatically, setting you could set PCTINCREASE on the tablespace level. Taking into account that this change will affect newly created objects which have NO PCTINCREASE specified.

But as previously stated, I'd be inclined to set to 0, unless you are going to monitor vigourously newly created objects to make sure they aren't taking the TABLESPACE default of PCTINCREASE > 0.

Cheers
03-11-2002, 05:40 AM
julian

Quote:

But as previously stated, I'd be inclined to set to 0, unless you are going to monitor vigourously newly created objects to make sure they aren't taking the TABLESPACE default of PCTINCREASE > 0.

Cheers

I agree with you. All that stuff about setting PCTINCREASE to 1 or 2 does not make sense at all. What not switch to LMTs and stop bothering with PCTINCREASE :-)