Re: Setting pctincrease 0 at the tablespace level

Niall Litchfield wrote:

> comments below
> "Dino Hsu" <dino1_at_ms1.hinet.net> wrote in message
> news:ia02ht0iku7utfb2t6fkocr0geecrjkkig_at_4ax.com...
> > Dear all,
> >
> > In a book, it says:
> > "Note: Setting pctincrease to 0 at the tablespace level affects
> > ORACLE's ability to automatically coalesce free space in the
> > tablespace. Set the default pctincrease for the tablespace to a very
> > low value, such as 1."
> >
> > My questions:
> > 1.Why the free-space-coalescing problem?
>
> because SMON the background process that coalesces free space, doesn't when
> PCTINCREASE is set to zero.
>
> > 2.Do you think pctincrease=1 is good?
>
> well it is better than the default of 50, but see below.
>
> > 3.Do you think fixed extent size stand well most of the time?
>
> <opinion> This should be the default situation for all of your databases.
> Anyone who wishes to change this policy for a particular situation should
> have a well argued reason as to why. If you rigorously stick to a fixed
> extent size per tablespace then you will NOT have fragmentation problems.
> All objects in the tablespace use the same extent size, so there will never
> be a situation where there is enough free space available, but in smaller
> contiguous chunks than the size of the next extent. </opinion>
>
> It might be worth (re)reading the extended discussion about locally managed
> tablespaces between Nuno and Howard, amongst others from about a month ago
> on this group, this effectively outlines the considerations in some depth.
> (you can use locally managed tablespaces as a method of forcing uniform
> extent size).
>
> HTH (and is right this time)
>
> --
>
> Niall Litchfield
> Oracle DBA
> Audit Commission UK

But if you have Oracle 8i use locally managed tablespaces and you can forget the whole thing.

Daniel A. Morgan Received on Sun May 27 2001 - 20:28:22 CDT