Oracle has done it again. Venture
with me down what seems like a small option but in fact has major implications
on what we, as DBAs no longer have to manage.
The world of database
performance and tuning is changing very fast. Every time I look at new
features, it convinces me more and more that databases are becoming auto-tunable
and self-healing. We could argue for quite awhile that DBAs will or will not
become obsolete in the future, but I think our current nitch is the acceptance
of new technology and our ability to empower the companies we work for by using
it. With Oracle9i, Oracle has given a peek into the future of where it is
going when tuning, not only the database but applications as well. The little
gem that Oracle has snuck in is Its’ new automated segment space management
option.
What Is It
If you haven’t read the
manuals yet, please do. You will quickly realize that Oracle is pushing us to
use locally managed tablespaces. Because all the information to manage
segments and blocks is kept in bitmaps in locally managed tablespaces, the
access to the data dictionary is relieved. Not only does this not generate
redo, contention is reduce. Along with the push to locally managed tablespaces,
is the push to use automatic segment space management. This option takes total
control of the parameters FREELISTS, FREELIST GROUPS, and PCTUSED. That means
that Oracle will track and manage the used and free space in datablocks using
bitmaps for all objects defined in the tablespace for which it has been defined.
How It Use to Be
In the olden days,
everything was dictionary-managed tablespaces. How objects were being used
within tablespaces made setting FREELIST, FREELIST GROUPS, and PCTUSED an
ordeal. Typically, you would sit down and look at the type of DML that was
going to be executed, the number of users executing the DML, the size of rows
in tables, and how the data would grow over time. You would then come up with
an idea of how to set FREELIST, PCTUSED, and PCTFREE in order to get the best
usage of space when weighed against performance of DML. If you didn’t know what
you were doing or even if you did, you constantly had to monitor contention and
space to verify and plan your next attempt. Let’s spend a bit of time getting
accustomed to these parameters.
FREELIST
This is a list of blocks
kept in the segment header that may be used for new rows being inserted into a
table. When an insert is being done, Oracle gets the next block on the freelist
and uses it for the insert. When multiple inserts are requested from multiple
processes, there is the potential for a high level of contention since the
multiple processes will be getting the same block from the freelist, until it
is full, and inserting into it. Depending on how much contention you can live
with, you need to determine how many freelists you need so that the multiple
processes can access their own freelist.
PCTUSED
This is a storage parameter
that states when a certain percentage of a block begin used falls below
PCTUSED, that block should be placed back on the freelist for available
inserts. The issue with using a value for PCTUSED was that you had to balance
the need for performance, a low PCTUSED to keep blocks off the freelist,
against a high PCTUSED to keep space usage under control.
FREELIST GROUPS
Basically used for multiple
instances to access an object. This setting can also be used to move the freelists
to other blocks beside the segment header and thus give some relief to segment
header contention.
Why Is Auto Segment Space Management Good
I have come up with a short
list of reasons why you might want to switch to auto segment space management.
I truly think you can find something that you will like.
- No worries
- No wasted time searching for problems that don’t exist.
- No planning needed for storage parameters
- Out of the box performance for created objects
- No need to monitor levels of insert/update/delete rates
- Improvement in space utilization
- Better performance than most can tune or plan for with concurrent access to objects
- Avoidance of data fragmentation
- Minimal data dictionary access
- Better indicator of the state of a data block
- Further more,
the method that Oracle uses to keep track of the availability of free space in
a block is much more granular than the singular nature of the old, on the
freelist or off the freelist scenario.
Create a Tablespace for Auto Segment Space Management
Creating a tablespace for
Auto Segment Space Management is quite simple. Include the statement at the end
of the CREATE TABLESPACE statement. Here is an example.
CREATE TABLESPACE no_space_worries_ts
DATAFILE ‘/oradata/mysid/datafiles/nospaceworries01.dbf’ SIZE 100M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
The AUTO keyword tells
Oracle to use bitmaps for managing space for segments.
Check What You Have Defined
To determine your current tablespace
definition, query the data dictionary.
select tablespace_name,
contents,
extent_management,
allocation_type,
segment_space_management
from dba_tablespaces;
How Do You Switch To Auto Segment Space Management
Realize that you can’t
change the method of segment space management by an ALTER statement. You must
create a new permanent, locally managed tablespace and state auto segment space
management and then migrate the objects.
Optional Procedures
Oracle has a package called
DBMS_REPAIR that contains a procedure called SEGMENT_FIX_STATUS that will allow
you to fix corruption of the bitmap states. This procedure will recalculate
the bitmap states based on either block contents or a specified value.
The package DBMS_SPACE
contains a procedure called SPACE_USAGE that gives information about how space is
being used within blocks under the segment high water mark.
Let Oracle Take Over
Maybe it’s my old age or
years of doing the mundane tasks as a DBA that wants to embrace this feature.
If there is one thing I have learned from using Oracle databases, it’s that
Oracle has gotten a ton better at making sure new features work and are geared
at truly making database performance better. Here is just one instance where I
think we can embrace Oracles’ attempt to take over a mundane task that is has
been prone to error in the wrong hands. After all, it isn’t rocket science
when you get down to it and will probably be gone in the next release anyway.
