Oracle Segment Header Internals and the Segment header block

When we look at the placement of Oracle data rows within data blocks, we must understand how Oracle uses the segment header to manage data rows. The data block header contains general block information. The segment header block is the first block of any Oracle table or index, and contains control information about free blocks, extents, and high water marks. Let us look at the contents of this block using the MouseOver that follows.

When a database is created, it is divided into multiple logical sections called tablespaces. The SYSTEM tablespace is the first tablespace created, and the SYSAUX tablespace is created as part of every Oracle Database 11g database. You can then create additional tablespaces to hold different types of data (such as tables, indexes, and undo segments). Tablespaces can be renamed by means of the rename to clause of the alter tablespace command.
Note:SYSAUX is created as part of every database starting with Oracle 10 g. It is created automatically if you use the Database Upgrade Assistance provided by Oracle.
When a tablespace is created, datafiles are created to hold its data. These files immediately allocate the space specified during their creation. Each datafile can support only one tablespace. Datafiles can be set to automatically extend when they run out of space.
You can set the increment by which

1. they extend and
2. their maximum size.

Each user's schema is a collection of logical database objects, such as tables and indexes, that refer to physical data structures that are stored in tablespaces. Objects from a user's schema may be stored in multiple tablespaces, and a single tablespace can contain objects from multiple schemas. When a database object (such as a table or index) is created, it is assigned to a tablespace via user defaults or specific instructions. A segment is created in that tablespace to hold the data associated with that object. A segment is made up of sections called extents, which are contiguous sets of Oracle blocks. Once the existing extents can no longer hold new data, the segment will obtain another extent. If a segment is composed of multiple extents, there is no guarantee that those extents will be contiguous. Tablespaces can be created as locally managed (the default) or dictionary managed. Databases have one or more tablespaces, and tablespaces consist of one or more datafiles. Within those tablespaces, Oracle stores segments for database objects and each segment can have multiple extents.

Extent information for a specific object is stored in both

1. the data dictionary and
2. the segment headers.

If a segment grows over a specific number of extents depending on the block size and platform, Oracle will create extent map blocks to keep track of the additional extents. Managing the space used by tablespaces, datafiles, segments, and database objects is one of the basic functions of the DBA. When you create a tablespace, specify the default storage parameters for the tablespace. Every object that is created in that tablespace will then use those storage parameters unless you explicitly override them.

All Oracle tables use freelists to manage free space. One or more freelists are enabled automatically for each data and index segment. These freelists are internal structures that can never be changed, have no parameters, and are never seen by the DBA. Master freelists, transaction freelists, and process freelists are also contained in the segment header.
To understand the role of Oracle's internal structures, we must examine the freelists in more detail. A freelist is a list of the data blocks that are available for inserts, and is a tool for Oracle to manage free data blocks. There is a relationship between the high water mark and the freelists. Before we discuss this, let us look at some other freelist concepts. A freelist can be thought of as a chain of block pointers or a one-way linked list, similar to a data structures linked list. As shown in the graphic below, the segment header points to the first free block, the first free block points to the second free block, and so on.

Oracle uses the following structures to manage free space: the master freelists, super master freelists, freelist groups, process freelists, and transaction freelists. The following series of images below reviews the high water mark, and other segment header internal structures Oracle uses to manage free space.

Segment header contention in Oracle occurs when multiple sessions compete for the same segment header block, leading to performance bottlenecks and potential scalability issues. It is commonly associated with high-concurrency environments, where multiple transactions access the same object, such as a table or index. The following recommendations will help you reduce segment header contention in Oracle:

1. Monitor contention: Use Oracle's Automatic Workload Repository (AWR) reports, Active Session History (ASH) data, or dynamic performance views, such as V$WAITSTAT and V$SESSION_WAIT, to identify segment header contention. Keep an eye on wait events like "buffer busy waits" and "enq: TX - allocate ITL entry" to pinpoint problematic objects.
2. Implement Automatic Segment Space Management (ASSM): ASSM simplifies space management by automatically managing the free and used space within segments. This reduces contention by allowing concurrent transactions to allocate space independently. To enable ASSM, set the TABLESPACE parameter to "AUTO" during tablespace creation.
3. Increase INITRANS parameter: INITRANS determines the initial number of Interested Transaction List (ITL) entries in a data block. By increasing this value, you provide more ITL slots for concurrent transactions, reducing contention. However, do not set the value too high, as it may waste space in each block.
4. Use partitioning: Break large tables or indexes into smaller, more manageable partitions to distribute contention across multiple segment headers. This approach improves performance by allowing Oracle to read or write data from different partitions concurrently.
5. Utilize reverse key indexes: For indexes with monotonically increasing key values, such as sequence-based primary keys, consider using reverse key indexes. This approach redistributes index insertions across all leaf blocks, reducing contention on the right-most index blocks.
6. Use freelist groups: In environments without ASSM, consider using freelist groups to allocate and manage space in segments. Assign each parallel server process to a separate freelist group to minimize contention.
7. Optimize application design: Review your application's transaction processing logic to identify potential bottlenecks and reduce the frequency of concurrent DML operations on the same object. Consider using techniques like batch processing, bulk binds, or optimized SQL statements to minimize contention.
8. Employ appropriate isolation levels: Choose the appropriate transaction isolation level to balance concurrency and consistency requirements. For example, the READ COMMITTED isolation level may help reduce contention by allowing non-blocking reads, while SERIALIZABLE provides a higher level of isolation at the cost of increased contention.

By implementing these strategies, you can effectively reduce segment header contention in Oracle, improving performance, scalability, and overall database efficiency. Regularly monitor your system and adjust these parameters as needed to maintain optimal performance in your specific environment.

One benefit of Automatic Segment Space Management is the number of bitmap freelists that are guaranteed to reduce buffer busy waits. Prior to Oracle9i, buffer busy waits were a major issue for systems with high concurrent inserts. As a review, a buffer busy wait often occurs when a data block is inside the data buffer cache, but it is unavailable because it is locked by another DML transaction. Without multiple freelists, every Oracle table and index has a single data block at the head of the table to manage the free block for the object. After any SQL insert runs, it has to go to this segment header block and get a free data block on which to place its row.
Oracle's ASSM feature claims to improve the performance of concurrent DML operations significantly since different parts of the bitmap can be used, simultaneously eliminating serialization for free block lookups.

The high water mark is increased when a request for a new free block cannot be satisfied by the existing freelists. When this happens, Oracle will increment the master freelist by five blocks.

With the Oracle Parallel Server (OPS), you have a super master freelist on block #1 of the segment. This is in addition to the master freelists that are dedicated to each freelist. We will discuss the OPS in more detail later in the course.

It is important that you understand the relationship between freelists and the high water mark .
All blocks above the high water mark are automatically eligible to receive rows, but all blocks below the high water mark must be on the freelist. When the high water mark is increased, Oracle will move the high water mark up, in increments of five blocks, and add these blocks to the master freelist.

Oracle has several structures for managing free space. These internal structures have an important relationship with PCTFREE and PCTUSED, and the high water mark. Listed below are some of the main points about PCTFREE and PCTUSED. You will learn more about these parameters later in the course:
Each update process reads its own freelist, and blocks only appear on one freelist.
The PCTUSED parameter determines when to perform a freelist re-link.
The PCTFREE parameter determines freelist un-link. The next lesson discusses how to set PCTFREE for optimal performance.
Before moving on to the next lesson, click the link below to read about data block fundamentals.
Data Block Fundamentals