Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Oracle automatic segment space management:  ASSM internal structures

Oracle Tips by Burleson Consulting
Jan 13, 2015
, updated November 1, 2007


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 Oracle because it relieves data dictionary contention, unless you experience high volume DML, in which case you can still see buffer busy waits.

Learn more:  See here for ASSM internals and how bitmap freelists compare to manual freelist management and here to see why Oracle ASSM Performance can be bad for high DML applications.

The second major tablespace enhancement in Oracle9i, was automatic segment space management (ASSM). a.k.a. bitmap freelists.   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 blocks, thereby improving segment storage internals.

Note: Beware:   Using ASSM can hinder database DML performance, and most Oracle experts will use manual freelists and freelist groups. However, in 11g release 2 many of the performance bugs in ASSM have been repaired, but it is still important to test ASSM if you use it for tables that experience high-volume DML updates.


Two methods to manage space

Let?s begin by comparing these two new methods of space management, LMT and ASSM:

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

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

From the docs, we see the convoluted rules for extent, storage and freelist management parameters:

If you specified the MINIMUM EXTENT clause, Oracle evaluates whether the values of MINIMUM EXTENT, INITIAL, and NEXT are equal and the value of PCTINCREASE is 0. If so, Oracle creates a locally managed uniform tablespace with extent size = INITIAL. If the MINIMUM EXTENT, INITIAL, and NEXT parameters are not equal, or if PCTINCREASE is not 0, Oracle ignores any extent storage parameters you may specify and creates a locally managed, autoallocated tablespace.

If you did not specify MINIMUM EXTENT clause, Oracle evaluates only whether the storage values of INITIAL and NEXT are equal and PCTINCREASE is 0. If so, the tablespace is locally managed and uniform. Otherwise, the tablespace is locally managed and autoallocated.

When you create a locally managed tablespace using the CREATE TABLESPACE statement, the SEGMENT SPACE MANAGEMENT clause allows you to specify how free and used space within a segment is to be managed. Your choices are:

MANUAL - Specifying MANUAL tells Oracle that you want to use free lists for managing free space within segments. Free lists are lists of data blocks that have space available for inserting rows. This form of managing space within segments is called manual segment-space management because of the need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS storage parameters for schema objects created in the tablespace.

AUTO - This keyword tells Oracle that you want to use bitmaps to manage the free space within segments. A bitmap, in this case, is a map that describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows. As more or less space becomes available in a data block, its new state is reflected in the bitmap. Bitmaps allow Oracle to manage free space more automatically, and thus, this form of space management is called automatic segment-space management.

Automatic segment-space management is a simpler and more efficient way of managing space within a segment. It completely eliminates any need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS storage parameters for schema objects created in the tablespace. If such attributes should be specified, they are ignored.

PCTFREE is still required even with ASSM because it determines how rows will be packed into blocks, while freelists, pctused and pctincrease are ignored with ASSM because they are used for transaction/block management.


If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational