 | |
max extent size in locally managed tablespace with AUTOALLOCATE
comp.databases.oracle.server comp.databases.oracle.server
 |
| | Thread Tools | Display Modes |
#21
 
| |||
| |||
|
Re: max extent size in locally managed tablespace with AUTOALLOCATE -
06-13-2008
, 08:36 AM
|
On 13 jun, 11:31, Vsevolod Afanassiev <vafanass... (AT) yahoo (DOT) com> wrote: What is the maximum extent size possible in locally managed tablespaces (LMT) with autoallocate? Let me clarify the question: I am interested in conventional datafiles (not bigfiles). I understand that "strictly speaking" this question does not have an answer as extent allocation in LMT isn't documented by Oracle and can change between versions/platforms/patches, may depend on block size, datafile size, empty space, etc. So I am looking for "informal" answer applicable in real life. In my tests (done with 8K block size) max extent size was 64M, even for 20 GB table. I saw this value mentioned in several discussions, although Jonathan Lewis mentions 256M: http://www.jlcomp.demon.co.uk/faq/lmt_losses.html Why do I need an answer: we have several databases that were created with 20+ filesystems, 50+ tablespaces, many tablespaces between 1GB and 3 GB, and with random distribution of datafiles between filesystems. Now the filesystems are 98% or so full, AUTOEXTEND has been disabled, and I need to monitor next extent size as extent allocation in an LMT can fail even when tablespace has 15% free space. Thanks I think you need 'uniform' LMT tablespaces. IMHO 'System' LMT's exist only to avoid DBA extra work over personal/ development/nonimportant databases. On a production system you should follow the 'uniform' way. HTH. Cheers. Carlos. |
Yup, definitely. non uniform LMT is kinda dangerous
unless one can afford the time - or the tools - to
constantly monitor for such problems as shown above.
I've got a bell going somewhere that the max extent
size is related to the db block size, but I'll be
buggered if I can place it. Maybe have a look at
one of the "database limits" section in one of the
dba manuals? Can't remember which, though.
#22
| |||
| |||
|
|
On 13 jun, 11:31, Vsevolod Afanassiev <vafanass... (AT) yahoo (DOT) com> wrote: What is the maximum extent size possible in locally managed tablespaces (LMT) with autoallocate? Let me clarify the question: I am interested in conventional datafiles (not bigfiles). I understand that "strictly speaking" this question does not have an answer as extent allocation in LMT isn't documented by Oracle and can change between versions/platforms/patches, may depend on block size, datafile size, empty space, etc. So I am looking for "informal" answer applicable in real life. In my tests (done with 8K block size) max extent size was 64M, even for 20 GB table. I saw this value mentioned in several discussions, although Jonathan Lewis mentions 256M: http://www.jlcomp.demon.co.uk/faq/lmt_losses.html Why do I need an answer: we have several databases that were created with 20+ filesystems, 50+ tablespaces, many tablespaces between 1GB and 3 GB, and with random distribution of datafiles between filesystems. Now the filesystems are 98% or so full, AUTOEXTEND has been disabled, and I need to monitor next extent size as extent allocation in an LMT can fail even when tablespace has 15% free space. Thanks I think you need 'uniform' LMT tablespaces. IMHO 'System' LMT's exist only to avoid DBA extra work over personal/ development/nonimportant databases. On a production system you should follow the 'uniform' way. HTH. Cheers. Carlos. |
Yup, definitely. non uniform LMT is kinda dangerous
unless one can afford the time - or the tools - to
constantly monitor for such problems as shown above.
I've got a bell going somewhere that the max extent
size is related to the db block size, but I'll be
buggered if I can place it. Maybe have a look at
one of the "database limits" section in one of the
dba manuals? Can't remember which, though.
#23
| |||
| |||
|
|
What is the maximum extent size possible in locally managed tablespaces (LMT) with autoallocate? Let me clarify the question: I am interested in conventional datafiles (not bigfiles). I understand that "strictly speaking" this question does not have an answer as extent allocation in LMT isn't documented by Oracle and can change between versions/platforms/patches, may depend on block size, datafile size, empty space, etc. So I am looking for "informal" answer applicable in real life. In my tests (done with 8K block size) max extent size was 64M, even for 20 GB table. I saw this value mentioned in several discussions, although Jonathan Lewis mentions 256M: http://www.jlcomp.demon.co.uk/faq/lmt_losses.html Why do I need an answer: we have several databases that were created with 20+ filesystems, 50+ tablespaces, many tablespaces between 1GB and 3 GB, and with random distribution of datafiles between filesystems. Now the filesystems are 98% or so full, AUTOEXTEND has been disabled, and I need to monitor next extent size as extent allocation in an LMT can fail even when tablespace has 15% free space. Thanks |
256M as Jonathan notes it. Its quite rare that you
might ever see 256M extnet size but that is the limit stated
by Oracle Support also (read in Metalink, Oracle
Support replies in forums .. example being the thread
that Laurent started. Search "LMT autoallocate laurent"
in metalink).
For tablespaces that are prone to the fragmentation
issue you talk about regarding auto allocate, you
might be better off to make them uniform extent
in the first place ... or provide enough free space
to minimize this issue from happening.
Anurag
#24
| |||
| |||
|
|
What is the maximum extent size possible in locally managed tablespaces (LMT) with autoallocate? Let me clarify the question: I am interested in conventional datafiles (not bigfiles). I understand that "strictly speaking" this question does not have an answer as extent allocation in LMT isn't documented by Oracle and can change between versions/platforms/patches, may depend on block size, datafile size, empty space, etc. So I am looking for "informal" answer applicable in real life. In my tests (done with 8K block size) max extent size was 64M, even for 20 GB table. I saw this value mentioned in several discussions, although Jonathan Lewis mentions 256M: http://www.jlcomp.demon.co.uk/faq/lmt_losses.html Why do I need an answer: we have several databases that were created with 20+ filesystems, 50+ tablespaces, many tablespaces between 1GB and 3 GB, and with random distribution of datafiles between filesystems. Now the filesystems are 98% or so full, AUTOEXTEND has been disabled, and I need to monitor next extent size as extent allocation in an LMT can fail even when tablespace has 15% free space. Thanks |
256M as Jonathan notes it. Its quite rare that you
might ever see 256M extnet size but that is the limit stated
by Oracle Support also (read in Metalink, Oracle
Support replies in forums .. example being the thread
that Laurent started. Search "LMT autoallocate laurent"
in metalink).
For tablespaces that are prone to the fragmentation
issue you talk about regarding auto allocate, you
might be better off to make them uniform extent
in the first place ... or provide enough free space
to minimize this issue from happening.
Anurag
#25
| |||
| |||
|
|
What is the maximum extent size possible in locally managed tablespaces (LMT) with autoallocate? Let me clarify the question: I am interested in conventional datafiles (not bigfiles). I understand that "strictly speaking" this question does not have an answer as extent allocation in LMT isn't documented by Oracle and can change between versions/platforms/patches, may depend on block size, datafile size, empty space, etc. So I am looking for "informal" answer applicable in real life. In my tests (done with 8K block size) max extent size was 64M, even for 20 GB table. I saw this value mentioned in several discussions, although Jonathan Lewis mentions 256M: http://www.jlcomp.demon.co.uk/faq/lmt_losses.html Why do I need an answer: we have several databases that were created with 20+ filesystems, 50+ tablespaces, many tablespaces between 1GB and 3 GB, and with random distribution of datafiles between filesystems. Now the filesystems are 98% or so full, AUTOEXTEND has been disabled, and I need to monitor next extent size as extent allocation in an LMT can fail even when tablespace has 15% free space. Thanks |
256M as Jonathan notes it. Its quite rare that you
might ever see 256M extnet size but that is the limit stated
by Oracle Support also (read in Metalink, Oracle
Support replies in forums .. example being the thread
that Laurent started. Search "LMT autoallocate laurent"
in metalink).
For tablespaces that are prone to the fragmentation
issue you talk about regarding auto allocate, you
might be better off to make them uniform extent
in the first place ... or provide enough free space
to minimize this issue from happening.
Anurag
#26
| |||
| |||
|
|
What is the maximum extent size possible in locally managed tablespaces (LMT) with autoallocate? Let me clarify the question: I am interested in conventional datafiles (not bigfiles). I understand that "strictly speaking" this question does not have an answer as extent allocation in LMT isn't documented by Oracle and can change between versions/platforms/patches, may depend on block size, datafile size, empty space, etc. So I am looking for "informal" answer applicable in real life. In my tests (done with 8K block size) max extent size was 64M, even for 20 GB table. I saw this value mentioned in several discussions, although Jonathan Lewis mentions 256M: http://www.jlcomp.demon.co.uk/faq/lmt_losses.html Why do I need an answer: we have several databases that were created with 20+ filesystems, 50+ tablespaces, many tablespaces between 1GB and 3 GB, and with random distribution of datafiles between filesystems. Now the filesystems are 98% or so full, AUTOEXTEND has been disabled, and I need to monitor next extent size as extent allocation in an LMT can fail even when tablespace has 15% free space. Thanks |
256M as Jonathan notes it. Its quite rare that you
might ever see 256M extnet size but that is the limit stated
by Oracle Support also (read in Metalink, Oracle
Support replies in forums .. example being the thread
that Laurent started. Search "LMT autoallocate laurent"
in metalink).
For tablespaces that are prone to the fragmentation
issue you talk about regarding auto allocate, you
might be better off to make them uniform extent
in the first place ... or provide enough free space
to minimize this issue from happening.
Anurag
#27
| |||
| |||
|
|
What is the maximum extent size possible in locally managed tablespaces (LMT) with autoallocate? Let me clarify the question: I am interested in conventional datafiles (not bigfiles). I understand that "strictly speaking" this question does not have an answer as extent allocation in LMT isn't documented by Oracle and can change between versions/platforms/patches, may depend on block size, datafile size, empty space, etc. So I am looking for "informal" answer applicable in real life. In my tests (done with 8K block size) max extent size was 64M, even for 20 GB table. I saw this value mentioned in several discussions, although Jonathan Lewis mentions 256M: http://www.jlcomp.demon.co.uk/faq/lmt_losses.html Why do I need an answer: we have several databases that were created with 20+ filesystems, 50+ tablespaces, many tablespaces between 1GB and 3 GB, and with random distribution of datafiles between filesystems. Now the filesystems are 98% or so full, AUTOEXTEND has been disabled, and I need to monitor next extent size as extent allocation in an LMT can fail even when tablespace has 15% free space. Thanks |
256M as Jonathan notes it. Its quite rare that you
might ever see 256M extnet size but that is the limit stated
by Oracle Support also (read in Metalink, Oracle
Support replies in forums .. example being the thread
that Laurent started. Search "LMT autoallocate laurent"
in metalink).
For tablespaces that are prone to the fragmentation
issue you talk about regarding auto allocate, you
might be better off to make them uniform extent
in the first place ... or provide enough free space
to minimize this issue from happening.
Anurag
#28
| |||
| |||
|
|
What is the maximum extent size possible in locally managed tablespaces (LMT) with autoallocate? Let me clarify the question: I am interested in conventional datafiles (not bigfiles). I understand that "strictly speaking" this question does not have an answer as extent allocation in LMT isn't documented by Oracle and can change between versions/platforms/patches, may depend on block size, datafile size, empty space, etc. So I am looking for "informal" answer applicable in real life. In my tests (done with 8K block size) max extent size was 64M, even for 20 GB table. I saw this value mentioned in several discussions, although Jonathan Lewis mentions 256M: http://www.jlcomp.demon.co.uk/faq/lmt_losses.html Why do I need an answer: we have several databases that were created with 20+ filesystems, 50+ tablespaces, many tablespaces between 1GB and 3 GB, and with random distribution of datafiles between filesystems. Now the filesystems are 98% or so full, AUTOEXTEND has been disabled, and I need to monitor next extent size as extent allocation in an LMT can fail even when tablespace has 15% free space. Thanks |
256M as Jonathan notes it. Its quite rare that you
might ever see 256M extnet size but that is the limit stated
by Oracle Support also (read in Metalink, Oracle
Support replies in forums .. example being the thread
that Laurent started. Search "LMT autoallocate laurent"
in metalink).
For tablespaces that are prone to the fragmentation
issue you talk about regarding auto allocate, you
might be better off to make them uniform extent
in the first place ... or provide enough free space
to minimize this issue from happening.
Anurag
#29
| |||
| |||
|
|
What is the maximum extent size possible in locally managed tablespaces (LMT) with autoallocate? Let me clarify the question: I am interested in conventional datafiles (not bigfiles). I understand that "strictly speaking" this question does not have an answer as extent allocation in LMT isn't documented by Oracle and can change between versions/platforms/patches, may depend on block size, datafile size, empty space, etc. So I am looking for "informal" answer applicable in real life. In my tests (done with 8K block size) max extent size was 64M, even for 20 GB table. I saw this value mentioned in several discussions, although Jonathan Lewis mentions 256M: http://www.jlcomp.demon.co.uk/faq/lmt_losses.html Why do I need an answer: we have several databases that were created with 20+ filesystems, 50+ tablespaces, many tablespaces between 1GB and 3 GB, and with random distribution of datafiles between filesystems. Now the filesystems are 98% or so full, AUTOEXTEND has been disabled, and I need to monitor next extent size as extent allocation in an LMT can fail even when tablespace has 15% free space. Thanks |
256M as Jonathan notes it. Its quite rare that you
might ever see 256M extnet size but that is the limit stated
by Oracle Support also (read in Metalink, Oracle
Support replies in forums .. example being the thread
that Laurent started. Search "LMT autoallocate laurent"
in metalink).
For tablespaces that are prone to the fragmentation
issue you talk about regarding auto allocate, you
might be better off to make them uniform extent
in the first place ... or provide enough free space
to minimize this issue from happening.
Anurag
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.