Optimizing Oracle Block Space Usage

High performance often depends on two factors: data block size and row storage. Let us now look at how data is stored within an Oracle data block. Oracle places some overhead in every data block. When a data file is added to an Oracle tablespace, Oracle allocates the data file and formats each data block, adding header and footer information within each block. We can add objects to the remaining space.

Oracle uses all of the row space in a data block, except for the space reserved by the PCTFREE storage management parameter.
To review, the PCTFREE table parameter sets the percentage of space to remain open for future updates of rows that already exist on that data block. PCTFREE can have a negative impact on performance if it is set too low. PCTFREE and PCTUSED work together to optimize the use of space in the data blocks of the extents within a data segment. We will look at setting PCTFREE and PCTUSED later in this module.

Only one object type may inhabit any given Oracle data block. Whenever Oracle allocates an extent for an object (table or index), it is the physical data blocks that are allocated to the object. Therefore a tablespace can have different data blocks with different PCTFREE space, but each data block will contain only one type of object.
Let us take a look at how data blocks are reserved with different amounts of free space.

In the previous module, an overview of tablespaces and the logical structures contained within them were discussed. Also briefly presented were datafiles, allocated at the operating system level, as the building blocks for tablespaces. Being able to effectively manage disk space in the database requires an in-depth knowledge of tablespaces and datafiles, as well as the components of the segments stored within the tablespaces, such as tables and indexes. At the lowest level, a tablespace segment consists of one or more extents, each extent comprising one or more data blocks. Figure 6-1 shows the relationship between segments, extents, and blocks in an Oracle database. In the following sections are the details of data blocks, extents, and segments with the focus on space management.

A data block is the smallest unit of storage in the database. Ideally, an Oracle block is a multiple of the operating system block to ensure efficient I/O operations. The default block size for the database is specified with the DB_BLOCK_SIZE initialization parameter. This block size is used for the SYSTEM, TEMP, and SYSAUX tablespaces at database creation and cannot be changed without re-creating the database.
Every data block contains a header that specifies what kind of data is in the block: table rows or index entries. The table directory section has information about the table with rows in the block; a block can have rows from only one table or entries from only one index, unless the table is a clustered table, in which case the table directory identifies all the tables with rows in this block. The row directory provides details of the specific rows of the table or index entries in the block. The space for the header, table directory, and row directory is a very small percentage of the space allocated for a block; our focus, then, is on the free space and row data within the block.
The next lesson examines how Oracle manages data rows using the Oracle segment header.