Reducing Segment Header Contention and Buffer Busy Waits

One huge benefit of Automatic Segment Space Management is the number of bitmap freelists that are guaranteed to reduce buffer busy waits X "buffer busy waits" . The following section provides more information on this feature, first introduced in Oracle9i.

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. Whenever any SQL insert ran, it had 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.

According to Oracle benchmarks, using bitmap freelists removes all segment header contention and allows for fast concurrent insert operations as shown in Figure 17.1.

The following section will introduce how the Oracle DBA can use these object management parameters to control every aspect of row storage on the data blocks.

Internal Freelist Management

With ASSM, Oracle controls the number of bitmap freelists, providing up to 23 freelists per segment.

Internally within Oracle, a shortage of freelists is manifested by a buffer busy wait. In traditional non-bitmap freelists, Oracle has a manual mechanism for the DBA to use to allocate a new segment header block with another freelist whenever buffer busy waits are detected for the segment. Oracle first introduced dynamic freelist addition in Oracle8i.

The following section will provide information on how the bitmap freelists of ASSM control free blocks within a segment.

Characteristics Of Bitmap Segment Management

Bitmap space management uses four bits inside each data block header to indicate the amount of available space in the data block. Unlike traditional space management with a fixed relink and unlink threshold, bitmap space managements allow Oracle to compare the actual row space for an insert with the actual available space on the data block. This enables better reuse of the available free space especially for objects with rows of highly varying size. Table 17.1 shows the values inside the four-bit space:

The value column of this bitmap table indicates how much free space exists in a given data block. In traditional space management, each data block must be read from the freelist to see if it has enough space to accept a new row. In Oracle10g, the bitmap is constantly kept up to date with changes to the block and there is also a reduction of wasted space because blocks can be kept fuller because the overhead of freelist processing has been reduced.

Another benefit of ASSM is that concurrent DML operations improve significantly. This is because different parts of the bitmap can be used simultaneously, thereby eliminating the need to serialize free space lookups.

The bitmap segment control structures of ASSM are much larger than traditional one-way linked-list freelist management. Since each data block entry contains the four-byte data block address and the four-bit free space indicator, each data block entry in the space management bitmap will consume approximately six bytes of storage.

It is also important to note that the space management blocks are not required to be the first blocks in the segment. In Oracle8, the segment headers were required to be the first blocks in the segment. In Oracle8i this restriction was lifted, and the DBA could allocate additional freelists with the alter table command, causing additional non-contiguous segment headers. In Oracle10g, Oracle automatically allocates new space management blocks when a new extent is created and maintains internal pointers to the bitmap blocks as shown in Figure 17.2

SEE CODE DEPOT FOR FULL SCRIPTS

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm