Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

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)

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

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

  3. 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

 

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster



Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks