Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Storage parameter setting for indexes

Re: Storage parameter setting for indexes

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Thu, 02 Nov 2000 19:10:20 +0800
Message-ID: <3A014B9C.5849@yahoo.com>

David Fitzjarrell wrote:
>
> In article <9f9M5.1105$Pqb.180101632_at_news.telia.no>,
> "Randi Wølner" <rw_at_computas.no> wrote:
> > 1. How important is it - for performance reasons - to have as few
 extents as
> > possible for indexes ?
> >
>
> There are advocates on both sides of that question -- on the one hand,
> the fewer fragments you have in an index the better performance should
> become. On the other hand, uniform extent sizing allows Oracle to
> extend the table/index with less work and allows re-use of deallocated
> extents without coalescing the tablespace. Both can improve
> performance, and both can hinder performance. Large extents can hinder
> performance due to Oracle needing large amounts of contiguous space for
> the next extent which it may not find readily. Uniform extents are, as
> the name implies, all the same size eliminating the need for large
> contiguous blocks of free space in a datafile but can create a large
> number of scattered extents for a given table/index resulting in a
> fragmented read of the tablespace to find the desired data.
>
> > 2. I have read somewhere that for tables one should set pctincrease
 to 1
> > (instead of 0, as I would otherwise prefer) - as otherwise coalesce
 would
> > not be done automatically. Is this true for indexes too?
> >
>
> It is not the tables but the tablespaces that will cause SMON to
> coalesce the contiguous free space when the PCTINCREASE is set to 1.
> There are certainly reasons to set the PCTINCREASE on your tablespaces
> to 1, especially on fairly active tablespaces that result in free space
> fragmentation, especially with older releases of Oracle (7.0, 7.1 and,
> I believe, 7.2, that don't have the 'ALTER TABLESPACE ... COALESCE'
> command). However it is not unusual to schedule a job to run several
> times a day, either with cron (on UNIX) or at (with NT), to coalesce
> tablespaces. Since the contiguous free space in tablespaces can be
> coalesced with great ease as compared with early versions of 7 and,
> yes, version 6, I would recommend against setting the tablespace
> PCTINCREASE values to 1. Yes, it is a small increase, and it would
> take quite a while for geometric 1% increase of an initial 1 Megabyte
> extent to actually become a trouble spot in the tablespace but that
> same PCTINCREASE value is also passed on, as the default, to all tables
> created in that tablespace when the table PCTINCREASE value is not
> specified. 1 table an 1% increase is one thing, a large number of
> tables, all at a 1% increase, is another. I would set your tablespaces
> PCTINCREASE to 0 and run a scheduled coalesce during the day if you're
> having trouble with 'fragmented' contiguous free space.
>
> > Thanks in advance!
> >
> > Best regards,
> > Randi Wølner
> >
> >
>
> --
> David Fitzjarrell
> Oracle Certified DBA
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

Enforcing the regime of all a single extent size per tablespace, eliminates the need for either coalesce or pctincrease > 0

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)

"Early to bed and early to rise, 
 makes a man healthy, wealthy and wise." - some dead guy
Received on Thu Nov 02 2000 - 05:10:20 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US