Database Reference
In-Depth Information
The exact effect of these two parameters varies depending on whether you are using ASSM or MSSM tablespaces.
When you are using MSSM, these parameter settings control when the block will be put on and taken off the
FREELIST
. If you are using the default values for
PCTFREE
(10) and
PCTUSED
(40), then a block will remain on the
FREELIST
until it is 90 percent full (10 percent free space). Once it hits 90 percent, it will be taken off the
FREELIST
and
remain off the
FREELIST
until the free space on the block exceeds 60 percent of the block.
When you are using ASSM,
PCTFREE
still limits if a new row may be inserted into a block, but it does not control
whether a block is on a
FREELIST
or not, as ASSM does not use
FREELIST
s at all. In ASSM,
PCTUSED
is simply ignored.
There are three settings for
PCTFREE
: too high, too low, and just about right. If you set
PCTFREE
for blocks too high,
you will waste space. If you set
PCTFREE
to 50 percent and you never update the data, you have just wasted 50 percent
of every block. On another table, however, 50 percent may be very reasonable. If the rows start out small and tend to
double in size, setting
PCTFREE
too small will cause row migration as you update the rows.
Row Migration
What is row migration?
Row migration
is when a row is forced to leave the block it was created on because it grew
too large to fit on that block with the rest of the rows. To illustrate row migration, we start with a block that looks like
Figure
10-3
.
Block Header
Free Space
Row 4 Data
Row 3 Data
Row 2 Data
Free Space
Row 1 Data
Figure 10-3.
Data block before update
Approximately one-seventh of the block is free space. However, we would like to more than double the amount
of space used by row 4 via an
UPDATE
(it currently consumes one-seventh of the block). In this case, even if Oracle
coalesced the space on the block as shown in Figure
10-4
, there is still insufficient room to double the size of row 4,
because the size of the free space is less than the current size of row 4.
