I know that the DBA handbook advises that a zero PCTINCREASE on tablespaces. Our System, RBS and Data tablespaces have a PCTINCREASE of 50 - are these the defaults. Should I be concerned? Should/Can I change them?
I believe our consultant set them up that way but I'm not sure why.
11-28-2000, 07:41 PM
I believe the default is 50. You need not be concerned, and yes, you can change them.
11-29-2000, 04:46 AM
Zero is better since you get uniform extent size, uniniform extent size leads to freespace fragmentation
11-29-2000, 12:13 PM
could it be that our consultant set up the system, rbs, temp and data tablespaces with the default b/c he wanted to allow those tablespaces to extend? Is it recommended to change it to 0?
11-29-2000, 12:28 PM
PCTINCREASE doesn't really have anything to do with tablespaces extending.
PCTINCREASE deals with the segments inside the tablespace. For example, if you have a table that has a next extent of 10K and PCTINCREASE is set to 50, the next-next extent would be 15K. The next-next-next extent would be 22.5K, etc.
If your PCTINCREASE is set to 0, all extents would be 10K, thus uniform extent sizes.
I like to set the default PCTINCREASE at 1 for the tablespace and then all the objects have a pctincrease of 0. This way, I can tell when my developers sneak a table in without sizing it correctly and don't have to worry about the table extending out really quickly.
11-29-2000, 01:50 PM
A non zero value will ensure that tablespace coalesce happens as and when smon wakes up.But the problem with 0 is that you have to manully coalesce the tablespace.
for temp and rbs keep pctincrease as zero .for all others make it 1.
11-29-2000, 01:59 PM
is is better in the long run to have a script to coalesce your tablespaces instead of having to deal with the non-uniform extent sizes from the pctincrease?
11-29-2000, 02:31 PM
If you have uniform extents you will never have to coalesce that is why when pctincrease is zero SMON doesnt coalesce the extents because simply there is no need to.
11-29-2000, 03:48 PM
You create the segments with pctincrease as zero and for tablespace 1.