Oracle Parallel Processing

The PCTFREE Parameter: Reserving Free Space

The PCTFREE storage parameter specifies the percentage of each data block that is to be kept free for future updates that might expand the rows stored in that block. This parameter also can be used to control the number of rows that reside in a block. By reducing the number of rows in each block, you may be able to reduce pings on tables that are frequently accessed from many instances.

Let’s use for an example a table that takes up 20 blocks and that is accessed randomly by multiple OPS instances. By increasing the value of the PCTFREE parameter and re-creating the table, you can cause that same data to be spread out over 80 blocks—four times as many as the original number. This reduces the probability of block contention to one-fourth of what it used to be. In essence, you are trading space for performance. This concept is practical for relatively small tables. In an extreme case, for a very “hot” table that cannot be partitioned, you can adjust PCTFREE high enough so that only one row is assigned per block. As long as no two instances are contending for the same row, no pinging will occur.

Assume that your database block size is 16K and that one row of our particular table takes up 200 bytes. You can specify the following STORAGE clause to ensure that each row is written to a block of its own:

STORAGE (INITIAL 128K NEXT 64K PCTINCREASE 0 PCTFREE 98 PCTUSED 2)

As you can see, the PCTFREE parameter in this example requests that each block ...