To keep its position as the most powerful and flexible database, Oracle has been creating new mechanisms to simplify and block storage of tables and indexes over the last few releases. Starting in Oracle8i, Oracle began to automate the management of objects within a tablespace. The first enhancement was called locally managed tablespaces (or LMTs). In an LMT, Oracle moves the tablespace information out of the data dictionary tablespace and stores it directly within the tablespace itself. This has become a de facto standard within Oracle9i because it relieves data dictionary contention.

The second major tablespace enhancement, first appearing in Oracle9i, was automatic segment space management (ASSM). With ASSM, the linked-list freelists are replaced with bitmaps, a binary array that turns out to be very fast and efficient for managing storage extents and free vlocks, thereby improving segment storage internals.

Two methods to manage space
Let’s begin by comparing these two new methods of space management:

  • Locally managed tablespace (LMT)—The LMT is implemented by adding the EXTENT MANAGEMENT LOCAL clause to the tablespace definition syntax. Unlike the older dictionary managed tablespaces (DMTs), LMTs automate extent management and keep the Oracle DBA from being able to specify the NEXT storage parameter to govern extent sizes. The only exception to this rule is when NEXT is used with MINEXTENTS at table creation time.
  • Automatic segment space management (ASSM)—The ASSM tablespace is implemented by adding the SEGMENT SPACE MANAGEMENT AUTO clause to the tablespace definition syntax. ASSM tablespaces automate freelist management by replacing the traditional one-way linked-list freelists with bitmap freelists, and remove the ability to specify PCTUSED, FREELISTS, and FREELIST GROUPS storage parameters for individual tables and indexes.

To Oracle’s credit, both of these space management methods are optional features, and Oracle gurus may still use the more detailed methods should they desire to do so. It is important to note that bitmap segment management in Oracle9i is optional and can only be implemented at the tablespace level. Existing systems may continue to use the traditional method of freelist management.

Bitmap freelists vs. traditional space management
Before I discuss the differences between bitmap freelists and traditional space management, let’s examine how bitmap freelists are implemented. I’ll begin by creating a tablespace with the segment space management auto parameter:
 
create tablespace
   asm_lmt_ts
datafile
   ‘c:\oracle\oradata\diogenes\asm_lmt.dbf’
size
   5m
EXTENT MANAGEMENT LOCAL       — Turn on LMT
SEGMENT SPACE MANAGEMENT AUTO — Turn on ASSM
;
 

Once you’ve defined the tablespace, tables and indexes can easily be moved into the new tablespace with a variety of methods. Here I’ve used create:
 
create table
   new_cust
tablespace
   assm_lmt_ts
as
   select * from customer;
 
alter index cust_name_idx rebuild tablespace assm_lmt_ts;
 

Note that after a table or index is allocated in this tablespace, the values for PCTUSED for individual objects will be ignored and Oracle9i will automatically manage the freelists for the tables and indexes inside the tablespace using bitmap arrays. For tables and indexes created inside an LMT tablespace, the NEXT extent clause is obsolete because the locally managed tablespace manages them. However, the INITIAL parameter is still required because Oracle cannot know in advance the size of the initial table load. For ASSM, the minimum INITIAL value is three blocks.

There is some debate about whether a one-size-fits-all approach is best for Oracle. In large databases, individual object settings can make a huge difference in performance and storage.

The issue of PCTFREE
The PCTFREE parameter is used to specify the amount of free space on a data block to reserve for future row expansion. If PCTFREE is set improperly, SQL update statements can cause a huge amount of row fragmentation and chaining.

The setting for PCTFREE is especially important where a row is initially stored small and expanded at a later time. In such systems, it is not uncommon to set PCTFREE equal to 95, telling Oracle to reserve 95 percent of the data block space for subsequent row expansion.

The issue of PCTUSED
Improper settings for PCTUSED (e.g., set too small) can cause huge degradations in the performance of SQL insert statements. If a data block is not largely empty, excessive I/O will happen during SQL inserts because the reused Oracle data blocks will become full quickly. Taken to the extreme, improper settings for PCTUSED can create a situation where the free space on the data block is smaller than the average row length for the table. In these cases, Oracle will try five times to fetch a block from the freelist chain. After five attempts, Oracle will raise the high-water mark for the table and grab five fresh data blocks for the insert.

In Oracle9i with ASSM, the PCTUSED parameter no longer governs the relink threshold for a table data block, and you must rely on the judgment of Oracle to determine when a block is empty enough to be placed onto the freelist.

While Oracle9i ignores the PCTUSED, FREELISTS, and FREELIST GROUPS parameters with locally managed tablespaces and ASSM, Oracle does not give an error message when they are used in a table definition:
 
SQL> create table
2 test_table
3 (c1 number)
4 tablespace
5 asm_test
6 pctfree 20 pctused 30
7 storage
8 ( freelists 23 next 5m ) ;
Table created.

 

This could be a serious issue unless you remember that locally managed tablespaces with ASSM ignore any specified values for PCTUSED, NEXT, and FREELISTS.

One huge benefit of ASSM is that bitmap freelists are guaranteed to reduce buffer busy waits, which were a serious issue prior to Oracle9i. Let’s take a close look at this feature.

No more buffer busy waits
Without multiple freelists, every Oracle table and index had a single data block at the head of the table to manage free blocks for the object and provide data blocks for new rows created by any SQL insert statements. A buffer busy wait occurs when a data block is inside the data buffer cache but is unavailable because it is locked by another DML transaction.When you want to insert multiple tasks into the same table, the tasks are forced to wait while Oracle assigned free blocks, one at a time.

With ASSM, Oracle 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 A).

Figure A
Oracle corporation benchmark on SQL insert speed with bitmap freelists

Limitations of ASSM
While ASSM appears exciting and simplifies the work of the Oracle DBA, there are several limitations on bitmap segment management in Oracle9i:

  • Once allocated, the DBA has no control over the storage behavior of individual tables and indexes inside the tablespace.
  • Large objects cannot use ASSM, and separate tablespaces must be created for tables that contain LOB datatypes.
  • You cannot create a temporary tablespace with ASSM. This is because of the transient nature of temporary segments when sorting is performed.
  • Only locally managed tablespaces can use bitmap segment management.
  • There may be performance problems with super high-volume DML (e.g., INSERTs, UPDATES, and DELETEs).

In my next installment, I’ll explain how, along with the ASSM features in Oracle9i, you get some new tools for the DBA, and I’ll take a look at how you’ll use these tools along with ASSM.