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.
set heading off;
set pages 9999;
set feedback off;
spool pctused.lst;
define spare_rows = 2;
define blksz = 4096;
select
alter table ||owner||'.'||table_name||
pctused ||least(round(100-((&spare_rows*avg_row_len)/(&blksz/10))),95)||
||
pctfree ||greatest(round((&spare_rows*avg_row_len)/(&blksz/10)),5)||
;'
from
dba_tables
where
avg_row_len > 1
and
avg_row_len < 2000
and
table_name not in
(select table_name from dba_tab_columns b
where
data_type in (RAW','LONG RAW')
)
order by owner, table_name
;
spool off;
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:
- 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.