When a session requires a slot but all the available ones are in use by other acve transacons, 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 essenal 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 theINITRANS
orMAXTRANS
for the table (either by using anALTER
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 theCREATE
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 forINITRANS
, 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