Re: Storage parameter setting for indexes

"Randi Wølner" <rw_at_computas.no> wrote in message news:l0uM5.1235$Pqb.185120256_at_news.telia.no...
> 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
>

Amazingly enough, the answer is to have a number of tablespaces (both for tables and indexes, separately) that have different extent size settings. Have DATA01 with extents of 500K (say), DATA02 with 2M extents... and so on up to DATAx with 1Gb extents. And if you have DATA01, have an Index01, and an Index02, and an Index0x... and so on (chances are your INDEX extent sizes will be smaller than their corresponding DATA tablespaces'). Segments created within the relevant tablespace will then pick up the correct sort of extent sizes: bingo! Consistent extents = no pctincrease=no need for coalesce=performance benefits=yet no massive amounts of extents either.

If you look around and detect the direction in which the breeze is blowing, you will realise that 8i, with its unform extent sizes in locally managed tablespaces, is kind of suggesting that the days of an assortment of extent sizes within a tablespace are (or should be) a thing of the past -at the very least, they're numbered. I give it until Oracle 12 and data dictionary managed tablespace will be a thing of the past.

Regards
HJR
> 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.
> >
> > 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 Fri Nov 03 2000 - 04:54:34 CST