Hi everyone,
The server is Oracle 11gr2 on AIX.
The query waits in TM contention and dba_constraints doesn't show any row with constraint_type 'R' (index_name column is null)
INSERT INTO XSCIT ( C_RN, C_POD, C_AT_NO, D_TN, C_BN_TN, C_UNO, C_BAT_NO, R_SYS_AUD_NO, R_EQ_NO, C_BAK, C_MODULE, D_VALUE, D_POST, R_CHQ_NO_TXN, C_ACC_CCY, C_DRCR, C_TXN_MNEMONIC, C_TXN_LITERAL, TXT_TXN_DESC, F_SC_OPTION, C_SC, A_TXN_ACY, C_AUH_ID, A_TXN_LCY, A_TN_TY, R_CNV_TCY, R_CV_ACY, C_TN_CY, C_TO_AC_NO, F_POES, C_RING_NO, F_CREIT, C_OC_CLG_TYPE, R_TXN_NO ) VALUES ( :B23 , :B25 , :B24 , SYSDATE, :B23 , :B22 , :B21 , :B20 , :B19 , :B18 , :B17 , :B16 , :B15 , NULL, :B14 , 'D', :B13 , :B12 , :B11 , :B10 , :B9 , :B8 , :B7 , :B6 , :B5 , :B4 , :B3 , :B2 , NULL, 'N', 0, NULL, NULL, :B1 )
SQL> select owner, table_name, index_name, constraint_name, constraint_type ctype from dba_constraints where table_name='XSCIT';
OWNER TABLE_NAME INDEX_NAME CONSTRAINT_NAME CTYPE
------- ----------- -------------- ------------------------------ ------
FH XSCIT CHK_BATCH_NO C
FH XSCIT SYS_C00130053 C
FH XSCIT SYS_C00130052 C
FH XSCIT SYS_C00130051 C
FH XSCIT SYS_C00130050 C
FH XSCIT SYS_C00130049 C
FH XSCIT SYS_C00130048 C
FH XSCIT SYS_C00130047 C
FH XSCIT SYS_C00130046 C
FH XSCIT SYS_C00133418 C
ASH report shows:
Top Event P1/P2/P3 Values
| Event | % Event | P1 Value, P2 Value, P3 Value | % Activity | Parameter 1 | Parameter 2 | Parameter 3 |
|---|
| enq: TM - contention | 5.50 | "1414332419","189776","0" | 5.50 | name|mode | object # | table/partition |
P2 points to the table mentioned above.
OWNER OBJECT_ID OBJECT_NAME OBJECT_TYPE
--------------- ---------- ------------------------------ -------------------
FH 189776 XSCIT TABLE
There is a trigger which inserts the new record in a temp table. Can anybody help me identify the cause of contention ?
btw, I've searched on this topic, but didn't find any case like mine