Tuesday, October 22, 2013

Some info about initrans fround from blogs and Oracle docs

From internet blogs

When a session requires a slot but all the available ones are in use by other ac􀀠ve transac􀀠ons, the database engine tries to dynamically create a new
slot. This is of course only possible when a) the maximum number of slots was not already allocated b) enough free space (one slot occupies 24 bytes) is
available in the block itself. If a new slot cannot be created, the session requiring it hits a so-called ITL wait. Note that the name of the actual wait event is
called “enq: TX – allocate ITL entry”.
It is essen􀀠al to point out that a session does not wait on the first slot becoming free. Instead, it probes, round-robin, the available slots to find out one
that becomes free. And, while doing so, it waits few seconds on every one it probes. When during this short wait the slot becomes free, it uses it.
Otherwise, it tries with another slot.


http://www.oracle-base.com/forums/viewtopic.php?f=1&t=4078  , says

Let's say you have a block that has five records in it. Let's also say that simultaneously five people update each of the records in the block. The block is taking part in 5 transactions. Space is required in the block to keep track of all this stuff. That's was these parameters are for. The initrans reserves a minimum amount of space in the block that can be used, the maxtrans specifies the maximum amount of space in the block that can be used to hold this information.

If you use locally managed tablespaces with automatic segment space management you can more or less forget about these parameters as Oracle takes care of it for you. In the past they were much more important that they are now. 


Is above in red true ? Can experts advise ?


Searched from Entire Library of 11gR2 Oracle docs

Database Performance Tuning Guide

10 Instance Tuning Using Performance Views



  • Waits for TX in mode 4 can occur if the session is waiting for an ITL (interested transaction list) slot in a block. This happens when the session wants to lock a row in the block but one or more other sessions have rows locked in the same block, and there is no free ITL slot in the block. Usually, Oracle Database dynamically adds another ITL slot. This may not be possible if there is insufficient free space in the block to add an ITL. If so, the session waits for a slot with a TX enqueue in mode 4. This type of TX enqueue wait corresponds to the wait event enq: TX - allocate ITL entry.
    The solution is to increase the number of ITLs available, either by changing the INITRANS or MAXTRANS for the table (either by using an ALTER statement, or by re-creating the table with the higher values).

Data Guard Concepts and Administration

A Troubleshooting Data Guard


Troubleshooting ITL Pressure
Interested transaction list (ITL) pressure is reported in the alert log of the SQL Apply instance. Example A-3 shows an example of the warning messages.
 ...
Resolving ITL Pressure
To increase the INITRANS integer for a particular database object, it is necessary to first stop SQL Apply.


Database VLDB and Partitioning Guide
11g Release 2 (11.2)

Miscellaneous Parallel Execution Tuning Tips

Increasing INITRANS

If you have global indexes, a global index segment and global index blocks are shared by server processes of the same parallel DML statement. Even if the operations are not performed against the same row, the server processes can share the same index blocks. Each server transaction needs one transaction entry in the index block header before it can make changes to a block. Therefore, in the CREATE INDEX or ALTER INDEX statements, you should set INITRANS, the initial number of transactions allocated within each data block, to a large value, such as the maximum DOP against this index.

Database Concepts
11g Release 2 (11.2)

9 Data Concurrency and Consistency


Read Consistency and Transaction Tables
The database uses information in the block header, also called an interested transaction list (ITL), to determine whether a transaction was uncommitted when the database began modifying the block. The block header of every segment block contains an ITL.
Entries in the ITL describe which transactions have rows locked and which rows in the block contain committed and uncommitted changes. The ITL points to the transaction table in the undo segment, which provides information about the timing of changes made to the database.
In a sense, the block header contains a recent history of transactions that affected each row in the block. The INITRANS parameter of the CREATE TABLE and ALTER TABLE statements controls the amount of transaction history that is kept.

Database Performance Tuning Guide
11g Release 2 (11.2)

8 I/O Configuration and Design


8.2.6.2 Writes

For high-concurrency OLTP systems, consider appropriate values for INITRANS, MAXTRANS, and FREELISTS when using a larger block size. These parameters affect the degree of update concurrency allowed within a block. However, you do not need to specify the value for FREELISTS when using automatic segment-space management.
If you are uncertain about which block size to choose, then try a database block size of 8 KB for most systems that process a large number of transactions. This represents a good compromise and is usually effective. Only systems processing LOB data need more than 8 KB.

No comments:

Post a Comment