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: MAS2.abcd.com
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]

Solution:
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.

http://www.dba-oracle.com/t_initrans.htm

Oracle Finetuning
http://www.dba-oracle.com/art_sql_tune.htm
http://www.orafaq.com/wiki/Oracle_database_Performance_Tuning_FAQ

Advertisements

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

    Like

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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