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

 

   
 

Tablespace one way freelist internals

Oracle Tips by Burleson 

Oracle Tablespace Design

FREELISTS blocks much like a B-tree index, multiple transactions can simultaneously access free blocks without locking or concurrency problems.

As we have noted, the purpose of bitmap blocks are to track the free blocks in the segment. Since the free blocks are organized in a B-tree, we see the following nodes inside the segment control block. There are three data blocks that comprise the segment control.

The extent control header block contains the following components:

The extent map of the segment

The "last" block at each level of the B-tree

The low high-water mark

The high high-water mark

The high-water mark in the segment header has also changed in Oracle9i bitmap blocks. Instead of having a single pointer to the highest free block in an object, the B-tree index structure allows for a range of high-water mark blocks. Hence, we see two pointers for the high-water mark.

  • The low high-water mark (LHWM) — All blocks below this block have been formatted for the table.
     

  • The high high-water mark (HHWM) — All blocks above this block have not been formatted. Internally, the HHWM is required to ensure that Oracle direct load operations can access contiguous unformatted blocks.

Let's look at each block in detail to understand how space is managed in bitmap segment control. The Extent Control Header Block contains the high high-water mark, the low high-water mark, the extent map, and the data block addresses for each of the three levels of bitmap blocks.

The extent map lists all of the data block address for each block within each extent within the segment and shows the four-bit free space of each block within the extent. Since the extent size is controlled by Oracle9i locally-managed tablespaces, each extent size within the tablespace is uniform, regardless of the NEXT extent size for each object in the tablespace.

Note that the first three blocks of the first extend list (blocks 0 — 2) are used for metadata and are not available for segment block addresses.  For each extent in the segment, Oracle9i keeps an entry pointing to the bitmap for that segment (Figure 5.3).

Figure 5.3: Segment header extent map points to all extent bitmaps in segments.

Oracle9i also has pointers to the last bitmap block within each logical bitmap level (Figure 5.4).


Figure 5.4: Pointers to last bitmap block on each bitmap level.

This new pointer structure allows Oracle9i to quickly access multiple bitmaps to improve concurrency of high-volume INSERTs.


Potential Performance Issues with ASSM

The Oracle9i community has mixed feelings about using ASSM tablespaces. Among the top points about ASSM, we find both Pros and Cons:

Pros of ASSM:

  • Varying row sizes — ASSM is better than a static PCTUSED. The bitmaps make ASSM tablespaces better at handling rows with wide variations in row length.
     

  • Reducing buffer busy waits — ASSM will remove buffer busy waits better than using multiple FREELISTS. As we may know, when a table has multiple FREELISTS, all purges must be parallelized to reload the FREELISTS evenly, and ASSM has no such limitation.
     

  • Great for RAC — The bitmap FREELISTS remove the need to define multiple FREELISTS groups for RAC, and provide overall improved FREELIST management over traditional FREELISTS.


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