Oracle Automatic Space Management

• Bitmap Indexes?Oracle 7.3.3
• Locally Managed Tablespaces?Oracle 8.0
• Bitmap segment management?Oracle9i

It?s important to note that bitmap segment management in Oracle9i is optional and can only be implemented at the tablespace level. Existing systems can continue to use the traditional method of freelist management.

Oracle has recently introduced two new tablespace parameters that automate storage management functions:

• Locally Managed Tablespaces (LMT)?The LMT tablespace is implemented by adding the EXTENT MANAGEMENT LOCAL clause to the tablespace definition. LMT tablespaces automate extent management and remove the ability to specify the NEXT storage parameter.
   
• Automatic Segment Space Management (ASSM)?The ASM tablespace is implemented by adding the SEGMENT SPACE MANAGEMENT AUTO clause to the tablespace definition. ASM tablespaces automate freelist management and remove the ability to specify PCTFREE, PCTUSED, FREELISTS, and FREELIST GROUPS storage parameters.  Beware:  Using ASSM can hinder database performance, and most Oracle experts will use manual freelists and freelist groups..

Implementing bitmap freelists (ASSM)

Before I describe the differences between bitmap freelists and traditional space management, let?s examine how bitmap freelists are implemented. We?ll begin by creating a tablespace with the segment space management auto parameter:

create tablespace
   asm_test
datafile
   'c:\oracle\oradata\diogenes\asm_test.dbf'
size
   5m
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
;

Once a table or index is allocated in this tablespace, the values for PCTUSED will be ignored, and Oracle9i will automatically manage the freelists for the tables and indexes inside the tablespace. For objects created in this tablespace, the NEXT extent clause is now obsolete because of the locally managed tablespace. The INITIAL parameter is still required because Oracle can?t know in advance the size of the initial table load. When using Automatic Space Management, the minimum value for INITIAL is three blocks.

There?s 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.

Beware:  Using ASSM can hinder database DML performance, and most Oracle experts will use manual freelists and freelist groups..
 

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 when a row is initially stored small and expanded at a later time. In such systems, it?s not uncommon to set PCTFREE equal to 95, telling Oracle to reserve 95 percent of the data block space for subsequent row expansion.

Sadly, Oracle 9i doesn?t allow you to specify the value for PCTFREE if you?re using automatic space management. This is a serious limitation because Oracle9i can?t know in advance about the amount of VARCHAR expansion in a table row, leading to excessive row chaining and poor access performance.

To see this problem, let?s start by creating a 2-KB tablespace using automatic space management:

create tablespace
   asm_test
datafile
   'c:\oracle\oradata\diogenes\asm_test.dbf'
size
   30m
blocksize
   2k
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
;

We can create a table in this tablespace with an unexpanded VARCHAR2(2000) datatype by entering the following commands. Later, we?ll expand the rows and see if there is fragmentation.
 

create table
   test_frag
(
   tab_key    number,
   big_column varchar2(2000)
)
tablespace
   asm_test
;

We now have a table named test_frag in a 2-KB tablespace. The next step is to populate 4,000 rows, with only a single spec in the VARCHAR2 column:

declare
myint integer := 1;
begin
loop
 
  insert into test_frag
  values
  (
     test_frag_seq.nextval,
    ' '
   );
 
  myint := myint+1;
 
  if myint > 4000 then exit; end if;
 
end loop;
end;
/

Now that we have the rows inserted, let?s take a look at how many rows are stored on the data block in DBA_TABLES:
 

Table                    % Free   NUM_ROWS AVG_ROW_LEN  CHAIN_CNT
-------------------- ---------- ---------- ----------- ----------
TEST_FRAG                    10       4000           9          0

In DBA_SEGMENTS, we see that the table is in a single extent. We also see that we used 32 data block, at 2 KB per block, to store 4,000 rows. This works out to 500 data rows per block.

Table             Tablespace      Buffer                       
name              Name            Pool          Bytes   Blocks  Extents
----------------- --------------- ---------- -------- -------- -----   
TEST_FRAG         ASM_TEST        DEFAULT      65,536       32        1

Now let?s make a mess and expand a large VARCHAR2 column from one byte to 2,000 bytes. After the update, we see in DBA_SEGMENTS that the table is much larger:

Table         Tablespace      Buffer                                 
name          Name            Pool              Bytes   Blocks Extents 
------------- --------------- ---------- ------------ -------- --------
TEST_FRAG     ASM_TEST        DEFAULT       9,437,184    4,608       24 

Now our table is on 4,608 blocks, and the table has taken 24 extents. When we examine DBA_TABLES, we see that the table now has an average row length of 1,378, and every single row has chained!

Table                    % Free   NUM_ROWS AVG_ROW_LEN  CHAIN_CNT
-------------------- ---------- ---------- ----------- ----------
TEST_FRAG                    10       4000        1378       4000

Row chaining is a serious problem for the database administrator (DBA), and it appears that Automatic Space Management is not appropriate for tables where you need to reserve space for large row expansions with PCTFREE.

The issue of PCTUSED

Let?s take a closer look at another Automatic Space Management feature, bitmap freelists. As you probably know, improper settings for PCTUSED can cause huge degradations in the performance of SQL inserts. If a data block isn?t largely empty, excessive I/O will happen during SQL inserts because the reused Oracle data blocks will fill quickly. Taken to the extreme, improper settings for PCTUSED can create a situation in which the free space on the data block is smaller than the average row length for the table. In such cases, Oracle will try five times to fetch a block from the freelist chain. After five attempts, it will raise the high-water mark for the table and grab five fresh data block for the insert.

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

Unlike PCTFREE, where Oracle can?t tell in advance how much row expansion will occur, Oracle9i does have information about the right time to relink a data block. Because Oracle knows the average row length for the table rows (dba_tables.avg_row_len), it should be able to adjust PCTUSED to ensure that the relinked data block will have room for new rows.

This is similar to the setting for PCTUSED on Oracle indexes. Oracle doesn?t allow you to set PCTUSED on an index because it has the ability to tell the optimal time to relink the data block onto the freelist chain. Here?s what happens if you try to specify PCTUSED for an index:

create index
   test_type_idx
on
   book(book_type)
PCTUSED 40;
 
PCTUSED 40
*
ERROR at line 6:
ORA-02143: invalid STORAGE option

In Oracle9i, we see that Oracle allows you to specify PCTFREE or PCTUSED for a table defined inside a tablespace with Automatic Space Management. even though they are ignored:

SQL> create table
  2   test_table
  3   (c1 number)
  4  tablespace
  5   asm_test
  6   pctfree 20 pctused 30
  7  ;
 

However, here?s an important point. Oracle9i does allow you to enter invalid settings for PCTFREE, PCTUSED, NEXT and FREELISTS settings:

SQL> create table
  2   test_table
  3   (c1 number)
  4  tablespace
  5   asm_test
  6  storage
  7   ( freelists 30 next 5m ) ;
 
Table created.

This could be a serious issue for Oracle professionals, unless they remember that locally managed tablespaces with automatic space management ignore any specified values for PCTFREE, PCTUSED, NEXT and FREELISTS.

So any DBA who chooses to implement Automatic Extent Management must be willing to trust Oracle9i to make an intelligent decision about relinking blocks onto the freelists.

No more buffer busy waits

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

Prior to Oracle9i, buffer busy waits were a major issue. A buffer busy wait occurs when a data block is inside the data buffer cache, but it?s unavailable because another SQL insert statement needed to get a block on which to place its row. 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. 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 Data Manipulation Language (DML) operations significantly since different parts of the bitmap can be used simultaneously, eliminating serialization for free space lookups.

Oracle tools for automatic space management

Along with the Automatic Segment Management features, Oracle9i provides several new procedures for automated space management. These include:

• dbms_space.space_usage
• dbms_repair.rebuild_freelists
• Querying the DBA_TABLES view

The most important procedure in the dbms_space package is the space_usage procedure. As you may recall, you can get information directly from DBA_TABLES on block usage.

select
   blocks,
   empty_blocks
from
   dba_tables
where
   table_name='CUSTOMER';

Here we see the total number of blocks and the number of empty blocks with the existing segment extents:

    BLOCKS EMPTY_BLOCKS                     
  ---------- ------------            
          10            3      

The sparse table problem in Oracle 8i and earlier

Sparse tables generally occur when a highly active table 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 doesn?t have any free data blocks.

A sparse table in a data warehouse can use a huge amount of unnecessary storage, consuming many gigabytes of new storage while the table appears to have lots of free space. Remember, with multiple freelists, the freelists are independent and Oracle can?t share freelist blocks. An INSERT task will only attach to one freelist, and it?s only able to use free blocks that are attached to that freelist.

The cause of a sparse table is a lack of balance between INSERT and DELETE activity. In our example, there are three freelists defined for the table, yet a purge job (SQL deletes) ran as a single task. Prior to Oracle9i, the DBA had to parallelize all purge jobs to the value of FREELISTS to ensure that all freelists were evenly populated with empty data blocks.

Prior to Oracle9i, the DBA would have had to reorganize the table using export/import or alter table move  to 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 table and indexes with multiple freelists because the DBA no longer has to reorganize a table to rebalance the bitmap freelists. Here?s an example of using this procedure to rebuild the freelists for the BOOK table:

dbms_repair.rebuild_freelists('PUBS','BOOK');

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

?There are three main types of freelists used to manage segment space; the Master Freelist, the Process Freelist and the Transaction Freelist. Each one controls its own set of datablocks . . . ?

?When using multiple process freelists, the amount of unused space within datablocks can increase. The reason for this is a user process maps onto a particular process freelist using an algorithm (described below), and will not search other process freelists for space if none is found within its own. If a large number of blocks are linked in a particular process freelist, and another user process has no free blocks on its process freelist or no free blocks exist on the master freelist, the process may request movement of the HWM or creation of a new extent. This could leave free blocks on the other process freelist unused.?

Oracle views for bitmap freelists

Oracle 9i also has several v$ and x$ views that display the status of freelists. The transaction freelist is stored inside the X$KVII.KTSMTF view, and the v$waitstat view contains information on bitmap freelists.

select
   class,
   count,
  time
from
   v$waitstat
where
   class like 'bitmap%';

Here we see all system-wide waits associated with bitmap blocks or bitmap index blocks. With the multiple bitmap features, we should seldom see any waits because multiple bitmap freelists are available for concurrent DML.

CLASS                       COUNT    TIME
------------------      ---------- ----------
bitmap block                173        121
bitmap index block          206         43

Limitations of automatic extent management

While automatic extent management simplifies the work of the Oracle DBA, there are several limitations on bitmap segment management in Oracle 9i:

• Once they?re allocated, the DBA has no control over the storage behavior of individual tables and indexes inside the tablespace.
  
• Large objects can?t use automatic segment-space management, and separate tablespaces must be created for tables that contain LOB datatypes.
  
• You can?t create a temporary tablespace with automatic space management. This is because of the transient nature of temporary segments when sorting is performed.
  
• Only locally managed tablespaces can use bitmap segment management.
  
• Row chaining may occur if small rows are expanded, and PCTFREE can no longer be used to control row chaining.