oracle delete query taking too much time

Question

I have a query like

DELETE from tablename where colname = value;

which takes awfully long time to execute. What could be the reason? I have an index on colname.

Answer 1

it could be that your table is related to multiple tables have a huge row count.

Answer 2

Hi Ajay,

There could be several explanations as to why your query takes a long 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).

Answer 3

How selective is that index? If your table has one million rows and that value hits one hundred and fifty thousand of them then your index is useless. In fact it may be worse than useless if it is actually being used. Remember, a DELETE is a like a SELECT statement: we can tune its access path.

Also, deletes take up a lot of undo tablespace, so you might be suffereing from contention, if the system is experiencing heavy use. In a multi-user system another session might have a lock on the rows(s) you want to delete.

Do you have ON DELETE triggers? Do you have ON DELETE CASCADE foreign key constraints?

Edit: Given all that you say, and especially the column in question being the primary key so you are attempting to delete a single row, if it is taking a long time it is much more likely that some other process or user has a lock on the row. Is anything showing up in V$LOCK?

Answer 4

Does your table holds more number of records ?
Is there some recursive programs(some nested loops etc..) running on the database server ?
Check network problems if database server is on different machines ?

Answer 5

If something's slow, and you don't know why, trace it and find out.

http://stackoverflow.com/questions/104066/how-do-i-troubleshoot-performance-problems-with-an-oracle-sql-statement