ORA 12829: Deadlock - ITLs Occupied by Siblings at Block %s of File %s (Doc ID 1326120.1)
Information in this document applies to any platform.
select name,value
from v$parameter
where name = ‘statistics_level’;
NAME VALUE
———————————— ———–
statistics_level TYPICAL
TTITLE “ITL-Waits per table (INITRANS to small)”
set pages 1000
col owner format a15 trunc
col object_name format a30 word_wrap
col value format 999,999,999 heading “NBR. ITL WAITS”
–
select owner,
object_name||’ ‘||subobject_name object_name,
value
from v$segment_statistics
where statistic_name = ‘ITL waits’
and value > 0
order by 3,1,2;
–
col owner clear
col object_name clear
col value clear
ttitle off
/
APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.2.0.1 to 12.1.0.2 [Release 10.2 to 12.1]Information in this document applies to any platform.
SYMPTOMS
Sql*plus script ends abnormally with the following errors:
ORA-20001: INACTIVE_ITEMS - ORA-20001: UPDATE_STAT_FCST -
ORA-20002: -12801 ORA-12801: error signaled in parallel query server P037
ORA-12829: Deadlock - itls occupied by siblings at block 166708 of file 45
Error in PROCEDURE UPDATE_STAT_FCST line 173
ORA-20001: INACTIVE_ITEMS - ORA-20001: UPDATE_STAT_FCST -
ORA-20002: -12801 ORA-12801: error signaled in parallel query server P037
ORA-12829: Deadlock - itls occupied by siblings at block 166708 of file 45
Error in PROCEDURE UPDATE_STAT_FCST line 173
Details of error:
Error: ORA 12829
Text: Deadlock - itls occupied by siblings at block %s of file %s
---------------------------------------------------------------------------
Cause: parallel statement failed because all itls in the current block are occupied by siblings of the same transaction.
Action: increase MAXTRANS of the block or reduce the degree of parallelism for the statement. Reexecute the statement.
Report suspicious events in trace file to Oracle support representative if error persists.
Error: ORA 12829
Text: Deadlock - itls occupied by siblings at block %s of file %s
---------------------------------------------------------------------------
Cause: parallel statement failed because all itls in the current block are occupied by siblings of the same transaction.
Action: increase MAXTRANS of the block or reduce the degree of parallelism for the statement. Reexecute the statement.
Report suspicious events in trace file to Oracle support representative if error persists.
CAUSE
Incorrect table configuration seems to cause the error.
SOLUTION
To correct the problem, have done the following:
alter table CTO_DATA initrans 32 pctfree 25;
alter index CTO_DATA_IDX initrans 32;
alter index CTO_DATA_IDX initrans 32;
Additional information related to the error:
ITL Entry Shortages
==============
There is an interested transaction list (ITL) in the variable header of each Oracle data block. When a new block is formatted for a segment, the initial number of entries in the ITL is set by the INITRANS parameter for the segment. Free space permitting, the ITL can grow dynamically if required, up to the limit imposed by the database block size, or the MAXTRANS parameter for the segment, whichever is less.
Every transaction that modifies a data block must record its transaction identifier and the rollback segment address for its changes to that block in an ITL entry. (However, for discrete transactions, there is no rollback segment address for the changes.) Oracle searches the ITL for a reusable or free entry. If all the entries in the ITL are occupied by uncommitted transactions, then a new entry will be dynamically created, if possible.
If the block does not have enough internal free space to dynamically create an additional ITL entry, then the transaction must wait for a transaction using one of the existing ITL entries to either commit or roll back. The blocked transaction waits in shared mode on the TX enqueue for one of the existing transactions, chosen pseudo-randomly. The row wait columns in V$SESSION show the object, file, and block numbers of the target block. However, the ROW_WAIT_ROW# column remains unset, indicating that the transaction is not waiting on a row-level lock, but is probably waiting for a free ITL entry.
The most common cause of ITL entry shortages is a zero PCTFREE setting. Think twice before setting PCTFREE to zero on a segment that might be subject to multiple concurrent updates to a single block, even though those updates may not increase the total row length. The degree of concurrency that a block can support is dependent on the size of its ITL, and failing that, the amount of internal free space. Do not, however, let this warning scare you into using unnecessarily large INITRANS or PCTFREE settings. Large PCTFREE settings compromise data density and degrade table scan performance, and non-default INITRANS settings are seldom warranted.
One case in which a non-default INITRANS setting is warranted is for segments subject to parallel DML. If a child transaction of a PDML transaction encounters an ITL entry shortage, it will check whether the other ITL entries in the block are all occupied by its sibling transactions and, if so, the transaction will roll back with an ORA-12829 error, in order to avoid self-deadlock. The solution in this case is to be content with a lower degree of parallelism, or to rebuild the segment with a higher INITRANS setting. A higher INITRANS value is also needed if multiple serializable transactions may have concurrent interest in any one block.
Check ITL Waits
=============
The following SQL-Statement shows the number of ITL-Waits per table (Interested Transaction List). INITRANS and/or PCTFREE for those tables is to small (could also be that MAXTRANS is too small). Note that STATISTICS_LEVEL must be set to TYPICAL or ALL, MAXTRANS has been desupported in Oracle 10g and now is always 255 (maximum).
ITL Entry Shortages
==============
There is an interested transaction list (ITL) in the variable header of each Oracle data block. When a new block is formatted for a segment, the initial number of entries in the ITL is set by the INITRANS parameter for the segment. Free space permitting, the ITL can grow dynamically if required, up to the limit imposed by the database block size, or the MAXTRANS parameter for the segment, whichever is less.
Every transaction that modifies a data block must record its transaction identifier and the rollback segment address for its changes to that block in an ITL entry. (However, for discrete transactions, there is no rollback segment address for the changes.) Oracle searches the ITL for a reusable or free entry. If all the entries in the ITL are occupied by uncommitted transactions, then a new entry will be dynamically created, if possible.
If the block does not have enough internal free space to dynamically create an additional ITL entry, then the transaction must wait for a transaction using one of the existing ITL entries to either commit or roll back. The blocked transaction waits in shared mode on the TX enqueue for one of the existing transactions, chosen pseudo-randomly. The row wait columns in V$SESSION show the object, file, and block numbers of the target block. However, the ROW_WAIT_ROW# column remains unset, indicating that the transaction is not waiting on a row-level lock, but is probably waiting for a free ITL entry.
The most common cause of ITL entry shortages is a zero PCTFREE setting. Think twice before setting PCTFREE to zero on a segment that might be subject to multiple concurrent updates to a single block, even though those updates may not increase the total row length. The degree of concurrency that a block can support is dependent on the size of its ITL, and failing that, the amount of internal free space. Do not, however, let this warning scare you into using unnecessarily large INITRANS or PCTFREE settings. Large PCTFREE settings compromise data density and degrade table scan performance, and non-default INITRANS settings are seldom warranted.
One case in which a non-default INITRANS setting is warranted is for segments subject to parallel DML. If a child transaction of a PDML transaction encounters an ITL entry shortage, it will check whether the other ITL entries in the block are all occupied by its sibling transactions and, if so, the transaction will roll back with an ORA-12829 error, in order to avoid self-deadlock. The solution in this case is to be content with a lower degree of parallelism, or to rebuild the segment with a higher INITRANS setting. A higher INITRANS value is also needed if multiple serializable transactions may have concurrent interest in any one block.
Check ITL Waits
=============
The following SQL-Statement shows the number of ITL-Waits per table (Interested Transaction List). INITRANS and/or PCTFREE for those tables is to small (could also be that MAXTRANS is too small). Note that STATISTICS_LEVEL must be set to TYPICAL or ALL, MAXTRANS has been desupported in Oracle 10g and now is always 255 (maximum).
select name,value
from v$parameter
where name = ‘statistics_level’;
NAME VALUE
———————————— ———–
statistics_level TYPICAL
TTITLE “ITL-Waits per table (INITRANS to small)”
set pages 1000
col owner format a15 trunc
col object_name format a30 word_wrap
col value format 999,999,999 heading “NBR. ITL WAITS”
–
select owner,
object_name||’ ‘||subobject_name object_name,
value
from v$segment_statistics
where statistic_name = ‘ITL waits’
and value > 0
order by 3,1,2;
–
col owner clear
col object_name clear
col value clear
ttitle off
/
-=========================================================================================
0mocil0ria-shi2001 Heather Wang https://wakelet.com/wake/tA51Gw89-PeRrQLU69j9M
ReplyDeletefromiztralvi
claminco-to Andrew Allen Avast Internet Security
ReplyDeleteVysor
Firefox browser
meconrappwadd
Mtiotaef_ra_1990 Wewere Tucson Software
ReplyDeleteAwesome
diotrucuntab