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 ASSM Performance
Oracle Tips by Burleson Consulting

Performance Issues With ASSM

The Oracle community has mixed feelings about using ASSM tablespaces.

See here for ASSM internals and how bitmap freelists compare to manual freelist management.

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. By high volume, we mean over 30 concurrent DML statements st any given time.


Among the top points about ASSM, there are both pros and cons:

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.

 

Also see MOSC Note: 6781367.8 and Bug 6781367 ALTER TABLE ADD COLUMN or mass UPDATE can be slow in ASSM.
 

The combination of bitmap freelists, ASSM, and Locally Managed Tablespaces has greatly simplified and improved the internal management of data blocks within Oracle tablespaces. The use of bitmap freelists removes the need to define multiple freelists for tables and indexes that experience high volume concurrent DML and provides a simple solution to the problem of segment header contention.

 

However, the savvy DBA recognizes the tradeoff between one-size-fits-all convenience and the power of being able to set individual object parameters for tables and indexes. The choice of LMT and ASSM for tablespace management depends heavily on the application, and real-world Oracle tablespaces will implement LMT and ASSM tablespaces only after careful consideration.

 

The following section examines proactive tablespace management techniques on Oracle10g.

SEE CODE DEPOT FOR FULL SCRIPTS


This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference". 

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

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm


 

 

��  
 
 
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