Oracle 11g delete by unique key slow

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:

• We do not have any on delete triggers on the table in question.

• 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 j

Delete is a DML command and stores the data in redo log till the delete operation is committed.
This means that if data to be removed by delete is slightly large[even though search time is less] it will take longer time as it will move data to redo log.

So may be the instance when your operation took longer large no. of rows were being deleted to many queries might be writing to redo log.

StackExchange Database Administrators Q#41103, answer score: 2