Subject: Space Management with the Oracle Parallel Server
Last Revision Date: 11 September 1994
Author:Lan Nguyen, Ly
SPACE MANAGEMENT WITH THE ORACLE PARALLEL SERVER
ABSTRACT:
The ORACLE Parallel Server, introduced with ORACLE Version 6.2,
introduces more variables in the already complicated space management
formulas. The algorithms for allocation and location of free space
have been modified. ORACLE Version 6.2 enables transactions running
on separate servers to insert and update data in the same table
concurrently, without contention to locate free space for new records.
Each instance is permitted to have its own set of free lists and space
can be allocated to a table on a per instance basis. A table can be
easily spread across multiple files because now the user can specify
the datafile from which to allocate the next extent. These new
features of the ORACLE Parallel Server will be discussed in detail in
this paper and suggestions will be made on how to use them for optimal
performance.
Space management is an important component of database administration.
A good space management "strategy" consists of forecasting space
requirements as well as anticipating changes in these requirements.
In other words, a Database Administrator (DBA) continuously needs to
determine how large the tablespace must be in order to accommodate
tables and indexes, what should the storage allocation be so that
future expansion or shrinkage can be anticipated, and how would the
size of the database be affected by changes to its structure such as
adding/deleting columns or indexes.
In general, managing space within ORACLE RDBMS has not changed
significantly between Versions 6.0 and 6.2. ORACLE Version 6.2
however, offers in addition a number of key features that if properly
utilized can serve to optimize space-usage and in turn enhance the
database performance. These features becomes particularly important
when multiple instances share a single database. Contention for
resources can be reduced by designing the database to take advantage
of the ORACLE architecture on a shared disk system. This paper
introduces the new concepts of "free lists for database objects",
"allocating log space to instances", and "allocating rollback segments
to instances", and makes suggestions on how to manage database space
to gain optimal performance.
ORACLE Parallel Server Space Management New Features
In Version 6.2, the ORACLE Parallel Server allows each node to run a
complete instance, therefore each node has its own System Global Area
(SGA) and its own set of background processes. All instances running
ORACLE Version 6.2 share access to the same database files, redo log
files, and control files. In a loosely coupled architecture, the
ORACLE RDBMS Version 6.2 provides the following features to control
the use of space by multiple instances, and minimize contention among
instances:
o Free Lists For Tables
For each database object (table, index), the ORACLE RDBMS keeps track
of blocks that are free for INSERTs or UPDATEs. These free blocks are
tracked on what is referred to as "free lists". A user process that
requires free space scans a particular free list for free blocks
before looking in the "common pool of blocks" . The common pool of
blocks is an instance specific "master free list" that contains free
space, including blocks freed by deletions or updates and blocks not
previously used. If both the free list and the master free lists do
not contain blocks with enough space when a user process requires it,
the RDBMS will attempt to use space that has not been used by any
instance before it allocates a new extent to the database object. The
ORACLE RDBMS Version 6.2, allows in addition, "groups of free list" to
be created for tables. Each group of free lists can contain multiple
process free lists. The syntax to create groups of free list and free
lists for a table is shown in the example below:
Example:
CREATE TABLE emp
STORAGE (
initial 50k next 50k pctincrease 0
FREELIST GROUPS 3
FREELISTS 4
)
The above example creates a table called EMP with initial and
next extents of 50 K bytes and percent increase of 0.
Minextent, maxextent, pctused, and pctfree values are used at
default values. The EMP table has 3 groups of free lists.
Each group of free list contains 4 free lists.
The FREELIST GROUPS option specifies the number of groups of
free lists. Each group of free lists is associated with one
or more instances at startup. The default value for FREELIST
GROUPS is 0, which means that the table's free lists are
available to all instances. In general, the value for
FREELIST GROUPS should be the same as the MAXINSTANCE option
of the CREATE DATABASE statement, which indicates the maximum
number of instances that can access a database concurrently.
the FREELISTS option specifies the number of free lists in
each group of free lists. The default is 1, which is also the
minimum value; the maximum depends on the database block size.
The optimal value of FREELISTS depends on the number of
concurrent processes expected to do INSERTs or UPDATEs using a
group of free list for the table.
When creating a table, whether specified zero or one group of free
lists in the FREELIST GROUPS option, the ORACLE RDBMS automatically
allocates a minimum of five ORACLE blocks for the first extent. They
are used for the segment header and a data block. If creating a table
with more than one group of free lists, the ORACLE RDBMS still
allocates five ORACLE blocks for the segment header and a data block,
but an additional allocation of ORACLE blocks which correspond to the
number of groups of free lists is allocated to the first extent. Each
group of free lists and its free lists are kept within one ORACLE
block. For each database object, the segment header keeps information
about the master free list. However, if a table is created without
one or more groups of free lists, then the user processes' free lists
also will be located in the segment header blocks. Therefore, without
groups of free lists, contention is likely to occur when concurrent
INSERTs and UPDATEs requiring new space attempt to search for free
blocks in the user processes' free lists located in the segment header
blocks. Furthermore, contention in the segment header blocks due to
lack of free list groups is even more acute if multiple user processes
are initiated from different instances to modify the same table.
Transactions running on different instances can insert data into the
same table concurrently. Groups of free lists can be created for a
table, using the CREATE TABLE statement (as shown ealier), with each
group containing multiple free lists. Each instance can be assigned
specific groups of free lists so that contention among instances to
locate free space for new records is eliminated. If there are more
instances than groups of free lists, then some or all instances will
share groups (Oracle uses a round robin algorithm to assign instances),
in which case, each free list is shared by user processes from
different instances. This is also true for a table which has multiple
free lists but does not have multiple groups of free lists. Oracle
user process IDs are used to associate user processes with free lists.
Each user process can only access one free list in the group of free
lists assigned to the instance from which the process was initiated.
Every user process however, also has access to the master free list.
The FREELIST GROUPS and FREELISTS storage options can only be used upon
table creation. This means that the users must recreate the table if
they want to change the storage options, since these parameters can
not be changed using the ALTER TABLE statement. The values of
FREELIST GROUPS and FREELISTS are not available from the data
dictionary. These options replace the FREE_LIST_INST and
FREE_LIST_PROC INIT.ORA parameters in Version 6.0.
When a process modifies a block from one instance, it acquires PCM
locks (distributed locks) in order to ensure that data and other
resources among various instances remain consistent. For further
background on PCM locks, read pages 2-6 to 2-11 of the ORACLE RDBMS
Database Administrator's Guide-Addendum Version 6.2. In many cases,
the PCM locks that reserve the blocks in a group of free lists are
usually held primarily by the instance using that group of free lists.
Therefore, contention for database blocks can occur when a number of
instances initiate concurrent processes to insert into the same table.
Concurrent processes inserting into the same table are likely to
contend for database blocks since they are attempting to take the free
space from the same extent. Since an instance that modifies blocks is
usually more likely to reuse those blocks than are other instances,
data partitioning can be used to eliminate contention for database
blocks. In Oracle Version 6.2, groups of free list can be used to
partition data by allocating extents to particular instances using the
ALTER TABLE ... ALLOCATE EXTENT statement (see example below). In
other words, for concurrent INSERTs and UPDATEs requiring new space,
groups of free lists are used to associate instances with different
sets of database blocks in order to eliminate contention and enhance
database performance.
Example:
ALTER TABLE emp
ALLOCATE EXTENT (
SIZE 100 K
DATAFILE 'usr/oracle/dbs/data1.dbs'
INSTANCE 2
)
In the above example, table EMP is altered to allocate an extent of
size 100K in a database file named 'data1.dbs', which is then
assigned to instance number 2.
The SIZE option specifies the extent size in bytes. The ORACLE
RDBMS rounds up the size to a multiple of the block size. If SIZE
is not specified, the extent size is computed according to the
values of the storage parameters NEXT and PCTINCREASE.
The DATAFILE option specifies the database file from which the
extent is to be allocated. The 'filename' must be exactly the
same string as the one stored in the control file, even with
respect to the case of letters. One way to find out the character
string of 'filename' is to look in the DBA_DATA_FILES view.
The INSTANCE option specifies the instance number with which the
group of free lists is associated. Each instance acquires a
unique instance number that maps it to a group of free lists at
startup. The lowest allowable instance number is 1.
Extents are allocated when the table is first created. Every time
extents are allocated, their blocks are automatically added to the
master free list. The ORACLE RDBMS can not reallocate this free space
to any group of free lists. To make the best use of groups of free
lists, automatic extent allocation must be avoided, and the size of
the initial allocation must be minimized. To prevent automatic extent
allocation, the extents must be preallocated, and the MAXEXTENTS
storage option must be set to the current number of extents
(preallocated extents + MINEXTENTS). Initial allocation can be
minimized when creating the table by setting the MINEXTENTS storage
option to 1 (default value), and the INITIAL storage option to the
minimum value (two ORACLE blocks, or 4096 if the block size is 2048).
To reduce database block contention among instances, multiple database
files must be created for each table, and each instance must be
associated with a different file.
In general, effective partitioning of data is achieved by: a) Creating
a separate datafile or tablespace for data that are expected to be
accessed most often by a particular instance; b) Grouping application
specific tables in different tablespaces; and c) Using ALTER TABLE ...
ALLOCATE EXTENT to load data into different extents that correspond
to different instances accessing the same table.
o Free Lists For Clusters
Clusters are groups of tables stored together because they share common
columns and are often used together. Similar to ORACLE Version 6.0,
free lists for clusters are currently not available with ORACLE RDBMS
Version 6.2, since the FREELISTS and FREELIST GROUPS storage options
can not be used with the CREATE CLUSTER statement, nor can the ALLOCATE
EXTENT clause be used with the ALTER CLUSTER statement. Future
releases of the ORACLE RDBMS will allow clusters to use multiple free
lists.
Clusters may improve performance, depending on the SQL operations most
often issued on the data. For example, queries that join more than one
table benefit from the use of clusters, since search and compute time
is reduced because data are stored closer together. However, DML
operations on a single table in the cluster usually does not enhance
performance because multiple tables have data in each block, so more
blocks must be used to store clustered table than if the same table
was stored non-clustered. Therefore, it would take more space to
store clustered tables and a higher number of blocks that the DML
operation must visit to scan a table. Since free lists are not yet an
available feature for clusters in ORACLE Version 6.2, it would be wise
not to cluster tables that are often modified by different instances.
Tables that are going to be clustered should be evaluated closely to
guarantee segment header contention and/or block contention will not
occur.
o Free Lists For Indexes
The ORACLE RDBMS Version 6.2 allows the FREELISTS storage option for
the CREATE INDEX (see example below) statement. Free lists function
and are used in the same manner with indexes as with tables. However,
the FREELIST GROUPS option is not available for the CREATE INDEX
statement, and ALLOCATE EXTENT clause is not available for the ALTER
INDEX statement.
Example:
CREATE INDEX emp_index ON emp
STORAGE (
FREELISTS 4
)
The above example creates an index called EMP_INDEX on table EMP
with 4 free lists. All other storage parameters are left at their
default values.
o Allocating Log Space To Instances
All instances running the ORACLE RDBMS Version 6.2 share the same
database and write to the same online redo log file. Each instance
sharing the database dynamically allocates to itself a range of blocks
in the same online redo log file. The number of blocks that an
instance can allocate to itself is specified by the parameter
LOG_ALLOCATION in the INIT.ORA file. When an instance fills its
current allocation of blocks, it make another allocation. If there is
no more space in the current online redo log file, then all instances
switch to the next log file.
The number of block allocations that should be set in the INIT.ORA file
parameter LOG_ALLOCATION for each instance is totally dependent on:
a) the database activity and the amount of redo generated by that
instance while the redo log file is in use; and b) the size of the
redo log file.
As suggested in the ORACLE RDBMS Database Administrator's
Guide-Addendum Version 6.2, page 2-26 and 2-27:
"The size of each redo log file should be several times larger
than the sum of all LOG_ALLOCATION parameters for all instances
sharing the database. This ensures that each instance can
allocate space in the redo log file when all instances run
concurrently, prevents excessive log switches, and reduces the
average amount of unused space.
Smaller allocation sizes make somewhat better use of log file
space, because they leave less unused space when switching from
one log file to another. Allocations that are too small,
however, can slightly decrease performance. If possible, allow
four to five allocations per redo log file for each instance.
Instances that generate greater amounts of redo data can acquire
more allocations, rather than using larger allocations."
o Allocating Rollback Segments To Instances
The ORACLE RDBMS uses rollback segments to allow transactions to be
"rolled back" prior to a commit and also to provide "read consistency".
Each instance running in parallel mode shares the use of the SYSTEM
rollback segment, but requires at least one dedicated rollback segment.
For this reason, immediately after the creation of the database, one
instance must start up in exclusive mode and create at least one
rollback segment for each instance before all instances can be started
up in parallel mode. All instances can write to the SYSTEM rollback
segment, but only one instance can write to any of the rollback
segments that the instance has acquired. However, all instances can
read from any rollback segment to create read-consistent snapshots or
to perform instance recovery.
Similar to ORACLE Version 6.0, there are two types of rollback
segments in ORACLE Version 6.2: PUBLIC and PRIVATE rollback segments.
PUBLIC rollback segments are held in a pool of rollback segments that
can be claimed by any instance when it is started up. PRIVATE
rollback segments are rollback segments that can only be claimed by an
instance as specified in the INIT.ORA file's ROLLBACK_SEGMENT
parameter. Once a rollback segment has been acquired by an instance,
it is used only by that instance until the instance is shut down. The
number of rollback segments an instance attempts to acquire on start
up is determined by the ratio of TRANSACTIONS and
TRANSACTIONS_PER_ROLLBACK_SEGMENT. This value is rounded to the next
largest integer. When an instance is started up, it first tries to
acquire all private rollback segments specified by the
ROLLBACK_SEGMENTS parameter or the values in ROLLBACK_SEGMENT_INITIAL
and ROLLBACK_SEGMENT_COUNT. If the ratio of TRANSACTIONS and
TRANSACTIONS_PER_ROLLBACK_SEGMENT is greater than the number of the
private rollback segments that the instance has acquired, then the
instance attempts to acquire any of the "available" public rollback
segments for that instance to complete the ratio.
There are no differences in performance between PUBLIC and PRIVATE
rollback segments. The advantage of using public rollback segments is
that they can be acquired by the instance startup immediately after
their creation without having to specify their names in the
ROLLBACK_SEGMENTS parameter. Therefore, it is much easier to do
maintenance for the INIT.ORA file on public rollback segments but the
DBA has minimal control over which instance gets which rollback
segments. On the other hand, there is more work involved in
maintaining the INIT.ORA file using PRIVATE rollback segments but the
DBA has more precise control over rollback segment allocation.
o INIT.ORA Parameters
The ORACLE Version 6.2 introduces a number of new INIT.ORA parameters
which if ultilized correctly will increase performance and give better
control over managing space effectively. The following INIT.ORA
parameters should be considered when running ORACLE Version 6.2 (for
the exact syntax of these parameters, refer to Appendix A of the
ORACLE RDBMS Database Administrator's Guide-Addendum Version 6.2):
GC_FILES_TO_LOCKS
This parameter controls the mapping of blocks from particular
database files to sets of PCM locks. The default value is NULL.
GC_ROLLBACK_SEGMENTS
This parameter sets the system-wide maximum number of rollback
segments, including the SYSTEM rollback segments. In addition,
each rollback segment specified by this parameter also require
one PCM lock. The default value is 20.
GC_ROLLBACK_LOCKS
This parameter sets the number of PCM locks available for each
rollback segment that has simultaneously modified blocks. The
default value is 20.
GC_SEGMENTS
This parameter sets the system-wide maximum number of segments
which may have space management activities performed
simultaneously by different instances. The default value is
10.
GC_TABLESPACES
This parameter sets the maximum number of tablespaces that can
be brought from offline to online (or vice versa) concurrently.
The default value is 5.
Note: ORACLE Version 6.2 does not support taking tablespaces
online or offline while running in parallel mode.
LOG_ALLOCATION
This parameter sets the number of redo log file blocks
allocated to an instance each time it requires more space in
the current online redo log file. The default value is 20 O/S
blocks.
ROLLBACK_SEGMENT_COUNT
This parameter sets the number of rollback segments to allocate
specifically to this instance in a range of consecutive
rollback segments, starting with the segment ID number
specified in ROLLBACK_SEGMENT_INITIAL. The default value is
NULL.
ROLLBACK_SEGMENT_INITIAL
This parameter sets the segment ID number of the first rollback
segment to allocate specifically to this instance in a range of
consecutive rollback segments. The default value is 1 if
ROLLBACK_SEGMENT_COUNT is greater than zero; otherwise NULL.
ROLLBACK_SEGMENTS
This parameter is used to specify the names of one or more
rollback segments to allocate specifically to this instance.
The default value is NULL.
TRANSACTIONS
This parameter sets the maximum number of concurrent
transactions for an instance. The default value is derived
from (1.1 * PROCESSES).
TRANSACTIONS_PER_ROLLBACK_SEGMENT
This parameter sets the number of concurrent transactions
allowed per rollback segment. The default value is 20.
Note: All values for the GC_ parameters above must be identical for
all instances running in parallel mode and they have no effect
on an instance running in exclusive mode.
General Space Management Tips
Similar to ORACLE Version 6.0, DBAs who manage the ORACLE RDBMS
Version 6.2 should always be aware of the activity and growth of their
database in order to utilize space efficiently. The following are
some general suggestions for effective space management strategies:
o Queries can be written to look at the DBA_FREE_SPACE view to find
out if available space is low and if tablespaces are heavily
fragmented.
o The size of the next extent for an object can be computed base on
the values of the NEXT and PCTINCREASE storage options. Sometime
it is useful to know the size of the next extent that the database
is going to allocate in order to prevent problems with running out
of space or not having enough contiguous extents in the tablespace
o The sizes of tables and indexes should be precalculated if the
number of rows and columns are known in advance so that space can
be allocated to prevent the running out of space problem
o Large tables are handled much more easily if created and
maintained in their own dedicated tablespaces. Interfering small
objects can cause serious fragmentation by splitting two adjacent
extents which would otherwise have been available to the large
object.
o The storage parameters of the object should be revised on regular
basis. Some tables may grow fast during a period of time for
which a large pctincrease may be very relevent. In other cases a
large table slows down its growth and does not require as large a
NEXT value as before.
o As the table becomes older, its size generally becomes larger and
the likelyhood of a table export drops as the DBAs find less and
less time on their hands for such an operation. This is a very
important issue. A well thought-out table definition can delay
fragmentation or prevent it altogether whereas a badly maintained
table can easily become too fragmented after a relatively short
period of time. The dba may initially create the object with an
educated guess of how large and fast it is going to grow(or use
the formulae given in the dba guide). But during the initial
period of the object's life, the dba needs to observe the growth
of the object and ideally export it at a convenient stage and
import it using better storage parameters having the tablespace
recreated. If this operation is carefully done at an early stage
in a large object's life, a lot of user and cpu time can be saved
in the future.
o Indexes on large tables should have their own tablespaces and need
as much attention as the tables themselves. Oracle provides
IDXSTAT.SQL script which can be used to create efficient indexes
on existing tables with data.
o Temporary space should never be defaulted to either a tablespace
which contains large objects, nor the SYSTEM tablespace.
o Large objects can be considered as the big animals in the jungle,
they do not like each other and they do not get along with the
smaller ones! Create as many tablespaces as is needed to separate
out such database objects, leaving the SYSTEM tablespace for system
operations, such as the data dictionary and certain installations.
Acknowlegments
I would like to thank Kevin Jernigan, Linda Fong, Moe Fardoost, Saleem
Haque and Vijay Oddiraj for providing valuable information and
assistance.