ALERT: Using UNLIMITED Extent Format [ID 50380.1]
--------------------------------------------------------------------------------
修改时间 08-OCT-2010 类型 ALERT 状态 ARCHIVED
Warning about uncontrolled use of 'UNLIMITED' extent format
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
*** Note: This alert was updated on 15-May-2002 to include information
about Oracle8i and Oracle9i.
Versions Affected
~~~~~~~~~~~~~~~~~
The information here effects V7.3 and higher, including Oracle 8.0,
Oracle8i and Oracle9i.
In Oracle8i and higher the problems described here relate mainly
to "dictionary managed" extent allocation. Databases using
"locally managed" tablespaces to store data do not suffer from
the issues described in this alert.
Port Affected/Generic
~~~~~~~~~~~~~~~~~~~~~
Generic
Description
~~~~~~~~~~~
In Oracle 7.3 a new feature was introduced to allow an object to have
more extents than was possible in earlier versions. This feature is very
useful but can cause severe administrative problems unless used with
caution.
The aim of this short note is to highlight some of the problems that can
arise when using this feature. You should read this note if you intend
to increase MAXEXTENTS for any object beyond the default OR if you
want to use TEMPORARY type TEMPORARY tablespaces.
What is Unlimited Extent Format ?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Prior to Oracle 7.3 objects in the database had to consist of no more
than a fixed number of extents dependent on the database block size.
Eg:
For a1K block size an object could have no more than 57 extents
For a2K block size an object could have no more than121 extents
For a4K block size an object could have no more than249 extents
For a8K block size an object could have no more than505 extents
For a 16K block size an object could have no more than 1017 extents
For a 32K block size an object could have no more than 2041 extents
This presented a severe problem if an object grew to the MAXEXTENTS limit
as it had to be rebuilt to fit in fewer extents.
In Oracle7.3 the above limitations were removed and it is now possible
to create an object with many more extents, or to even define an object
as having 'MAXEXTENTS UNLIMITED' in its storage clause. To allow for
this Oracle introduced a new block type which is used when MAXEXTENTS
is greater than the relevant limit above. It is not possible to extend
the system rollback segment beyond the fixed extents limits per block size.
Eg: On a database with a blocksize of 2K Oracle only allows 121 extents
in an object. To allow 122 extents in 7.3 the new block format is used.
This new format is only used if MAXEXTENTS is set ABOVE the 121 limit.
Using UNLIMITED Extent format
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
It is very simple to make use of this new feature:
1. Make sureis set to 7.3.0 or higher
2. Specify a MAXEXTENTS value in the STORAGE clause which is greater
than the previous limit. You can even specify 'MAXEXTENTS UNLIMITED'
but this is not recommended.
OR
3. Create a TEMPORARY tablespace whose contents are of type TEMPORARY
Note:
If you create an object with no MAXEXTENTS clause then the TABLESPACE
'default storage' information is used. This may cause an object to use
UNLIMITED extent format.
Possible Problems
~~~~~~~~~~~~~~~~~
There are several issues with use of the UNLIMITED extents feature
against segments in dictionary managed tablespaces:
1. Changing the storage clause of dictionary tables can cause
serious problems (In particular see Bug:434596 which is fixed in
Oracle 7.3.4):
Bug:434596 prevents a database from being opened if BOOTSTRAP$
has its MAXEXTENTS value modified. Although it is unlikely
anyone would intentionally change this a number of users have run
scripts to change the storage on ALL tables and in doing so have
encountered this problem.
2. MAXEXTENTS UNLIMITED on an object allows unbounded growth of an object:
If an object has MAXEXTENTS UNLIMITED (either set explicitly or from
inheriting a default storage clause) then the object can keep growing
until the tablespace runs out of space. An object can thus contain a
large number of small extents rather than a small number of larger
extents (which is preferable).
This can cause performance problems performing space management
operations such as DROPing the table, TRUNCATING the table etc..
3. MAXEXTENTS UNLIMITED as the default storage on a tablespace:
This can result in objects being created with MAXEXTENTS UNLIMITED
and a small extent size resulting in the same problems as in '2' above.
If the tablespace is used for TEMPORARY segments these can cause
subsequent problems:
a.
SMON can take a long time to clean up these segments
and can consume considerable resource in doing so.
This can cause systems to appear to hang as SMON
holds resources for long periods of time.
b.
Clean shutdown operations can take an excessive length
of time.
4. Each extent requires information in the dictionary:
Each extent allocated on a system requires an entry in the data
dictionary. If a large number of objects have a large number of
(small) extents this can impact the storage used in the SYSTEM
tablespace.
5. Most space management requires the 'ST' enqueue:
Oracle has only a single 'ST' enqueue for space management operations.
Allocating & freeing extents use this enqueue which can become a
point of contention when lots of space management is occurring.
6. Using this feature will prevent you from downgrading.
Earlier releases of Oracle (prior to 7.3) do not understand the
new block format. To downgrade a database to 7.2 after using this
feature all of the new blocks need to be changed back to the old
format so that compatibility can be reset. This may not be
possible without dropping / rebuilding objects.
7. TEMPORARY tablespaces in Oracle 7.3 onwards (including Oracle8/8i):
Sort segments created in TEMPORARY tablespaces are created with
a storage clause which may not be as expected:
INITIAL is taken from the tablespace NEXT default storage value
NEXT is taken from the tablespace NEXT default storage attribute
PCTINCREASE is explicitly ZERO
MAXEXTENTS is explicitly UNLIMITED
8. Rollback segments have an internal maximum of 32767 extents.
Attempting to extend a rollback segment beyond this may result in
internal errors and corruption (See Bug:671491)
9. Tablespaces which currently hold or have previously held many
objects with many extents can take a considerable time to DROP.
ie: DROP TABLESPACE may a long time.
Recommendations
~~~~~~~~~~~~~~~
A. NEVER use 'MAXEXTENTS UNLIMITED' for objects OR default storage clauses
for segments in dictionary managed tablespaces.
A sensible upper limit for MAXEXTENTS is one or two thousand - values
higher than this can impact the performance of DDL operations if
objects are dictionary managed. Eg: DROP TABLE may take a while if
there are several thousand extents in a table.
B. Only use values of MAXEXTENTS above the limits listed above if this
is required. Eg: If an object is at 121 extents with a block size of
2K it may be sensible to increase MAXEXTENTS to 130 along with any
other change to the extent sizing to ensure these extra extents take
a reasonable length of time to fill. If an object quickly fills
additional extents then it may be sensible to review the objects
extent sizes.
C. Never change the storage clause of SYS objects unless the change is
both needed and supported.
D. Use the 'TEMPORARY' tablespace feature in Oracle 7.3 and higher but
make sure you set sensible sizes for the NEXT default storage
attribute of the TEMPORARY tablespace as there is no way to control
MAXEXTENTS of such sort segments. This is the most common cause of
problems: ie: A TEMPORARY tablespace with NEXT set to a small value
such as 10K.
E. Setto FALSE and avoid using
unlimited extent format for rollback segments. Where large numbers
of extents are required on a rollback segment limit MAXEXTENTS to a
couple of thousand.
F. In Oracle8i onwards use LOCALLY MANAGED tablespaces for segments
which must have large numbers of extents.
G. In Oracle8i onwards use TEMPFILE based TEMPORARY tablespaces.
______________________________________________________________________________
