Oracle INITRANS tips Oracle Database Tips by Donald Burleson

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.