delete statement contention
497150Mar 7 2007 — edited Mar 7 2007I have problem with delete statement that runs slowly and causes contention. The delete statements for our app depend on the data processed and are usually rear. But this time, there are many of them and they might delete up to several hundred rows at the time. The size of the table is a little over 200 mln rows and the contention is due to the multiple processes that access this table simultaneously for inserts, deletes, updates.
These are the top 5 events:
Event Waits Time (s) (ms) Time
enq: TX - row lock contention 16,682 31,354 1880 35.3
db file sequential read 3,297,450 30,594 9 34.4
latch: enqueue hash chains 50,017 15,592 312 17.5
CPU time 8,343 9.4
db file parallel write 631,033 829 1 .9
I did a trace session by session and all I see are the deletes causing not only enq: TX - row lock contention, but also enq: TX - allocate ITL entry and latch: enqueue hash chains – these are the event with big waits > 1sec in the statpack too.
I also see many reads on delete statements.
Oracle 10.1.0.5, big SAN on windows 2000 Ent.
What will be the best way to approach this problem and try to fix the big waits?
Thanks a lot, for the help, mj