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 


 

 

 


 

 

 
 


initrans, ITL waits freelists and freelists groups

Oracle Tips by Mike Ault of Burleson Consulting

June 29, 2015


The Automatic Segment Space Management (a.k.a. ASSM or bitmap freelists) is a wonderful features for most shops.  However, using ASSM in some high-DML environments can result in poorer performance, and you will need to manually set the values for INITRANS and FREELISTS (as well as FREELIST GROUPS and PCTFREE and PCTUSED).

Let's examine some brief guidelines for these settings.

Understanding INITRANS:

The INITTRANS setting controls Initial Transaction Slots (ITLs). A transaction slot is required for any session that needs to modify a block in an object. For tables INITRANS defaults to 1 for indexes, 2.

The MAXTRANS setting controls the maximum number of ITLs that a block can allocate (usually defaults to 255). If a block is sparsely populated then Oracle will dynamically increase the number of ITLs up to MAXTRANS.

ITL's and Block Waits

However, if the block has little or no free space then transactions will serialize waiting on a free ITL. This is one cause for data base block waits. By setting INITRANS to the number of expected simultaneous DML (data manipulation language – insert, update and delete) transaction for a single block, you can avoid serialization for ITL slots.

The maximum value suggested for INITRANS is 100 and settings over this size rarely improve performance. Therefore a setting of INITRANS to the average number of simultaneous DML users and setting MAXTRANS to 100 will most likely result in the best utilization of resources and performance. Remember, each ITL requires approximately 23 bytes in the block header.

 

FREELISTS and their cousin FREELIST GROUPS:

A freelist if a one-way linked list (or a bitmap) that identified blocks that can accept data.

FREELIST GROUPS was designed for Oracle Parallel Query (OPQ) and used in Oracle Real Application Clusters (RAC) where many instances need to attach to the same data block.

For example, a setting of FREELISTS 4 and FREELIST GROUPS 2 result in 8 total FREELISTS. Generally speaking FREELIST groups are used in Oracle real application clusters where the setting should be equal to the number of nodes (instances) participating in the cluster. FREELISTS should be set to the number of simultaneous DML users for the table (not the block!), tables default to 1 FREELIST and 0 FREELIST GROUPS.

Again, settings of greater than 100 rarely result in better performance for FREELISTS. Also, tables will, by default, extend based on the minimum allowed extension times the number of FREELISTS so be aware of this when setting FREELISTS.

 
Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.


 

 

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

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster