Storage parameter setting for indexes

Storage parameter setting for indexes

Post by Randi W?lne » Fri, 03 Nov 2000 17:05:25



1. How important is it - for performance reasons - to have as few extents as
possible for indexes ?

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?

Thanks in advance!

Best regards,
Randi W?lner

 
 
 

Storage parameter setting for indexes

Post by David Fitzjarrel » Fri, 03 Nov 2000 23:15:26




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

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

Quote:> Thanks in advance!

> Best regards,
> Randi W?lner

--
David Fitzjarrell
Oracle Certified DBA

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Storage parameter setting for indexes

Post by Connor McDonal » Fri, 03 Nov 2000 20:10:20





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

 
 
 

Storage parameter setting for indexes

Post by Randi W?lne » Sat, 04 Nov 2000 16:43:13


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





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

 
 
 

Storage parameter setting for indexes

Post by Howard J. Roger » Sat, 04 Nov 2000 19:54:34



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

- Show quoted text -






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

 
 
 

Storage parameter setting for indexes

Post by Connor McDonal » Sun, 31 Dec 1899 09:00:00





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

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

 
 
 

1. Storage Parameters for Indexes

Hello DBAers,

I'm rebuilding all my Oracle tables with more suitable storage
parameters.  In regards to the storage parameters for indexes, will
they use the storage parameters I specified for the table or do I need
to create separate storage parameters for each?

Thanks in advance,

JGB

2. Excel and vb application

3. Storage parameters of tables and indexes

4. FP2.5W Screen Arrange problem

5. loss of storage parameters for primary key indexes

6. Remote Procedure Calls/API/Protocols

7. storage parameters for primary key indexes

8. Need help !!

9. Calculate table, index (storage parameters)

10. Help needed: Setting parameters in parameter queries

11. STORAGE Parameters

12. Tablespace storage parameters