Oracle7 Server Administrator's Guide
Managing Space in Data Blocks
This section describes the various aspects of managing space in data blocks, and includes the following topics:
You can use the PCTFREE and PCTUSED parameters to make the following changes:
- increase the performance of writing and retrieving data
- decrease the amount of unused space in data blocks
- decrease the amount of row chaining between data blocks
The PCTFREE Parameter
The PCTFREE parameter is used to set the percentage of a block to be reserved for possible updates to rows that already are contained in that block. For example, assume that you specify the following parameter within a CREATE TABLE statement:
PCTFREE 20
This indicates that 20% of each data block used for this table's data segment will be kept free and available for possible updates to the existing rows already within each block. Figure 10 - 1 illustrates PCTFREE.
Figure 10 - 1. PCTFREE
Notice that before the block reaches PCTFREE, the free space of the data block is filled by both the insertion of new rows and by the growth of the data block header.
Specifying PCTFREE
The default for PCTFREE is 10 percent. You can use any integer between 0 and 99, inclusive, as long as the sum of PCTFREE and PCTUSED does not exceed 100.
A smaller PCTFREE has the following effects:
- reserves less room for updates to expand existing table rows
- allows inserts to fill the block more completely
- may save space, because the total data for a table or index is stored in fewer blocks (more rows or entries per block)
A small PCTFREE might be suitable, for example, for a segment that is rarely changed.
A larger PCTFREE has the following effects:
- reserves more room for future updates to existing table rows
- may require more blocks for the same amount of inserted data (inserting fewer rows per block)
- may improve update performance, because Oracle does not need to chain row pieces as frequently, if ever
A large PCTFREE is suitable, for example, for segments that are frequently updated.
Ensure that you understand the nature of the table or index data before setting PCTFREE. Updates can cause rows to grow. New values might not be the same size as values they replace. If there are many updates in which data values get larger, PCTFREE should be increased. If updates to rows do not affect the total row width, PCTFREE can be low. Your goal is to find a satisfactory tradeoff between densely packed data and good update performance.
PCTFREE for Non-Clustered Tables If the data in the rows of a non-clustered table is likely to increase in size over time, reserve some space for these updates. Otherwise, updated rows are likely to be chained among blocks.
PCTFREE for Clustered Tables The discussion for non-clustered tables also applies to clustered tables. However, if PCTFREE is reached, new rows from any table contained in the same cluster key go into a new data block that is chained to the existing cluster key.
PCTFREE for Indexes You can specify PCTFREE only when initially creating an index.
The PCTUSED Parameter
After a data block becomes full, as determined by PCTFREE, Oracle does not consider the block is for the insertion of new rows until the percentage of the block being used falls below the parameter PCTUSED. Before this value is achieved, Oracle uses the free space of the data block only for updates to rows already contained in the data block. For example, assume that you specify the following parameter within a CREATE TABLE statement:
PCTUSED 40
In this case, a data block used for this table's data segment is not considered for the insertion of any new rows until the amount of used space in the block falls to 39% or less (assuming that the block's used space has previously reached PCTFREE). Figure 10 - 2 illustrates this.
Figure 10 - 2. PCTUSED
Once the free space in a data block reaches PCTFREE, no new rows are inserted in that block until the percentage of space used falls below PCTUSED. The percent value is for the block space available for data after overhead is subtracted from total space.
The default for PCTUSED is 40 percent. You may specify any integer between 0 and 99, inclusive, as long as the sum of PCTUSED and PCTFREE does not exceed 100.
A smaller PCTUSED has the following effects:
- reduces processing costs incurred during UPDATE and DELETE statements for moving a block to the free list when it has fallen below that percentage of usage
- increases the unused space in a database
A larger PCTUSED has the following effects:
- improves space efficiency
- increases processing cost during INSERTs and UPDATEs
Selecting Associated PCTUSED and PCTFREE Values
If you decide not to use the default values for PCTFREE or PCTUSED, keep the following guidelines in mind:
- The sum of PCTFREE and PCTUSED must be equal to or less than 100.
- If the sum equals 100, then Oracle attempts to keep no more than PCTFREE free space, and processing costs are highest.
- The smaller the difference between 100 and the sum of PCTFREE and PCTUSED (as in PCTUSED of 75, PCTFREE of 20), the more efficient space usage is, at some performance cost.
Examples of Choosing PCTFREE and PCTUSED Values
The following examples show how and why specific values for PCTFREE and PCTUSED are specified for tables.
Example 1
Scenario: Common activity includes UPDATE statements that increase the size of the rows.
| Settings: | PCTFREE = 20
PCTUSED = 40 |
| Explanation: | PCTFREE is set to 20 to allow enough room for rows that increase in size as a result of updates. PCTUSED is set to 40 so that less processing is done during high update activity, thus improving performance. |
Example 2
Scenario: Most activity includes INSERT and DELETE statements, and UPDATE statements that do not increase the size of affected rows.
| Settings: | PCTFREE = 5
PCTUSED = 60 |
| Explanation: | PCTFREE is set to 5 because most UPDATE statements do not increase row sizes. PCTUSED is set to 60 so that space freed by DELETE statements is used soon, yet processing is minimized. |
Example 3
Scenario: The table is very large; therefore, storage is a primary concern. Most activity includes read-only transactions.
| Settings: | PCTFREE = 5
PCTUSED = 90 |
| Explanation: | PCTFREE is set to 5 because this is a large table and you want to completely fill each block. |