By reserving free space in table spaces you can enable
your data to remain clustered longer between reorganizations and you
can reduce the number of indirect references and overflow records,
which can harm performance.
Before you begin
To determine the amount of
free space that is currently available on a page, run the RUNSTATS
utility and examine the PERCACTIVE column of the SYSIBM.SYSTABLEPART
catalog table.
About this task
When
insufficient free space is available for insert or update operations, Db2 often appends new rows at the
end of the table, out of clustering sequence. When updates to existing
rows mean that they cannot fit on the original page, Db2 creates indirect references to overflow records
on different data pages. When many of these records are physically
located out of sequence, performance suffers.
Consequently,
a sufficient amount of free space can provide the following advantages
during normal processing:
- Data
rows can remain clustered longer after data is reorganized or loaded
because random inserts are not needed. Good clustering can improve
buffer hit ratios and enables more use of dynamic prefetch. Without
clustering, queries must rely on list prefetch for good performance. Data clustering might be less important
if your storage hardware uses newer control units and solid state
disks that can process list prefetch efficiently.
- Indirect
references can be avoided. Indirect references are created
when an update operation increases the size of a row so that it cannot
fit on the original page that contained the row. Db2 stores the row in an overflow page and the
original RID points to the overflow RID. Indirect references only
occur for varying length rows. The most common cause of indirect references
is the use of nullable VARCHAR columns, which initially contain null
values and are later updated with non-null values. Indirect references
are particularly problematic for queries that use random access or
list prefetch. They cause additional CPU cost and more synchronous
I/Os. Db2 cannot use list prefetch
to read overflow records. You can monitor the NEARINDREF and FARINDREF
columns in the SYSIBM.SYSTABLEPART catalog table to find how many
rows have indirect references.
- Fewer data rows are locked by a single page lock, reducing contention
when page-level locking is used.
However, specifying too much free space also has disadvantages,
including:
- More disk space is used for the same amount of data.
- Less information can be transferred by a single I/O operation
- The same amount of data occupies more pages that must be scanned.
- Buffer pools and storage controller cache cannot be used as efficiently
You might not need to reserve any free space in certain situations,
including:
- For read-only objects. If you do not plan to insert or update
data in a table, no free space is needed for the table space.
- The object is not read-only, but inserts are at the end, and updates
that lengthen varying-length columns are few.
Procedure
To improve the use of free space, use the following approaches:
-
Use the PCTFREE clause in most situations.
The PCTFREE
clause specifies the percentage of each data page in a table space
that is left free when loading or reorganizing the data.
For
example, the default value for table spaces PCTFREE 5, which means
that 5% of each data page is kept free when you load or reorganize
the data.
Db2 reserves
the specified amount of free space when data is loaded into the table
or reorganized by utility operations.
Db2 uses
the free space later when you insert or update the data. Regardless
of the specified PCTFREE values, at least one row is always inserted
on each data page.
The value of PCTFREE applies to the
table spaces for hash-organized tables only when you invoke the REORG
TABLESPACE utility and specify the AUTOESTSPACE(YES) option.
-
For tables spaces with heavy update activity (and especially
for compressed data), specify a PCTFREE FOR UPDATE value.
The
FOR UPDATE value specifies the percentage of each page that is reserved
to be used only by future update operations. When you specify FOR
UPDATE -1,
Db2 uses real-time
statistics to automatically calculate how much free space to reserve
for updates.
The default value of FOR UPDATE is set by the value
of the PCTFREE_UPD subsystem parameter.
When
you specify both PCTFREE and FOR UPDATE values, the percentage of
free space reserved by a REORG or LOAD REPLACE operation is the sum
of the two values.
-
If you know the number of rows that fit on a data page at their
maximum size, use the MAXROWS clause to control the number of rows
per page.
However, a MAXROWS value that is too small is
likely to waste disk space, and a MAXROWS value that is too large
is unlikely to prevent indirect references. The MAXROWS clause has
the advantage of maintaining the free space even when new data is
inserted.
-
Use the FREEPAGE clause to specify how often Db2 leaves a full page of free space when loading
data or when reorganizing data.
For example, if you
specify FREEPAGE 10, Db2 leaves
every tenth page free.
Use of the FREEPAGE clause is
most appropriate in the following situations:
- If MAXROWS is 1 or rows are larger than half a page. The PCTFREE
clause has no impact in this case because Db2 cannot insert a second row on a page.
- If additional free space is needed for catalog table spaces and
indexes. The recommendation is to always use the default PCTFREE values
for catalog objects.
-
You might use MAXROWS values to improve concurrency for small
tables and shared table spaces that use page-level locking.
This
approach reduces the number of rows per page, which helps to avoid
lock contention. However, the use of row-level locking is an alternative
to this approach and might be preferred in many situations.