Re: Storage parameter setting for indexes

Howard J. Rogers wrote:
>
> "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
> >
> >

Interestingly the 8.1.6 doco for dbms_space_admin seems to hint at the fact that oracle 9 will support locally managed system tspaces...so it may be even earlier than 12 ...

:-)

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."