|
Once you
identify tablespace
fragmentation , what do you do
about it? Honeycomb
fragmentation is easy to fix.
All that needs to be done is
to combine adjacent free
segments into one by issuing a
coalesce statement similar to
this:
alter tablespace USERS
coalesce;
Bubble
fragmentation is more
difficult to handle. Of
course, the best course of
action is to prevent it in the
first place. The best weapon
for this is to use
locally-managed tablespaces.
It may sound too simple, but
in reality, implementing these
storage structures in your
database can just about remove
the need to perform full
tablespace reorganizations.
However,
what do you do if you are in a
situation where you have many
databases that were set up
with dictionary-managed
tablespaces? You have two
options, at least if you are
running Oracle 8i (the version
that gave birth to
locally-managed tablespaces).
First, you can create new
locally-managed tablespaces in
your database and then perform
one last, full tablespace
reorganization of all the
database objects into the new
tablespaces.
Needless
to say, this can be a
difficult task if you have
large databases and no
third-party reorganization
tool. However, it will be
worth the effort as you will
likely never have to
reorganize your tablespaces
again, and your objects
should never again encounter a
maximum extent limit.
If you are
using Oracle 8.1.6 or higher,
you can convert any current
dictionary-managed
tablespaces to
locally-managed tablespaces.
Buried in the Oracle
documentation is a procedure
for converting a tablespace's
extent management from
dictionary to local or
vice-versa.
The
additions to the
sys.dbms_space_admin
package make it quite simple
to convert a tablespace to
locally-managed (or to convert
a locally-managed tablespace
to dictionary- managed if
desired). For example, if you
want to convert a
dictionary-managed tablespace,
called USERS to
locally-managed in Oracle; all
you would have to do is issue
this single command:
sys.dbms_space_admin .tablespace_migrate_to_local('USERS')
If you are afraid of how long
this procedure might take on
large tablespaces, do not be.
It actually runs very fast.
If, for some reason, you would
like to take a tablespace that
is locally-managed back to
dictionary management, you can
issue this command:
sys.dbms_space_admin .tablespace_migrate_from_local('USERS')
There are a few restrictions
on these conversion procedures
(for example, 9i UNDO
tablespaces currently cannot
be converted, etc.), so you
should check the Oracle
documentation for the
specifics of using these new
procedures. Also, note that
converting a
dictionary-managed tablespace
that has existing objects to
local will not magically
rebuild all the existing
object extents to conform to
the sizing guidelines used by
locally-managed tablespaces.
If your
situation precludes the use of
locally-managed tablespaces,
what choices are you left with
to control tablespace
fragmentation ? One thing you
can do is manually mimicking
the mechanisms of
locally-managed tablespaces.
This is done by:
-
Creating a tablespace that
has same-sized extents for
every object's INITIAL and
NEXT extent values
-
Setting the tablespace's
PCTINCREASE property to zero
-
Creating new objects in the
tablespace without
storage properties so they
will inherit the
tablespace's default storage
properties
-
Setting each object's
maximum extent limit to
unlimited
If you're a DBA who's looking
for real world Oracle tuning
techniques, Oracle scripts,
and advice on how to get to
the heart of critical Oracle
performance problems, then
you've come to the right
place. Oracle Performance
Troubleshooting: With
Dictionary Internals SQL &
Tuning Scripts was written
by one the world's most widely-read DBAs
and Oracle internals experts.
Robin Schumacher focuses his
incredible knowledge of the
Oracle data dictionary into a
superb book that shows how to
quickly troubleshoot and
correct Oracle performance
problems.
Plus! The online code depot is
available immediately!
http://www.dba-oracle.com/bp/bp_book5_perf.htm
|
