INITRANS 参数在parallel 中的影响 12829 ora-12801

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;

----for an existing table or index--truncate应该可以重分配的----

GOAL

How to modify the physical attribute INITRANS for an existing table or index ?

(Note: The INITRANS attribute specifies the initial number of concurrent transaction entries allocated within each data block allocated to the database object)

SOLUTION

The table/index can be altered to set the new value for INITRANS. 
For example a delete on a table does not re-initialize the blocks and therefore will not change the INITRANS.

Basically you need to rebuild the objects so that the blocks are initialized again.

For an index this means the index needs to be rebuild or recreated.

For a table this can be achieved through:
- exp/imp
- alter table move
- dbms_redefenition
   For more information, kindly refer to Note 177407.1: How to Re-Organize a Table Online

---------------

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 （每个事务在块头标记ITL以及回滚段的地址，延迟块清理用的）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 （默认是10）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.

这种问题只会出现在

1.多个事务在同一块操作， 很少出现

2. PDML 的父子事务，这个出现就是死锁了

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).

---最大已经是255，说明不够的情况就是表用的太满了，大量update后的结果？

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
/

------------------INITRANS  不能再tablespace级别设置的

SOLUTION

The INITRANS (ini_trans) parameter is specified when creating a table or index to specify the amount of space to be pre-allocated for an initial number of transaction entries to access rows in the data block concurrently. Space is reserved in the headers of all blocks in the associated data or index segment. For HCM application tables the default value is 10 for tables (because 10*23 bytes = 230 bytes) and 11 for clusters and indexes.

Running Payroll transactions using multiple processes (a THREADS setting of more than 1) requires you to also amend the INITRANS definition accordingly for some key tables and indexes. Without doing this, you may experience locking contention between sub-processes and differences between CPU and elapsed timings for certain Payroll processing SQL statements.

If the ini_trans value is changed on the table, the ini_trans value for the index should be the table value +1.

PCTFREE enables you to manage how the space within data blocks is used. If you are running the application with a large number of THREADS then there may not be enough room in the block header to allocate to variable transactions if there are a lot of processes trying to access the same data block at the same time.

-----------------

------interval partitions------------------------

GOAL

How do you change INITRANS and PCTFREE for a partitioned table or index?
 

SOLUTION

Entire table or index:

alter table <table_name> initrans X pctfree Y; 
alter index <index_name> initrans X ;

Just a partition:

alter table <table name> modify partition <partition name> initrans X;
alter index <index name> modify partition <partition name> initrans X;

Note: You cannot alter for PCTFREE on an index; you have to rebuild the index. X and Y are integers.

 Note: For interval partitions, you may need to add "modify default attributes" clause so interval partitions pick up the correct default attributes when created:  alter table <table_name> modify default attributes initrans <value> pctfree <value>;

------------------分区表

After an initrans modification, the dba_tables view is not updated

For Example:
  

alter table <userid>.<TABLENAME> initrans 20;

Table altered.

select owner, table_name, ini_trans from dba_tables where owner='<USERID>' and table_name = 'TABLENAME';

OWNER TABLE_NAME INI_TRANS
------------------------------ ------------------------------ ----------
<USERID> TABLENAME

 

CHANGES

CAUSE

This is a partitioned table.

See also information below pertaining to IOTs and global temporary tables.

SOLUTION

This is expected behavior with regards to the 'ini_trans' column in  'dba_tables', a null value is explicitly reported if the object is a partitioned table.

In short, for partitioned tables, since storage information for each individual partition is not retrieved by this view, a null value is reported for columns that are partition-specific.

Please see the Example below:
 

SQL> conn XXX/xxxx
Connected.

SQL> CREATE TABLE tablename
2 (salesman_id NUMBER(5),
3 salesman_name VARCHAR2(30),
4 sales_amount NUMBER(10),
5 sales_date DATE)
6 PARTITION BY RANGE(sales_date)
7 (
8 PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')),
9 PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')),
10 PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')),
11 PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY'))
12 );

Table created.

SQL> select owner, table_name, ini_trans from dba_tables where owner='XXX' and table_name = 'tablename';

OWNER TABLE_NAME INI_TRANS
------------------------------ ------------------------------ ----------
XXX tablename

SQL> select TABLE_OWNER , TABLE_NAME,PARTITION_NAME,INI_TRANS from DBA_TAB_PARTITIONS where table_owner='XXX' and table_name = 'tablename';

TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
PARTITION_NAME INI_TRANS
------------------------------ ----------
XXX tablename
SALES_JAN2000 1

XXX tablename
SALES_FEB2000 1

XXX tablename
SALES_MAR2000 1

TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
PARTITION_NAME INI_TRANS
------------------------------ ----------
XXX tablename
SALES_APR2000 1

SQL> select TABLE_OWNER , TABLE_NAME,PARTITION_NAME,INI_TRANS from DBA_TAB_PARTITIONS where table_owner='XXX' and table_name = 'tablename'
2
SQL> alter table tablename initrans 20;

Table altered.

SQL> select TABLE_OWNER , TABLE_NAME,PARTITION_NAME,INI_TRANS from DBA_TAB_PARTITIONS where table_owner='XXX' and table_name = 'tablename';

TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
PARTITION_NAME INI_TRANS
------------------------------ ----------
XXX tablename
SALES_JAN2000 20

XXX tablename
SALES_FEB2000 20

XXX tablename
SALES_MAR2000 20

TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
PARTITION_NAME INI_TRANS
------------------------------ ----------
XXX tablename
SALES_APR2000 20

SQL> select owner, table_name, ini_trans from dba_tables where owner='XXX' and table_name = 'tablename';

OWNER TABLE_NAME INI_TRANS
------------------------------ ------------------------------ ----------
XXX tablename

Also With regard to the 'tablespace_name' column in 'dba_tables', a null value is explicitly reported if the object has any of the following characteristics:
 
  Table is partitioned.
  Table is an IOT (index organized table).
  Table is a global temporary table.

--------------------------

Enq: FB - contention: This is Format Block enqueue, it is seen only when data blocks are using ASSM (Automatic Segment Space Management or bitmap free lists). As we might expect, common FB enqueue relate to buffer busy conditions, especially under heavily DML loads.

The FB enqueue (format block enqueue) is retained if blocks are formatted in ASSM tablespaces (for example, in the context of INSERT operations). Significant waiting times for this enqueue type are generally a result of other problems, such as"log buffer space" waits. Therefore, check whether there are also noticeable Wait Events during times of increased FB Enqueue Waits from AWR and other reports.

SOLUTION

* Enq: FB – contention we can assume that two sessions have simultaneously tried to format the same new batch of blocks and one of them is waiting for the other to complete the format.

* This wait can also be viewed (like the “read by other session” wait) in a positive light – if the second session weren’t waiting for the first session to complete the block format it would have to do the formatting itself, which means the second end-user is actually going to get an improved response time 

* Also the number of blocks picked by a session is dependent on its process id so the second session might have picked a different set of blocks to format, which means that in the elapsed time of one format call the segment could have had more number of blocks formatted – this wouldn’t have improved the end-user’s response time, but it would mean that more time would pass before another session had to spend time formatting blocks. Basically, in a highly concurrent system, there’s not a lot you can do about enq:FB waits (unless, of course, partitioned the hot objects into different tablespace).

* This is most likely to happen if you have a tablespace using large extents and Oracle thinks you’re going to process a relatively small amount of data (e.g. small indexes on large tables) – and its the collisions between processes and wasted space.

select * from v$event_name where name like 'enq: FB%';
 select NAME,PARAMETER1,PARAMETER2,PARAMETER3,WAIT_CLASS_ID,DISPLAY_NAME from v$event_name where name like 'enq: FB%';

 select TYPE,NAME,ID1_TAG,ID2_TAG,IS_USER,DESCRIPTION from v$lock_type where TYPE='FB';

 select session_id, sql_id, event, p1, p1text, p2 "TS#", p3 "Block", wait_time, session_state, time_waited from gv$active_session_history where event like '%FB%'
 
  and sql_id = '<insert_Stmt_SQL_ID_FROM_AWR>'; ------>>>> Replace SQL_ID
  
  
  
  2) If we have multiple sessions doing insert concurrently and more than one session are trying to format the block that may cause the contention.The value on P1 is name|mode , P2 tells the tablespace number, and P3 gives the dba.

SQL> select session_id,SQL_ID,EVENT,P1,P1text,P2,P3,WAIT_TIME,SESSION_STATE,TIME_WAITED from v$active_session_history where event like '%FB%';
SQL> select name from v$tablespace where ts#=P2; ----------->>> P2 value from above

SQL> SELECT dbms_utility.data_block_address_block(p3) "BLOCK", dbms_utility.data_block_address_file(p3) "FILE" from dual; ---->>> P3 Value from Above

-- and --

#1-- Also you can use below ::

SQL> select SID,USERNAME,SQL_ID from v$session where SQL_ID='&SQL_ID';

Enter SQL_IDs

#2-- SQL> select username, event, p1, p2 from v$session_wait where sid =&SID;

Now Enter all the SID separately / one by one for each SQL_ID from (#1).

#3-- SQL> select segment_name,segment_type from dba_extents where file_id = &file_id and &Block_id between block_id and block_id + blocks - 1;

FILE_ID=P1
BLOCK_ID=P2

SQL> alter table <TABLE> INITRANS 10 PCTFREE 40;
SQL> alter index <IND_NAME> rebuild partition <Partition_name> initrans 50 PCTFREE 40;
SQL> alter table <Schema.Table_name> modify default attributes tablespace <TS_NAME> initrans 10;
SQL> ALTER INDEX INDEX_NAME INITRANS 10 ;

3) Check for Below possible workarounds

* If there is any changes in Tablespace / Table level storage parameters recently
* If you have all the object and partitions in same tablespaces then move them to different tablespaces.
* Undersized storage parameters may cause the issue so please check and increase the value ( ex. PCTFREE and INITRANS etc )

SQL> alter table <TABLE> INITRANS 10 PCTFREE 40;
SQL> alter index <IND_NAME> rebuild partition <Partition_name> initrans 50 PCTFREE 40;
SQL> alter table <Schema.Table_name> modify default attributes tablespace <TS_NAME> initrans 10;
SQL> ALTER INDEX INDEX_NAME INITRANS 10 ;
* If you have large index block split then:

For segments with automatic ASSM, Oracle ignores attempts to change the PCT% setting. If you alter the PCT% setting or changed , then you must subsequently run the DBMS_REPAIR.SEGMENT_FIX_STATUS procedure to implement the new setting on blocks already allocated to the segment.

/* Below will Fix the bitmap status for all the blocks in table for that Schema */

SQL> exec dbms_repair.segment_fix_status('<Table_owner>','<Tab_Name>',dbms_repair.table_object);
-- and --

SQL> exec dbms_repair.segment_fix_status('<index_owner>','<index_name>',sys.dbms_repair.index_object);

SQL> exec dbms_repair.segment_fix_status('<index_owner>','<index_name>',dbms_repair.index_object);

Other possible workarounds:

* Auto allocate extent size or Pre allocate the extents to the segment having a high number of inserts.
* Make room for more extents on the Segments also check AWR and ADDM report for other possible Wait events which affects the IO of Database ( db file sequential read etc..)
* Look for any IO issue that is causing slowdown in formatting the blocks.
* Some time high IO operation like LARGE INDEX BLOCK SPLIT may require more extent allocation and may cause the enq: FB contention.
* Try to avoid multiple sessions doing the insert against one segment. Each session may try to format the block and can trigger the contention.
* It's better to put partition segments into different tablespace