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.

Read more about ASSM

For more information on ASSM, read my previous article.

The sparse table problem in Oracle8i
Sparse tables generally occur in non-ASSM tablespaces when a highly active object (e.g., a table or index) is defined with 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, any INSERT SQL statement will only attach to one freelist and can use only free blocks that are attached to that freelist (see Figure A).

Figure A
Unbalanced freelists in Oracle8i

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, 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 Oracle9i, 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. Oracle9i makes this 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, Oracle9i 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’);
 

Oracle9i views for bitmap freelists
Oracle9i 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 chained rows and remember to use PCTFREE when appropriate for each table or index.