ZT: ASSM, freelists and PCTFREE

 
Concepts I  — Oracle storage logical units
==============================
Block: the finest level of granularity is a block (also called an Oracle block, a logical block or a page) which is a multiple of a physical    
         block. More than often, its size is 8k;
Extent: a number of blocks make up an extent;
Segment: a number of extents consist of a segment
Tablespace: a number of segments compose a tablespace. Tablespace is built on phsical files, such as user.mdf
 
Concepts II — Data in memory
=======================
All data are writtem into a memory area called DBC — data buffer cashe. Oracle achieves amzing performace because it manages to get the right data into the memory efficiently to take advantage of computer memory’s process speed which is fater than disk in manitutudes. 
Oracle writes data into the DBC in multiple of blocks (buffers).
 
One then may ask how Oracle knows which buffer (block) is free and which is not to write new data into it?  And the following article presents an interseting review in answering this questions.
 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 

Oracle introduced Automatic Segment Storage Management (ASSM) as a replacement for traditional freelists management which used one-way linked-lists to manage free blocks with tables and indexes.  ASSM is commonly called "bitmap freelists" because that is how Oracle implement the internal data structures for free block management.  Do not confuse ASSM (bitmap freelists) with Automatic Storage Management (ASM). 

As of Oracle 10g, BC does not always recommend implementing bitmap freelists for high DML tables because there is a tradeoff between reduced buffer busy waits and DML contention during high update activity.

Note: As of 11g release 2, many of the performance bugs in ASSM have been repaired.  However, it is still important to test ASSM if you use it for tables that experience high-volume DML updates.

The bitmap freelists of ASSM greatly reduce segment header contention and improve simultaneous insert concurrency (up to a point).  ASSM also removes the need to specify freelist groups in RAC.  The new dbms_space procedures allow the DBA to see growth trends within specific objects, and ASSM provides better multi-instance RAC behavior in terms of performance/space utilization.

Simpler object management with ASSM

ASSM provides administrative ease of use by avoiding the specification of storage parameters and ASSM is a very efficient method for handling objects with varying row sizes. Lastly, ASSM provides better run-time adjustment for variations in concurrent access and avoids tedious tuning methods.
 

Why use ASSM?

Automatic segment space management (ASSM) 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 freelist groups storage parameters for schema objects created in the tablespace. If any of these attributes are specified, they are ignored.  ASSM is not for every database, especially those with super-high DML rates:

Pros of ASSM:

Varying row sizes: ASSM is better than a static pctused. The bitmaps make ASSM tablespaces better at handling rows with wide variations in row length.

Reducing buffer busy waits: ASSM will remove buffer busy waits better than using multiple freelists. When a table has multiple freelists, all purges must be parallelized to reload the freelists evenly, and ASSM has no such limitation.

Great for Real Application Clusters: The bitmap freelists remove the need to define multiple freelists groups for RAC and provide overall improved freelist management over traditional freelists.

Cons of ASSM:

Slow for full-table scans: Several studies have shown that large-table full-table scans (FTS) will run longer with ASSM than standard bitmaps. ASSM FTS tablespaces are consistently slower than freelist FTS operations. This implies that ASSM may not be appropriate for decision support systems and warehouse applications unless partitioning is used with Oracle Parallel Query.

Slower for high-volume concurrent inserts: Numerous experts have conducted studies that show that tables with high volume bulk loads perform faster with traditional multiple freelists.

ASSM will influence index clustering: For row ordered tables, ASSM can adversely affect the clustering_factor for indexes. Bitmap freelists are less likely to place adjacent tows on physically adjacent data blocks, and this can lower the clustering_factor and the cost-based optimizer’s propensity to favor an index range scan.

According to Oracle benchmarks, using bitmap FREELISTS removes all segment header contention and allows for super-fast concurrent insert operations:

For each extent in the segment, Oracle ASSM keeps an entry pointing to the bitmap for that segment:

 

ASSM Segment header extent map points to all extent bitmaps in segments.

Oracle ASSM also has pointers to the last bitmap block within each logical bitmap level:

ASSM has 6 different status flags for table blocks:

0 = unformatted
1 = logically full (per pctfree)
2 = 0-25% free
3 = 25-50% free
4 = 50%-75% free
5= 75-100% free

As rows are inserted, ASSM updates his 搇evel 1 bitmap block?when the new free space crosses one of these six thresholds. It抯 also important to note that blocks are marked as full as soon as the last insert/update crosses the PCTFREE threshold, the point at which ASSM un-links the data block from the freelist chain.

For example, assume the default PCTFREE=20, which specifies an freelist unlink when the block is 80% full. Whether an insert takes the 搑eal?free space to 81% or 99%, Oracle marks the block as full, without regard to the amount of fullness.

Hence you cannot assume that the real free space kept in reserve for row expansion is 20%; it depends on several factors, most notably the block size and average row length for the table.

Adjusting the behavior of ASSM (guru’s only)

According to Oracle expert Tanel Poder, there are some undocumented hidden parameters in ASSM to control it’s behavior: (Note: Never change hidden parameters without the consent of Oracle Technical Support):
_walk_insert_treshold (default 5)
  Freelist blocks to scan before turning to higher level list or bump HWM (if walking on TFL, PFL and MFL are searched next)

_release_insert_threshold (default 5)
  How many unsuitable blocks to unlink from freelist before bump HWM

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s