-ORA-12801: error signaled in parallel query server P009, instance  XX.local:dwhyyy2 (2)"

ORA-12829: Deadlock - itls occupied by siblings at block 16320 of file 139

SYMPTOMS

Sql*plus script ends abnormally with the following errors:
 
ORA-20001: INACTIVE_ITEMS - ORA-20001: <item name> -
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 <procedure name> 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.

CAUSE

Incorrect table configuration seems to cause the error.

SOLUTION

To correct the problem, have done the following:

alter table <table name> initrans 32 pctfree 25;
alter index <index name> 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.

job 在很多表发出8个parallel的delete，再同一个transaction 在，如果这几个表的记录在同一个数据块，就会出现ITL不足，机会是不是很低，一个块只能容纳几百条记录，几个表还要同时放在一个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 too 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
/