Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

Oracle Tablespace Design Oracle Tips by Burleson

Artomatic segment space management (bitmap freelists)

2. It is a good method for handling objects with varying row sizes.
    

3. It provides better run-time adjustment for variations in concurrent access and avoids tedious tuning methods.
    

4. It provides better multi-instance behavior in terms of performance/space utilization.

However, note that this automatic feature of segment space management is available only with locally managed tablespaces and their objects. A new column called SEGMENT_SPACE_MANAGEMENT has been added to the dba_tablespaces view to indicate the segment space management mode used by a tablespace.

Use the Oracle procedure dbms_space.space_usage to provide the space usage ratio within each block in the Bitmap Managed Block (BMB) segments. It provides information regarding the number of blocks in a segment with the following range of free space.

0-25% free space within a block
25-50% free space within a block
50-75% free space within a block
75-100% free space within a block

One huge benefit of Automatic Segment Management is the bitmap FREELISTS that are guaranteed to reduce buffer busy waits. Let's take a close look at this feature.

 As a review, a buffer busy wait occurs when a data block is inside the data buffer cache, but it is unavailable because it is locked by another DML transaction. A block was unavailable because another SQL insert statement needed to get a block on which to place its row.

Without multiple bitmap FREELISTS, every Oracle table and index had 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 block and get a data block on which to place its row.

Obviously, single FREELISTS cause a backup. When multiple tasks wanted to insert into the same table, they were forced to wait while Oracle assigned free blocks, one at a time.

Oracle's Automatic Segment Space Management 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 space lookups.

According to Oracle benchmarks, using bitmap FREELISTS removes all segment header contention and allows for super-fast concurrent insert operations (Figure 5.1).

Figure 5.1: Oracle Corporation benchmark on SQL insert speed with bitmap FREELISTS.

Along with the Automatic Segment Management features, we get some new tools for the DBA. Let's take a look at how the Oracle9i DBA will use these tools.

Internal Freelist Management with ASSM

With ASSM, Oracle controls the number of bitmap FREELISTS, up to 23 per segment. Internally within Oracle, a shortage of FREELISTS is manifested by a buffer busy wait. This is because the segment header is available in the data cache, but the block cannot be accessed because another task has locked the block to INSERT, DELETE, or UPDATE a row.

Oracle may have a mechanism to allocate a new segment header block (with another bitmap FREELIST) whenever buffer busy waits are detected for the segment. As we may know, Oracle introduced dynamic FREELIST addition in Oracle8i.

• Freelist Unlinks - While it is possible for Oracle to detect the average row length for segments in a bitmap managed tablespace, Oracle has no way of predicting how much space to reserve of each data block for row expansion. This is because Oracle9i has no knowledge of VARCHAR datatypes that may later be expanded with SQL UPDATE statements. Logic dictates that Oracle must examine the updated row length for every UPDATE and relocate the row if it would chain onto another data block if left on its target block. Row relocation can have a high overhead, especially for batch-oriented SQL updates.
   

• Freelist Re-links - For Oracle to optimize the threshold for re-linking a data block, it needs a priori knowledge of the volume of subsequent INSERT statements. If the threshold is set too high, only a small amount of space is reserved on the re-linked data block, and only a few rows can be INSERTED before Oracle is forced to perform an I/O to grab another data block. Of course, Oracle9i could detect high-volume

Download your Oracle scripts now: www.oracle-script.com The definitive Oracle Script collection for every Oracle professional DBA