We're on Oracle 11.2.0.4. Recently application has become too slow. Checking on the active sessions, I'm seeing a lot of waits on "enq: TM - contention'"
Consequently, on checking the alert log of the database, I can see sessions being killed due to deadlocks.
I suspect the deadlocks could be occurring due to locks caused by lack of indexes on the foreign keys. I have check the table and I can see all the foreign keys are indexed. Here is the snippet of the deadlock logs:
DEADLOCK DETECTED ( ORA-00060 )
[Transaction Deadlock]
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TM-00024770-00000000 174 2290 SX 86 2280 S
TM-000246c6-00000000 86 2280 SX 174 2290 S
session 2290: DID 0001-00AE-00041C40 session 2280: DID 0001-0056-0010FEDE
session 2280: DID 0001-0056-0010FEDE session 2290: DID 0001-00AE-00041C40
Rows waited on:
Session 2290: obj - rowid = 000246C6 - AAAAAAAAAAAAAAAAAA
(dictionary objn - 149190, file - 0, block - 0, slot - 0)
Session 2280: obj - rowid = 00024770 - AAAAAAAAAAAAAAAAAA
(dictionary objn - 149360, file - 0, block - 0, slot - 0)
----- Information for the OTHER waiting sessions -----
Session 2280:
sid: 2280 ser: 23199 audsid: 541787 user: 501/MOGAKAE
flags: (0x8000045) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 86 O/S info: user: SYSTEM, term: BAEQDIGAS11G, ospid: 7084
image: ORACLE.EXE (SHAD)
client details:
O/S info: user: britakGROUP\BAEQDIGASAPP$, term: BAEQDIGASAPP, ospid: 6704:20664
machine: britakGROUP\BAEQDIGASAPP program: frmweb.exe
application name: CSV_TRAD, hash value=2694402937
current SQL:
UPDATE POLICY POL SET POL.POLICY_NO=:1,POL.START_DATE=:2,POL.POL_REF_NO=:3,POL.NX_EXP_PR_DT=:4,POL.NX_PR_INSTNO=:5,POL.TERM=:6,POL.FREQ=:7,POL.POLICY_FEE=:8,POL.CURR_NO=:9,POL.SITENO=:10,POL.POL_STAT=:11,POL.PLANNO=:12,POL.GROSS_PREMIUM=:13,POL.INCOME_FREQ=:14 WHERE ROWID=:15 RETURNING POL.ROWID,POL.POLICY_NO,POL.START_DATE,POL.POL_REF_NO,POL.NX_EXP_PR_DT,POL.NX_PR_INSTNO,POL.TERM,POL.FREQ,POL.POLICY_FEE,POL.CURR_NO,POL.SITENO,POL.POL_STAT,POL.PLANNO,POL.GROSS_PREMIUM,POL.INCOME_FREQ INTO :16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30
----- End of information for the OTHER waiting sessions -----
Information for THIS session:
----- Current SQL Statement for this session (sql_id=gymqxhqbjfkac) -----
UPDATE POLICY SET INCOME_FREQ = :b1,GROWTH_RATE = :b2,MIN_PENSION = :b3,PROP_RECEIVED_DATE = :b4,PROP_NO = :b5,START_DATE = :b6,SIGN_DATE = :b7,SUB_STANDARD = :b8,STAFF_POLICY = :b9,FREQ = :b10,POLICY_FEE = :b11,NX_EXP_PR_DT = :b6,BASIC_PREMIUM = :b12,GROSS_PREMIUM = :b12 + :b11 ,TECHNICAL_RATE = :b13,RETURN_RATE = :b14,LOMBARD_RATE = :b15,TOTAL_SPE = :b16,TOTAL_NON_SPE = :b17 WHERE SITENO = :b18 AND POL_REF_NO = :b19
===================================================
PROCESS STATE
-------------
Process global information:
process: 0x00007FF7F75BBCE8, call: 0x00007FF7E3F43C98, xact: 0x00007FF7ED6A1908, curses: 0x00007FF7F6A83860, usrses: 0x00007FF7F6A83860
in_exception_handler: no
----------------------------------------
SO: 0x00007FF7F75BBCE8, type: 2, owner: 0x0000000000000000, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0x00007FF7F75BBCE8, name=process, file=ksu.h LINE:12721, pg=0
(process) Oracle pid:174, ser:90, calls cur/top: 0x00007FF7E3F43C98/0x00007FF7E3F43C98
flags : (0x0) -
flags2: (0x0), flags3: (0x10)
intr error: 0, call error: 0, sess error: 0, txn error 0
intr queue: empty
ksudlp FALSE at location: 0
(post info) last post received: 138 0 2
last post received-location: ksl2.h LINE:2374 ID:kslpsr
last process to post me: 0x7ff7f85e2e20 1 6
last post sent: 0 0 26
last post sent-location: ksa2.h LINE:285 ID:ksasnd
last process posted by me: 0x7ff7f85e2e20 1 6
(latch info) wait_event=0 bits=0x0
Process Group: DEFAULT, pseudo proc: 0x00007FF7F7981DE0
O/S info: user: SYSTEM, term: BAEQDIGAS11G, ospid: 7524
OSD pid info: Windows thread id: 7524, image: ORACLE.EXE (SHAD)
----------------------------------------
SO: 0x00007FF7EF025B58, type: 10, owner: 0x00007FF7F75BBCE8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1
proc=0x00007FF7F75BBCE8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0
(FOB) 00007FF7EF025B58 flags=2050 fib=00007FF7EE0CE638 incno=0 pending i/o cnt=0
fname=E:\ORACLE\ORADATA\IGAS11\SYSTEM01.DBF
fno=1 lblksz=8192 fsiz=199680
----------------------------------------
SO: 0x00007FF7EF0708A8, type: 10, owner: 0x00007FF7F75BBCE8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1
proc=0x00007FF7F75BBCE8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0
(FOB) 00007FF7EF0708A8 flags=2050 fib=00007FF7EE0CF8B0 incno=0 pending i/o cnt=0
fname=E:\ORACLE\ORADATA\IGAS11\USERS01.DBF
fno=4 lblksz=8192 fsiz=2677760
----------------------------------------
SO: 0x00007FF7EF0180D8, type: 10, owner: 0x00007FF7F75BBCE8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1
proc=0x00007FF7F75BBCE8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0
(FOB) 00007FF7EF0180D8 flags=2050 fib=00007FF7EE0D1DB8 incno=0 pending i/o cnt=0
fname=F:\ORACLE\ORADATA\IGAS11\IGASINDEX03.DBF
fno=10 lblksz=8192 fsiz=1920000
----------------------------------------
SO: 0x00007FF7EF06DF48, type: 10, owner: 0x00007FF7F75BBCE8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1
proc=0x00007FF7F75BBCE8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0
(FOB) 00007FF7EF06DF48 flags=2050 fib=00007FF7EE0D1160 incno=0 pending i/o cnt=0
fname=F:\ORACLE\ORADATA\IGAS11\IGASINDEX01
fno=8 lblksz=8192 fsiz=1920000
----------------------------------------
SO: 0x00007FF7EF06DE00, type: 10, owner: 0x00007FF7F75BBCE8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1
proc=0x00007FF7F75BBCE8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0
(FOB) 00007FF7EF06DE00 flags=2050 fib=00007FF7EE0CF290 incno=0 pending i/o cnt=0
fname=E:\ORACLE\ORADATA\IGAS11\UNDOTBS01.DBF
fno=3 lblksz=8192 fsiz=3308160
----------------------------------------
SO: 0x00007FF7EF03C040, type: 10, owner: 0x00007FF7F75BBCE8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1
proc=0x00007FF7F75BBCE8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0
(FOB) 00007FF7EF03C040 flags=2050 fib=00007FF7EE0D0508 incno=0 pending i/o cnt=0
fname=E:\ORACLE\ORADATA\IGAS11\IGASDATA02.DBF
fno=6 lblksz=8192 fsiz=1710216
----------------------------------------
SO: 0x00007FF7EF0380E8, type: 10, owner: 0x00007FF7F75BBCE8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1
proc=0x00007FF7F75BBCE8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0
(FOB) 00007FF7EF0380E8 flags=2050 fib=00007FF7EE0D0B40 incno=0 pending i/o cnt=0
fname=E:\ORACLE\ORADATA\IGAS11\IGASDATA03.DBF
fno=7 lblksz=8192 fsiz=1710216
----------------------------------------
SO: 0x00007FF7EF030B30, type: 10, owner: 0x00007FF7F75BBCE8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1
proc=0x00007FF7F75BBCE8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0
(FOB) 00007FF7EF030B30 flags=2050 fib=00007FF7EE0CFEE8 incno=0 pending i/o cnt=0
fname=E:\ORACLE\ORADATA\IGAS11\IGASDATA01
fno=5 lblksz=8192 fsiz=1718512
----------------------------------------
SO: 0x00007FF7EF02E998, type: 10, owner: 0x00007FF7F75BBCE8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1
proc=0x00007FF7F75BBCE8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0
(FOB) 00007FF7EF02E998 flags=2050 fib=00007FF7EE0D1798 incno=0 pending i/o cnt=0
fname=F:\ORACLE\ORADATA\IGAS11\IGASINDEX02.DBF
fno=9 lblksz=8192 fsiz=1920000
----------------------------------------
SO: 0x00007FF7F6A83860, type: 4, owner: 0x00007FF7F75BBCE8, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0x00007FF7F75BBCE8, name=session, file=ksu.h LINE:12729, pg=0
(session) sid: 2290 ser: 14029 trans: 0x00007FF7ED6A1908, creator: 0x00007FF7F75BBCE8
flags: (0x8000045) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
DID: , short-term DID:
txn branch: 0x0000000000000000
edition#: 100 oct: 6, prv: 0, sql: 0x00007FF6DAEF20E0, psql: 0x00007FF70E6E8FE8, user: 573/QAWINO
ksuxds FALSE at location: 0
service name: igas11
client details:
O/S info: user: britakGROUP\BAEQDIGASAPP$, term: BAEQDIGASAPP, ospid: 17500:11968
machine: britakGROUP\BAEQDIGASAPP program: frmweb.exe
application name: BENEFITS, hash value=1454952988
Current Wait Stack:
0: waiting for 'enq: TM - contention'
name|mode=0x544d0004, object #=0x246c6, table/partition=0x0
wait_id=120769 seq_num=55235 snap_id=1
wait times: snap=6.036076 sec, exc=6.036076 sec, total=6.036076 sec
wait times: max=infinite, heur=6.036076 sec
wait counts: calls=2 os=2
in_wait=1 iflags=0x15a0
There is at least one session blocking this session.
Dumping 1 direct blocker(s):
inst: 1, sid: 2280, ser: 23199
Dumping final blocker:
inst: 1, sid: 2280, ser: 23199
There are 2 sessions blocked by this session.
Dumping one waiter:
inst: 1, sid: 2280, ser: 23199
wait event: 'enq: TM - contention'
p1: 'name|mode'=0x544d0004
p2: 'object #'=0x24770
p3: 'table/partition'=0x0
row_wait_obj#: 149360, block#: 0, row#: 0, file# 0