PCTINCREASE

[farrokhp]

I heared that PCTINCREASE must always be zero to ensure minimum fragmentization. So in which situation it is good to have it not zero ??

[kgb]

Ther is SMON:
It coalsces contiguous areas of free space in database tablespcaes, where the PCTINCREASE != 0.
So I prefer = 1

[mstarkman]

You can still minimize fragementation on the tablespace when setting the pctincrease to 0. Make sure to set the pctincrease to 0 when creating your tables

[vijay8282]

I think that deciding factor abt pct increase if the volatility of your ata, i mean inserts etc. and it is advisable to kep PCTINCREASE=0 for rollback segs only.

so for data segs it is good to keep PCTINCREASE > 0 as it also facilitates SMON i coalces space.

what do u say....

[aghosh]

Hi,
I feel that keeping PCTINCREASE as 1 or similar very small figure will help in fragmentation. Yes, SMON will coalesce, free space from time to time, but it it is always advisable to try and reduce fragmentation as well.


Regards

Amit

[oddster]

Hi all,

Pctincrease should be set to 0.

This ensures that all extents allocated to a tablespace remain the same size, thus minimizing fragmentation.

If Pctincrease is set > 0 then deallocated extents are not reused due to the fact that they would not be large enough to be 'Next' extent.

Of course, as the others have said, SMON will now not coallesce the free space.

You must do this manually on a regular basis - I run contab jobs to perform this in a Unix env.

So unless you are lazy - set the pctincrease to 0!!!!!

Good luck!

[marist89]

If PCTINCREASE is > 0 then smon will automatically coalesce your free space.

Personally, I like to set the PCTINCREASE for the tablespace at 1 and then set the individual objects at 0. I get a couple of advantages this way:
1. By having pctincrease=1, smon automatically coalesces my free space.
2. By having pctincrease=1, I can tell when my developers decide to create a table without getting sizing information. I can then go back and resize the table appropriately and put it in the appropriate tablespace.
3. By having pctincrease=0 for the objects, I guarantee that my objects will be able to re-use any blocks that they give up.
4. In fact, I set the INITIAL and NEXT paramters of all my objects the same as the INITIAL and NEXT for the tablespace. Every object in the same tablespace has the exact same INITIAL and NEXT extent. This way, I can simulate LMTs in Oracle 7 & 8.0 and never worry about fragmentation again.

[pando]

there is a reason behind, why SMON doesnt coalesce if pctincrease is > 0? The reason is there is no need to coalesce if your extents are multiple size of each other. These free extents will always be reused, what is the point to coalesce 4 extents of 128K and have a big 512K chunk if your 4 free 128K extents will be resued by other segments, if you coalesce them when Oracle needs one 128K extents it then has to split that 512K into one 128K extent and one 384K (extra job!). If coalesce didnt take place Oracle simply has to get one extent from those four

And this is the use of minimum extent clause in create tablespace command, if we set to 128K no matter the extent size we specify to initial and next at tablespace and segment level they will always be multiples of 128. For example if we set initial 250K and next 500K in a table creation Oracle will round those initial and next to multiple of 128, in this case 256K and 512K

I recogn the key thing is having extents with sizes which are multiple of each other

[Edited by pando on 06-26-2001 at 12:12 PM]

[chrisrlong]

See!. I knew you guys would have some *awesome* feedback.

I *love* your trick for catching developer-created tables. Very cool!

I was wondering about the applicability of the SMON process as pando described - If they are already re-usable, then why coalesce?

But I don't see that simply having the extents be multiples of each other is nearly as beneficial. Basically:

- if compeltely different extents - have fragmentation problems and need to coalesce

- if all extents are multiples of some base, say 128K, then still need to coalesce in order to minimize fragmentation, which is still possible. Example: What if I only had one table where the extent size actually matched the base size and I deleted most of the records? This would leave some 128K 'holes' sitting around that no other table could re-use.

- if all extents exactly the same, then no need to coalesce and fragmentation is truly minimal (and only really exists in the short term, since all extents can be re-used)

Correct?

So, the optimal would seem to be to have a single extent size applicable to each tablespace. PCTINCREASE at the table level always = 0. At the tablespace, it is probably more efficient to have it =0 as well. Although I think that having some trigger to alert you to developer-created tables would be extremely beneficial.

Does this make sense? I'm weak on the admin stuff, so be sure to check my math .

So, if this is correct, why is it not the default behavior? That's almost a rhetorical question - Oracle thrives on its complexity. I would think this should be the given standard for any new database, though.

If not, why not?

Also, how do LMTs play into this?

- Chris

[pando]

Hi

if we have an extent of 128K and we deleted most record that extent is still not free (stills belongs to the segment)! Therefore even we coalesce that extent would have anything to do with SMON since SMON only deals with free extents

LMT's theory is based on this multiple extent size and uniform extent size. LMT's extents are always same size or always multiple of each other