Oracle automatic segment space management:  ASSM internal structures

• Locally managed tablespace (LMT) vs Dictionary managed (DMT):
  
  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.
  
  In a dictionary managed tablespace (DMT), the data dictionary stores the free space details.  While the free blocks list is managed in the segment heard of each table, inside the tablespace), the Free space is recorded in the sys.uet$ table, while used space in the sys.uet$ table.  But with high DML-rate busy tablespaces the data dictionary became a I/O bottleneck and the, ,movement of the space management out of the data dictionary and into the tablespace have two benefits.  First, the tablespace become independent and can be transportable (transportable tablespaces).  Second, locally managed tablespaces remove the O/O contention away from the SYS tablespace.
  
• Segment size management manual vs segment size management auto.
  
  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 Oracle 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).
 

 

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 Oracle:

• 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 ASSM when using super high-volume DML (e.g., INSERTs, UPDATES, and DELETEs).  Also see MOSC Note: 6781367.8 and Bug 6781367 ALTER TABLE ADD COLUMN or mass UPDATE can be slow in ASSM.

Locally managed tablespaces (LMT) and automatic segment space management (ASSM) provide a new way to manage freelists for individual objects in a database. Along with these ASSM features, Oracle9i provides several new DBMS PL/SQL packages for viewing and managing tablespaces with ASSM. These include:
 

• dbms_space.space_usage
• dbms_repair.rebuild_freelists

Let?s explore how some of these packages are used with ASSM tablespaces.

The sparse table problem in Oracle RAC

Sparse tables generally occur in RAC when non-ASSM tablespaces when a highly active object (e.g., a table or index) is defined with Real Application Clusters (RAC, using multiple freelists) and the table has heavy INSERT and DELETE activity. In a sparse table, the table will appear to have thousands of free blocks, yet the table will continue to extend. And it will behave as if Oracle does not have any free data blocks.

A sparse table in a data warehouse can consume a huge amount of unnecessary storage, consuming many gigabytes of new storage while the table appears to have a lot of free space. Remember, when you have multiple freelists, the freelists are independent and Oracle cannot share freelist blocks. Regardless of whether you are using ASSM with RAC, any INSERT SQL statement will only attach to one freelist and can use only free blocks that are attached to that freelist:

The unbalanced freelist issue dates back to the early days of Oracle HA solutions (OPS), and was problematic for large batch jobs.  Even though freelist blocks are shared across nodes, the node that a batch job attaches to will be the one that gets freed blocks from delete activity.  Conversely, insert jobs attach to only one node, and it?s that node that issues the data block addresses for insert activity.

For example, consider a batch job that inserts 2m rows.  The job attaches to a specific node in the RAC cluster, and uses the freelists.  Mellissa Holman on MOSC notes that the primary cost with using multiple process free lists or multiple free list groups is increased space usage, and this space allocation can become unbalanced.

MOSC note 220970.1, says that freelist contention is an issue in RAC for those shops not using Automatic Segment Space Management (ASSM). 

?a shortage of freelists and freelist groups can cause contention with header blocks of tables and indexes as multiple instances vie for the same block.  This may cause a performance problem and require data partitioning.?

The cause of a sparse table is a lack of load balancing between concurrent INSERT and DELETE activity. In this example, I have three freelists defined for the table (one for each RAC node), yet a purge job (SQL deletes) ran as a single task. Since the delete job attached to only one of the three freelists, all of the deleted blocks are added to that freelist. Prior to Oracle, the DBA would have to parallelize all purge jobs to the value of FREELISTS to ensure that all freelists were evenly populated with empty data blocks.

Also prior to Oracle9i, the DBA would have to reorganize the table using export/import or alter table moveto balance the free blocks on each freelist chain. In Oracle9i and beyind  this is much easier with the dbms_repair.rebuild_freelists procedure. The purpose of the rebuild_freelists procedure is to coalesce bitmap freelist blocks onto the master freelist and zero out all other freelists for the segment. For tables and indexes accessed by real application clusters using multiple freelist groups, Oracle will evenly distribute all free blocks among the existing freelist groups.

This is an important feature for tables and indexes with multiple freelists, because DBAs no longer have to reorganize a table to rebalance the bitmap freelists. Here is an example of this procedure being used to rebuild the freelists for the EMP table:
 
dbms_repair.rebuild_freelists('SCOTT','EMP');
 

Oracle views for ASSM bitmap freelists

Oracle also has several new v$ and x$ views that display the status of ASSM bitmap freelists. The transaction freelist is stored inside the ktsmtf column in the x$kvii fixed table and the v$waitstat view contains information on bitmap freelists. Remember, the freelist structure with ASSM has changed from one-way linked lists to bitmap freelists. In the following example, you see all system-wide waits associated with bitmap blocks or bitmap index blocks:
 
select
   class,
   count,
   time
from
   v$waitstat
where
   class like 'bitmap%';
 

With the multiple bitmap features, you should seldom see any waits because multiple bitmap freelists are available for concurrent DML, as in this example:
 
CLASS             COUNT  TIME
-------------    ------  -----
bitmap block        173  121
bitmap index block  206  43
 

How many DBAs will use ASSM?

It remains to be seen how many experienced DBAs will start using ASSM and how many will continue to use the older method. While ASSM promises faster throughput for multiple DML statements, Oracle professionals must always be on the watch for migrated/chained rows and remember to use PCTFREE when appropriate for each table or index.

References:

MOSC note: 1029850.6: "As can be seen from the algorithms above, using multiple free lists may cause some empty blocks to go unused, causing the segment to extend. If performance is critical, multiple free lists can be used to improve concurrent access, possibly at the expense of additional space used."

This MOSC paper "Free list management in Oracle8i" is also excellent for describing the sharing issue with linked-list (non ASSM) freelists (emphasis added):