RelationalDBDesign 




Data Blocks  «Prev

Internals of Oracle Segment_header_contro

Segment Header Control

Reducing Segment Header Contention

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 Segment header contains a pointer to the high water mark for the table or index.
The segment header contains a pointer to the high water mark for the table or index.

The master freelist (common pool) exists in the segment header in the first block of the table, for all Oracle segments.
The master freelist (common pool) exists in the segment header in the first block of the table, for all Oracle segments.

The internal structure manages the new extents for the object and directs Oracle to the next extent for the table or index
The internal structure manages the new extents for the object and directs Oracle to the next extent for the table or index

This internal structure is enabled by using the FREELIST parameter when creating the table or index
This internal structure is enabled by using the FREELIST parameter when creating the table or index


If FREELIST GROUPS are not used, the process freelist will exist in the segment block header for the table
If FREELIST GROUPS are not used, the process freelist will exist in the segment block header for the table

Process freelists help reduce contention for freelists in the segment header, particularly in a non-OPS environment
Process freelists help reduce contention for freelists in the segment header, particularly in a non-OPS environment

The drawback in a non-OPS environment, is that each process freelist exists independently of the other process free lists, and blocks that appear on one process freelist are not shared with others
The drawback in a non-OPS environment, is that each process freelist exists independently of the other process free lists, and blocks that appear on one process freelist are not shared with others

Transaction freelists are allocated as needed. Transaction entries are transferred to the process freelist
Transaction freelists are allocated as needed. Transaction entries are transferred to the process freelist.