| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Storage parameter setting for indexes
Thanks a lot for your answers which are very helpful, I have an additional question:
In my case I have tables containing from 300k to 1.6 G of data (and a wide spread of values between these). If I choose the uniform extents solution, I would go for an extent size at 1-2M, not to waste too much storage space on the small tables. Then the large tables would consist of a very high number of extents - isn't this a situation I should try to avoid? The situation is the same for indexes - from very small to very large.
Best regards,
Randi Wølner
Connor McDonald <connor_mcdonald_at_yahoo.com> wrote in message
news:3A014B9C.5849_at_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.
>
>
>
 |
 |