Very Computer

> > 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.

> > > 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

> > > > 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

> > 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

> > > > > 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