Re: Question on Installing Oracle.

PCTINCREASE is one of the most annoying aspects of Oracle. Fortunately, with Locally Managed Tablespaces the point becomes moot. However, I've heard this debate numerous times. But one day, after an L.A. Oracle User's Group meeting, I had a conversation with one of the speakers from Oracle. He put this into perspective for me.

PCTINCREASE enables automatic coalesce if PCTINCREASE is greater than zero.

Automatic coalesce only coalesces extents. Oracle coalesces the contents of data blocks when it wants to. Oracle NEVER coalesces rows between blocks (potentially your greatest waste of disk space). You have to either export, DROP, and import... or in 8i you can ALTER TABLE xyz MOVE TABLESPACE abc. (I make my students recite much of this paragraph).

Automatic coalesce isn't necessary. It checks periodically to see if a tablespace needs to be coalesced. Because it only coalesces extents, it only needs to coalesce if you have dropped objects from the tablespace. Objects are seldom dropped from tablespaces except in a development environment. In any case, objects are almost NEVER dropped from the SYSTEM tablespace. If, after you drop one or more tables, indexes, sequences, etc. you simply remember to ALTER TABLESPACE the_tablespace COALESCE and you've eliminated the fragmentation at the extent level.

If PCTINCREASE is not specifically set it always defaults to 50. Somebody in development at Oracle fell in love with PCTINCREASE and auto coalesce, other than that there is no technical reason for it to be the default. It never has to be 50, even on the SYSTEM tablespace. I always set it to zero on the SYSTEM tablespace and have never seen a negative impact on my servers.

If, as you say, you need to reduce the number of extents, then export all the objects from your tablespace, DROP the tablespace and recreate it with larger extents (I still recommend PCTINCREASE of zero). Or export it with compress extents. If you compress extents each table will be put in one large extent when you import it back. Make sure default storage on the tablespace can handle an extent large enough for your largest table. Getting this right has given me some problems in the past.

If you are in Oracle8i, Oracle would prefer that you make the tablespace locally managed. With locally managed tablespaces in 8i this whole discussion is irrelivant.

Best!

Randall Roberts
OCP DBA Kev.- <java2e_at_yahoo.com> wrote in message news:3b01d34b.16300609_at_ns1.nothingbutnet.net...
> No, I believe they are set to 0. I know that 50 or 100 will reduce
> the fragmentation but it is like pulling teeth to get anyone to
> listen.
>
>
> Do you think that because it is 0 that is causing most of the problem?
> I know the with 0 the extents will be uniform in size thus reducing
> wasted space. But again I need to reduce the number of extents.
>
> Thanks.
>
>
> On Wed, 16 May 2001 02:00:59 +0200, Bruno Jargot
> <bjargot_at_club-internet.fr> wrote:
>
> >On Tue, 15 May 2001 21:37:53 GMT, Kev.- wrote:
> >
> >>Hello Howard,
> >>
> >>>Who cares if it's too small? Autoextend is set to on by default (for
> >>>SYSTEM), and so when it needs more space, you get it. You haven't been
 daft
> >>>enough to stop it autoextending, have you? SYSTEM must never run out
 of
> >>>space.
> >>No I am not daft enough to do that. BUt when I query to see the
> >>extents it shows that the system and catalog are well over 300
> >>extents. This just seems like alot of extents to me and it is getting
> >>worse by the day.
> >
> >One question :
> >The pctincrease of the tablespace SYSTEM is 50, isn't it ? And all the
> >segments in the tablespace SYSTEM have a pctincrease of 50 ?
>
Received on Tue May 22 2001 - 19:20:08 CDT