Oracle: delete suddenly taking a long time

Question

Hi

We have a feed process which runs every day of the year. As part of that we delete every row from a table (approx 1 million rows) every day, repopulate it using 5 different stored procedures and then commit the transaction. This is the only commit statement that we call. All of a sudden the delete has started takign about 2 hours to complete. The delete is also very simple (delete from T_PROFILE_WORK) This has worked perfectly well for the past year, but in the past week i have noticed this issue.

Any help on this is greatly appreciated

Thanks Damien

Answer 1

If you just want to remove everything from the table use truncate instead of delete.

Answer 2

Hi Damo,

please review the answers of this SO Question: "oracle delete query taking too much time":

1. You could be blocked by another session (most likely). Before you delete you should make sure noone else is locking the rows, eg: issue SELECT NULL FROM tablename WHERE colname=:value FOR UPDATE NOWAIT,
2. There could be a ON DELETE TRIGGER that does additional work,
3. Check for UNINDEXED REFERENCE CONSTRAINTS pointing to this table (there is a script from AskTom that will help you determine if such unindexed foreign keys exist).

I would check #2 and #3 first, they are the easiest to diagnose.

Answer 3

How about you have a second (perhaps global temporary) table which you populate from the stored procedures and, at the end, do a MERGE into the existing table. Without know the details of the activity, table structures etc, it is hard to be certain. But you may find it quicker.

As an alternative, if you have Enterprise Edition with the partitioning option, look at partition exchange.