Setting PCTFREE and PCTUSED Based on Average Row Length

Oracle Tips by Burleson Consulting

October, 2008

As
any experience DBA understand, the settings for PCTUSED
can have a dramatic impact on the performance of an
Oracle database. Many new Oracle DBAs fail to realize
that PCTUSED is only used to re-link a full data onto
the table freelist. A re-link only occurs when a DELETE
or UPDATE statement has reduced the free space in the
data block. The setting for PCTUSED will determine the
amount of row space in this newly re-linked data block.

The
default settings for all Oracle tables are PCTUSED=40.
The PCTUSED=40 setting means that a block must become
less than 40 percent full before being re-linked on the
table free list.

Let's
take a closer look at how the PCTUSED operator works and
how it affects the operation of re-links onto the table
freelist. As I said, a data block becomes available for
re-use when a block's free space drops below the value
of PCTUSED for the table.

This
happens when the amount of space in a database block
falls below PCT_USED, and a free list re-link operation
is triggered. For example, with PCTUSED=60, all database
blocks that have less than 60 percent data will be on
the free list, as well as other blocks that dropped
below PCT_USED and have not yet grown to PCT_FREE. Once
a block deletes a row and becomes less than 60 percent
full, the block goes back on the free list.

There
is a direct tradeoff between the setting for PCTUSED and
database performance on insert operations. In general,
the higher the setting for PCTUSED, the less free space
will be on re-used data blocks at INSERT time. Hence,
INSERT tasks will need to do more frequent I/Os than
they would if they were inserting into empty blocks. In
short, the value for PCTUSED should only be set above 40
when the database is short on disk space, and it must
make efficient re-use of data block space.

It
should now be very clear that you need to consider the
average row length when customizing the values for
PCTFREE and PCTUSED. You want to set PCTFREE such that
room is left on each block for row expansion, and you
want to set PCTUSED so that newly-linked blocks have
enough room to accept rows.

Herein
lies the tradeoff between effective space usage and
performance. If you set PCTUSED to a high value, say 80,
then a block will quickly become available to accept new
rows, but it will not have room for a lot of rows before
it becomes logically full again. In the most extreme
case, a re-linked free block may only have enough space
for a single row before causing another I/O.

The
script in Listing 1 (pctused.sql) allows you to adjust
the setting for PCTFREE and PCTUSED as a function of the
number of rows that you want to store between I/Os.

Remember
the rule: The lower the value for PCTUSED, the less I/O
your system will have at insert time, and the faster
your system will run. The downside, of course, is that a
block will be nearly empty before it becomes eligible to
accept new rows.

Because
row length is a major factor in intelligently setting
PCTUSED, a script can be written that allows the DBA to
specifically control how many rows will fit onto a
re-used data block before it unlinks from the freelist.

The
script shown in the listing below generates the table
alteration syntax. Please note that this script only
provides general guidelines, and you will want to leave
the default PCTUSED=40 unless your system is low on disk
space, or unless the average row length is very large.

Note
that the script below allows the DBA to define the
blocksize and the number of rows for which they want to
leave room after the block re-links onto the freelist.

The
file in pctused.lst will now contain the ALTER TABLE
syntax to re-set PCTUSED.

Again,
this script is generally used when the database is very
"tight" on space and the DBA wants to make the
tradeoff between efficient space re-use and INSERT
performance.

All professional DBAs know that minimizing chained rows is
a fundamental job role and they recognize that row migration (chaining) is a
function of:

blocksize

PCFREE

load time average row length

Expected row expansion (bytes per row)

Excessive migrated rows are often the result of a result of
a DBA error, usually by failing to anticipate the future row expansion. An
improper PCTFREE (precipitating a high chain_cnt) is a DBA error, and quite
rare. In my world migrated rows are the result of large objects in small
blocks, and the most common remedy of to deploy a larger blocksize.

��

Burleson is the American Team

Note:This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.

Verify
experience!Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.

Errata? Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just e-mail:
and include the URL for the page.