Oracle Table Settings
Oracle Tips by Burleson
It is vital to the performance of an OPS
database that the DBA properly understand and define the multiple
freelist groups that are required by OPS. With Oracle Parallel Server,
internal segment header structures become even more complex. Oracle
provides an additional OPS table parameter called freelist_groups.
With multiple freelist groups, extra segment header blocks are
defined, one for each freelist group that is defined for the table.
For example, assume the following table definition:
CUSTOMER( . . . )
. . .
FREELIST GROUPS 20);
Here we see an OPS table defined with 20 freelist
groups, meaning that the OPS architecture has 20 instances accessing a
single database. Within each freelist group, we see five freelists,
indicating that up to five concurrent tasks may be inserting,
updating, or deleting rows from this table.
Returning to our example, when the customer
table is allocated, Oracle will reserve 20 blocks at the front of the
table for space management purposes. Oracle allocates block 1 for the
segment header, block 2 for freelist group 1, block 3 for freelist
group 2, and so on.
You must define one freelist group for each
instance that will be connecting to the database, and enough freelists
within each group to support the high-water mark for updates and
inserts. However, multiple freelist groups also have a problem
relating to the reuse of space within the freelists for the tables and
indexes. Because each freelist group is an isolated entity, Oracle is
not able to share freelists between the Oracle instances.
Remember, each one of the OPS instances
attaches to a separate freelist group, and free blocks are allocated
independently of one another within these freelist groups.
Most DBAs begin to run into a problem in the
Oracle Parallel Server environment when standard purge routines are
executed. In most large OPS environments, it's not uncommon to
periodically run purges of transaction information. One of the most
common mistakes seen in OPS environments is where the purge routines
are run from only one of the instances. When the purge routines are
run from only a single instance in an OPS configuration, only that
freelist group will receive the empty blocks. The other freelist
groups for the table will not get these blocks, and consequently will
not be able to access these free blocks to use for later inserts into
This is the same kind of sparse table
phenomenon that we discussed in Chapter 10. Again, the remedy for the
sparse table phenomenon is to make sure that you parallelize all of
your deletes to the same degree for which you have parallelized any
insert or update statements. In other words, if you have
three instances doing inserts into the customer table, the customer
purge routines should be parallelized so that one-third of the purge
runs on each one of the three instances. This will keep the freelists
in balance, and will avoid having a largely empty table extending
unnecessarily into new free space.
Now, let's take
a closer look at the Oracle segment header, as shown in Figure 13-4.
Figure 13-112: Segment header details for an OPS
table with two freelist groups
In an Oracle Parallel Server environment with
multiple freelist groups, there are three types of freelists in each
master freelist (aka the common pool)
The master freelist exists for all Oracle segments and
resides in the segment header in the first block of the table. With
OPS, you have a super master freelist on block 1 of the segment in
addition to the master freelists that are dedicated to each freelist
The process freelist (aka multiple
The process freelist is enabled by using the freelist
parameter in the STORAGE clause when creating the table or index. A
process freelist will acquire blocks in two ways. A process freelist
can get blocks when a row delete occurs, or when a
freelist merge occurs. A freelist merge is a process
whereby Oracle moves blocks from the master freelist to the process
freelist, usually in chunks of five blocks.
If the freelist_groups parameter is not used, the process
freelist will exist in the segment block header for the table, right
next to the master freelist. As you know, multiple process freelists
can be defined whenever concurrent updates are expected against the
table. Process freelists greatly help reduce contention for
freelists in a non-OPS environment, since each update process
may read its own freelist. The downside is that each freelist exists
independently of the other freelists, and blocks that appear on one
freelist are not shared with the other freelists.
The transaction freelist (not shown)
The transaction freelist is used by an in-flight
program, which performs delete or insert operations.
Unlike the master and process freelists, transaction freelists are
allocated in the segment header on an as-needed basis. If there is
no room in the segment header for the transaction freelist, the task
will wait and incur a “wait on segment header.” After the task has
ended or committed, the entries are transferred from the transaction
freelist to the process freelist in a FIFO manner, with the most
recently freed blocks being added to the head of the freelist chain.
While having multiple freelists minimizes
contention in regular databases, with OPS a freelist group is used to
minimize contention for space within a shared table that is being
updated by multiple instances.
As of Oracle OPS release 7.3 and beyond, it is
possible to allocate table extents that are “dedicated” to a specific
instance. Oracle8 gives you more control over free space if you
specifically allocate extents to instances, and it has the side
benefit of reducing contention for resources. Consider the following
example where we allocate a data file for the exclusive use of
(size 100m datafile '/oracle/filename' instance 10);
Here we see that the customer table has increased by
100MB, and this additional space is exclusively dedicated to instance
number ten. Automatic table extension will never dedicate rows to an
instance, and the space will go onto the master freelist.
Next, let's get deeper and look at how the OPS
environment is tuned.
This is an excerpt from "Oracle
High Performance tuning with STATSPACK" by Oracle Press.
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning
tips & scripts.
You can buy it direct from the publisher for 30%-off and get instant access to
the code depot of Oracle tuning scripts.