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

 
 Home
 E-mail Us
 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 INITRANS tips

Oracle Tips by Burleson Consulting

Eliminating data block contention involves eliminating ?hot? data  blocks from the application, and super-high concurrent DML can cause block contention that will be relieved by increasing INITRANS for the effected tables and indexes.  Since each transaction updating a block requires a transaction entry, you can also increase the INITRANS value for high volume databases.

Use a tool (like Ion) to see the specific table of index that has the contention, that's a clue. Also see this article to tell you how to find the specific table or index, and consider increasing INITRANS for that object.

All about INITRANS

The INITRANS 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, INITRANS 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.

The v$segment_statistics view provides a statistic_name and value column for each table, unfortunately this format doesn?t lend itself to easy use. By utilizing the crosstab technique we can easily create a report to show us these vital tuning statistics for our system. An example of this type of cross tab report is shown below.

col "Object" format a20

set numwidth 12

set lines 132

set pages 50

@title132 'Object Wait Statistics'

spool rep_out\&&db\obj_stat_xtab

 

select * from

(

   select

      DECODE

      (GROUPING(a.object_name), 1, 'All Objects', a.object_name)

   AS "Object",

sum(case when

   a.statistic_name = 'ITL waits'

then

   a.value else null end) "ITL Waits",

sum(case when

   a.statistic_name = 'buffer busy waits'

then

   a.value else null end) "Buffer Busy Waits",

sum(case when

   a.statistic_name = 'row lock waits'

then

   a.value else null end) "Row Lock Waits",

sum(case when

   a.statistic_name = 'physical reads'

then

   a.value else null end) "Physical Reads",

sum(case when

   a.statistic_name = 'logical reads'

then

   a.value else null end) "Logical Reads"

from

   v$segment_statistics a

SEE CODE DEPOT FOR FULL SCRIPTS

/

spool off

clear columns

ttitle off


The cross tab report generates a listing showing the statistics of concern as headers across the page rather than listings going down the page and summarizes them by object. This allows us to easily compare total buffer busy waits to the number of ITL or row lock waits. This ability to compare the ITL and row lock waits to buffer busy waits lets us see what objects may be experiencing contention for ITL lists, which may be experiencing excessive locking activity and through comparisons, which are highly contended for without the row lock or ITL waits. AN example of the output of the report, edited for length, is shown below.

Object        ITL Waits      Busy Waits Row Lock Waits Physical Reads Logical Reads   

------------- --------- ----------------- -------------- -------------- -------------    

BILLING                0             63636          38267        1316055     410219712   

BILLING_INDX1          1             16510             55         151085      21776800   

...

DELIVER_INDX1       1963             36096          32962        1952600      60809744   

DELIVER_INDX2         88             16250           9029       18839481     342857488  

DELIVER_PK          2676             99748          29293       15256214     416206384 

...

All Objects        12613          20348859        1253057     1139977207   20947864752   

In the above report the BILLING_INDX1 index has a large amount of buffer busy waits but we can?t account for them from the ITL or Row lock waits, this indicates that the index is being constantly read and the blocks then aged out of memory forcing waits as they are re-read for the next process. On the other hand, almost all of the buffer busy waits for the DELIVER_INDX1 index can be attributed to ITL and Row Lock waits. In situations where there are large numbers of ITL waits we need to consider the increase of the INITRANS setting for the table to remove this source of contention.

ORA-00060 and INITRANS shortage

There can also be a ORA-00060 "deadlock detected" where the table and index INITRANS is set too low.  The "Interested Transaction List" and deadlocks caused by an ITL-shortage as described in MOSC note 62354.1.  The eBook "Oracle Space Management Handbook" also has notes on the internals of ITL.

You can also see ITL waits in a STATSPACK or AWR report, in the segments section we see this section:  Segments by ITL Waits: Includes segments that had a large contention for Interested Transaction List (ITL). The contention for ITL can be reduced by increasing INITRANS storage parameter of the table.

Also, Arup Nanda shares scripts for detecting ITL waits:

Select
   s.sid
SID,
   s.serial# Serial#,
   l.type type,
   ' ' object_name,
   lmode held,
   request request
from
   v$lock l,
   v$session s,
   v$process p
where
   s.sid = l.sid and
   s.username <> ' ' and
   s.paddr = p.addr and
   l.type <> 'TM' and
   (l.type <> 'TX' or l.type = 'TX' and l.lmode <> 6)
union
select
   s.sid
SID,
   s.serial# Serial#,
   l.type type,
   object_name object_name,
   lmode held,
   request request
from
   v$lock l,
   v$session s,
   v$process p,
   sys.dba_objects o
where
   s.sid = l.sid and
   o.object_id = l.id1 and
   l.type = 'TM' and
   s.username <> ' ' and
   s.paddr = p.addr
union
select
   s.sid
SID,
   s.serial# Serial#,
   l.type type,
   '(Rollback='||rtrim(r.name)||')' object_name,
   lmode held,
   request request
from
   v$lock l,
   v$session s,
   v$process p,
   v$rollname r
where
   s.sid = l.sid and
   l.type = 'TX' and
   l.lmode = 6 and
   trunc(l.id1/65536) = r.usn and
   s.username <> ' ' and
   s.paddr = p.addr
order by 5, 6;

 

 
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 performance tuning software 
 
oracle dba poster
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 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.