Optimizing Oracle Block Space Usage: How Data is stored in a Data Block

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.
The PCTFREE table parameter determines when to perform a freelist un-link. This happens when a block becomes full due to SQL INSERTS and the percent full exceeds the PCTFREE parameter. At this point the block is removed from the table's freelist. If PCTFREE is set to 20, this means that 20% of each data block in this table's data segment will be set aside and free for possible updates to the rows already existing within each block. New rows can be added to the row data area, and corresponding transaction information can be logged in the variable portions of the block overhead area, until the row data and overhead are 80% of the total block size.

When you create a segment, such as a table, Oracle allocates space in the database for the data. If subsequent database operations cause the data volume to increase and exceed the space allocated, then Oracle extends the segment. When installing applications, an initial step is to create all necessary tables and indexes. This operation is by itself relatively fast, and not much is gained by doing it in parallel. However, some things require attention:

1. Using Automatic Segment-Space Management: Oracle automatically manages segment space for best performance.
2. Setting Storage Options: Applications should carefully set storage options for the intended use of the table or index. This includes setting the value for PCTFREE. (Using automatic segment-space management eliminates the need to specify PCTUSED).
   Note: Use of free lists is no longer encouraged. To use automatic segment-space management, create locally managed tablespaces, with the segment space management clause set to AUTO.
3. Setting INITRANS: Each datablock has a number of transaction entries that are used for row locking purposes. Initially, this number is specified by the INITRANS parameter, and the default value (1 for tables, 2 for indexes) is generally sufficient. However, if a table or index is known to have many rows for each block with a high possibility of many concurrent updates, then it is beneficial to set a higher value. This must be done at the CREATE TABLE/CREATE INDEX time to ensure that it is set for all blocks of the object.

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 following series of images.

In a relational database, the concepts of row storage and column storage refer to two different methods of organizing and storing data. Each method has its advantages and disadvantages, depending on the type of queries and operations being performed. Here’s an overview of both:

1. Row Storage (Row-Oriented Storage)
   • Concept:
     • In row storage, data is stored by rows. Each row contains all the information for a single entity or record.
     • Data for a complete row is stored together on disk or in memory.
   • Advantages:
     • Efficient for transactional workloads (OLTP) where the focus is on inserting, updating, and deleting individual records.
     • Accessing complete records is faster because all the data for a record is stored contiguously.
     • Suitable for applications with a high volume of write operations.
   • Disadvantages:
     • Not as efficient for analytical queries (OLAP) that require reading specific columns across many rows, as it may involve reading a lot of irrelevant data.
     • Can result in more storage space used if there are many columns and only a few are accessed frequently.
   • Use Cases:
     • Traditional RDBMS (e.g., MySQL, PostgreSQL)
     • Systems with high-frequency transactional operations
2. Column Storage (Column-Oriented Storage)
   • Concept:
     • In column storage, data is stored by columns. Each column contains the values for that column across all rows.
     • Data for each column is stored together on disk or in memory.
   • Advantages:
     • Efficient for analytical workloads (OLAP) where queries often involve aggregations and operations on a few columns across many rows.
     • Reduces the amount of data read from disk since only the relevant columns are read.
     • Data compression is often more effective because similar data types and values are stored contiguously.
   • Disadvantages:
     • Not as efficient for transactional workloads where operations typically involve entire rows.
     • Write operations can be slower because updating a single row may involve multiple column stores.
   • Use Cases:
     • Analytical databases (e.g., Apache Cassandra, Amazon Redshift)
     • Data warehousing and business intelligence applications

Summary

• Row Storage: Best for OLTP systems where transactions involve full rows; efficient for frequent writes and updates.
• Column Storage: Best for OLAP systems where queries involve specific columns across many rows; efficient for read-heavy operations and data compression.

Choosing the appropriate storage method depends on the specific requirements of your application, such as the type of queries, read/write patterns, and the overall workload.

PCTUSED is a storage parameter in Oracle which specifies when a database block is empty enough for Oracle to add it to the free list. When the percentage of used space in a block is greater than the PCTUSED parameter, Oracle will not add new rows to the block. The default settings for all Oracle tables are PCTUSED=40.

1. PCTUSED is disregarded for all objects created in locally managed tablespaces when Automated Segment Space Management (ASSM) is enabled.
2. PCTUSED is ignored for indexes: Oracle must manage the freelist re-link process for index tree management because the index node boundaries are the same as the index data block size.

Hence, PCTUSED is not used for index segments.

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.

• Data Blocks: 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.