Oracle 11g delete by unique key slow

jdbcoracle

We have been plagued by a deadlock issue for quite some time that relates to deletes and inserts on the same table. It happens sporadically, but had been increasing in frequency. It would manifest itself as a DELETE FROM MYTABLE by unique ID statement BLOCKING INSERT INTO MYTABLE statements.

Initially, we could not see the bind variables because we were using Hibernate and we could not turn on show_sql for Hibernate since there is far too much traffic on other Hibernate-managed POJOs in other areas of the application. We had thought Hibernate might be doing something with its cache at unexpected times.

Recently, I removed Hibernate for the table in question, replaced it with JDBC, and log EVERY touch (every SELECT/INSERT/UPDATE/DELETE) against that table.

After doing that, I've only seen 1 "blocker". So I don't know if the problem is fixed by virtue of removing Hibernate and doing everything via JDBC. However, I would be remiss if I didn't attempt to run down that blocker instance.

Here's what I see:

  • A DELETE FROM MYTABLE by unique ID took almost 10 seconds at one point.
  • There are no other "touches" on MYTABLE for that Unique ID on or around the time of the DELETE FROM MYTABLE by Unique ID.
  • That DELETE blocked other INSERT INTO statements.
  • The "blocker" Delete statement completed in 10 seconds and cleared itself, and after that, everything was fine.
  • It only happened once, the rest of the day DELETE FROM MYTABLE by Unique ID would complete sub-second, as one would expect.

I verified with our DBAs that:

  1. We do not have any on delete triggers on the table in question.
  2. All FKs in that table are indexed.

Does anyone have any insight/recommendations on what might cause a Delete statement by Unique ID to possibly take 10 seconds, when there is no other touches on that table for that row (no select, update, or insert by that Unique ID)?

I realize that there probably isn't a precise and exact answer to this questions, I'm just hoping someone can share insight or anecdotal information on their own experiences.

Thank you sincerely in advance.

Best Answer

This could happen if: - Your table MYTABLE has a unique column UNIQCOL - This MYTABLE.UNIQCOL is referenced by some column in another table, say MYTABLE2.UNIQCOL_REF - This MYTABLE2.UNIQCOL_REF is not indexed.

Adding a (non-unique) index to MYTABLE2.UNIQCOL_REF could then solve the problem.

(You said all FKs in MYTABLE are indexed, but you didn't say that all FKs referencing MYTABLE are indexed - they should be to avoid whole-table locks on MYTABLE2)

Related Question