Re: Oracle Myths

Ed Stevens wrote:

> On 17 May 2002 03:23:23 -0700, p_byrne76_at_hotmail.com (Pascal Byrne) wrote:
>
> >"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:<3ce21b71$0$8510$ed9e5944_at_reading.news.pipex.net>...
> >> PCTIncrease should be as small as possible but non-zero to minimize
> >> tablespace fragmentation.1% is a good value (from my OCP course notes though
> >> not necessarily given by the tutor!)
> >
> >I've seen this quoted in a recent paper by Michael R. Ault (author of
> >"Oracle8i Administration and Management"). The reason given was that
> >SMON will not coalesce free space if PCTINCREASE is zero. Is this
> >definitly a myth?
> >
> >-Pascal
>
> I can't say for sure, but my bet is that this falls into the catagory of "not a
> myth, but so what?" It's my understanding that with proper tablespace
> definition and management, tablespace fragmentation is itself a non-issue.
> Whether my understanding of that "fact" is correct or not, the real point I'm
> tryng to make is that in some cases, the basic "statement of truth" ( in this
> case it is "SMON will not coalesce free space if PCTINCREASE is zero.") may
> still be true, but other features/functions make it a non-issue. Kind of like
> the old story of the housewife who always cut an inch off the end of a ham
> before putting it in the baking pan.
> --
> Ed Stevens
> (Opinions expressed do not necessarily represent those of my employer.)

My best guess would be that it was valid back before 8i and before LMT.

Then I always created my tablespaces with PCTINCREASE 1 and my tables with PCTINCREASE 0.

But why oh why oh why does Oracle, itself, continue to create the system tablespace with the defaults it does? Is it due to optimization or just a reluctance to change it?

Daniel Morgan Received on Fri May 17 2002 - 11:12:56 CDT