Oracle7 Server Concepts

Data Blocks

Oracle manages the storage space in the datafiles of a database in units called data blocks. A data block is the smallest unit of I/O used by a database.

Data Block Format

The Oracle block format is similar regardless of whether the data block contains table, index, or clustered data. Figure 3 - 2 illustrates the format of a data block.

Figure 3 - 2. Data Block Format

Once the space has been allocated in the row directory of a block's header, this space is not reclaimed when the row is deleted. Therefore, a block that is currently empty but had up to 50 rows at one time continues to have 100 bytes allocated in the header for the row directory. Oracle only reuses this space as new rows are inserted in the block.

Row Data

This portion of the block contains table or index data. Rows can span blocks; see "Row Chaining across Data Blocks"

Free Space

Free space is used for inserting new rows and for updates to rows that require additional space (for example, when a trailing null is updated to a non-null value). Whether issued insertions actually occur in a given data block is a function of the value for the space management parameter PCTFREE and the amount of current free space in that data block. See "An Introduction to PCTFREE, PCTUSED, and Row Chaining"

for more information on space management parameters.

Space Used for Transaction Entries

Data blocks allocated for the data segment of a table, cluster, or the index segment of an index can also use free space for transaction entries. A transaction entry is required in a block for each INSERT, UPDATE, DELETE, and SELECT...FOR UPDATE statement accessing one or more rows in the block. The space required for transaction entries is operating system dependent; however, transaction entries in most operating systems require approximately 23 bytes.

An Introduction to PCTFREE, PCTUSED, and Row Chaining

Two space management parameters, PCTFREE and PCTUSED, allow a developer to control the use of free space for inserts of and updates to the rows in data blocks. You specify these parameters only when creating or altering tables or clusters (data segments). You can also specify the storage parameter PCTFREE when creating or altering indexes (index segments).

The PCTFREE Parameter

The PCTFREE parameter is used to set the percentage of a block to be reserved (kept free) 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 states 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 3 - 3 illustrates PCTFREE.

Figure 3 - 3. 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.

The PCTUSED Parameter

After a data block becomes full, as determined by PCTFREE, Oracle does not consider the block 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 3 - 4 illustrates this.

Figure 3 - 4. PCTUSED

How PCTFREE and PCTUSED Work Together

PCTFREE and PCTUSED work together to optimize the utilization of space in the data blocks of the extents within a data segment. Figure 3 - 5 illustrates how PCTFREE and PCTUSED work together to govern the free space of a data block.

Figure 3 - 5. Maintaining the Free Space of Data Blocks with PCTFREE and PCTUSED

How Oracle Uses PCTFREE and PCTUSED

In a newly allocated data block, the space available for inserts is the block size minus the sum of the block overhead and PCTFREE. Updates to existing data can use any available space in the block; therefore, updates can reduce the available space of a block to less than PCTFREE, the space reserved for updates but not accessible to inserts.

For each data and index segment, Oracle maintains one or more free lists; a free list is a list of data blocks that have been allocated for that segment's extents and have free space greater than PCTFREE; these blocks are available for inserts. When you issue an INSERT statement, Oracle checks a free list of the table for the first available block and uses it if possible; if the free space in that block is not large enough to accommodate the INSERT statement, and it is at least PCTUSED, Oracle takes the block off the free list. Multiple free lists per segment can reduce contention for free lists when concurrent inserts take place.

After you issue DELETE and UPDATE statements, Oracle checks to see if the space being used in the block is less than PCTUSED; if it is, the block goes to the beginning of the free list, and it is the first of the available blocks to be used.

Availability and Compression of Free Space in a Data Block

Two types of statements return space to the free space of one or more data blocks: DELETE statements, and UPDATE statements that update existing values to smaller values. The released space from these types of statements is available for subsequent INSERT statements under the following conditions:

If the INSERT statement is in the same transaction and subsequent to the statement that frees space, the INSERT statement can use the space made available.

If the INSERT statement is in one transaction and the statement that frees space is in a second transaction (perhaps being executed by another user), the INSERT statement can only use the space made available after the second transaction commits, and only if the space is needed.

Released space may or may not be contiguous with the main area of free space in a data block. Oracle coalesces the free space of a data block only when an INSERT or UPDATE statement attempts to use a block that contains enough free space to contain a new row piece, yet the free space is fragmented so that the row piece cannot be inserted in a contiguous section of the block. Oracle does this compression only in such situations so that the performance of a database system is not decreased by the continuous and unnecessary compression of the free space in data blocks as each DELETE or UPDATE statement is issued.

Row Chaining across Data Blocks

In some circumstances, all of the data for a row in a table may not be able to fit in the same data block. When this occurs, Oracle stores the data for the row in a chain of data blocks (one or more) reserved for that segment. Row chaining most often occurs with large rows (for example, rows that contain a column of datatype LONG or LONG RAW).

Note: The format of a row and a row piece are described in "Row Format and Size" .

If a table contains a column of datatype LONG, which can hold up to two gigabytes of information, the data for a row may need to be chained to one or more data blocks. Nothing can be done to avoid this type of row chaining.

If a row in a data block is updated so that the overall row length increases and the block's free space has been completely filled, the data for the entire row is migrated to a new data block, assuming the entire row can fit in a new block. Oracle preserves the original row piece of a migrated row to point to the new block containing the migrated row; the ROWID of a migrated row does not change.

When a row is chained or migrated, I/O performance associated with this row decreases because Oracle must scan more than one data block to retrieve the information for the row. For information about reducing migrated rows and improving I/O performance, see Oracle7 Server Tuning.