Oracle deadlock for Delete statement

When analyzing oracle dump
kjxocdr: drop duplicate open [0x1530018][0x362c1],[TX].17 0x0x1b834e988 [held 0][req 3]
kjxocdr: drop duplicate open [0x1530018][0x362c1],[TX].17 0x0x1b834e988 [held 0][req 3]
kjddopr: skip converting lock 0x1d96bf530 dd_cnt 1
user session for deadlock lock 0x1d93e8088
pid=575 serial=63890 audsid=150018868 user: 77/MAS
O/S info: user: abcuser, term: unknown, ospid: 1234, machine:
program: JDBC Thin Client
application name: JDBC Thin Client, hash value=2546894661
Current SQL Statement:
delete from MAS.table_name where app_name = :1 and lst_updt_ts between :2 and :3
ENQUEUE DUMP REQUEST: from 0.27988 on [0x320020][0x182e1a],[TX] for reason 3 mtype 0
DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0x320020][0x182e1a],[TX]

Problem: look at the table description …you can see…INITRANS 2 MAXTRANS 255

We need to increase INITRANS to 100 for better performance. When we have more concurrent sessions, this is important.

Oracle Finetuning


One thought on “Oracle deadlock for Delete statement

  1. Until your trace does not shows and itl sign , there is no pint increasing it.

    scn: 0x0000.389b8d81 seq: 0x01 flg: 0x04 tail: 0x8d810601

    frmt: 0x02 chkval: 0x2ef5 type: 0x06=trans data

    Block header dump: 0x02011f88

    Object id on Block? Y

    seg/obj: 0xd1ad csc: 0x00.389b8d81 itc: 4 flg: – typ: 1 – DATA

    fsl: 0 fnx: 0x0 ver: 0x01

    Itl Xid Uba Flag Lck Scn/Fsc

    0x01 0x0003.003.000024cc 0x00804067.050a.13 C-U- 0 scn 0x0000.389b304e

    0x02 0x0007.010.00002763 0x00801f49.0453.01 C— 0 scn 0x0000.389b2628

    0x03 0x0002.00a.000025d5 0x00804d42.04b2.25 C— 0 scn 0x0000.389b2811

    0x04 0x0006.006.00002515 0x00800962.03c8.18 CU 0 scn 0x0000.389b3044


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s