Delete from empty table taking forver

Question

I have an empty table that previously had a large amount of rows.

The table has about 10 columns and indexes on many of them, as well as indexes on multiple columns.

DELETE FROM item WHERE 1=1

This takes approximately 40 seconds to complete

SELECT * FROM item

this takes 4 seconds.

The execution plan of SELECT * FROM ITEM shows the following;

SQL> select * from midas_item;

no rows selected

Elapsed: 00:00:04.29

Execution Plan
----------------------------------------------------------
 0      SELECT STATEMENT Optimizer=CHOOSE (Cost=19 Card=123 Bytes=73
      80)

1    0   TABLE ACCESS (FULL) OF 'MIDAS_ITEM' (Cost=19 Card=123 Byte
      s=7380)





Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
   5263  consistent gets
   5252  physical reads
      0  redo size
   1030  bytes sent via SQL*Net to client
    372  bytes received via SQL*Net from client
      1  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      0  rows processed

any idea why these would be taking so long and how to fix it would be greatly appreciated!!

Answer 1

One possibility is a lock. That is there was a row in the table which had been committed and locked by another delete. Your delete sat and waited on the lock. When the locking transaction was committed, your delete was then able to finish.

A second possibility is that you ran the delete first, which fetched the blocks from disk into the cache (which took time). When the select ran, the data was in the cache and so ran quicker. I think this is less likely as you select stats indicated "5252 physical reads", so it wasn't getting them from the SGA cache. It is possible that a disk cache was involved though.

A third possibility is that there is a BEFORE/AFTER DELETE trigger (not FOR EACH ROW) which did something.

A fourth possibility is that the DELETE resulted in a delayed block cleanout. When the rows were actually deleted, if they were written to disk prior to being committed they'd still have the lock/transaction info. Your delete comes along, reads the blocks, sees the now outdated transaction info, removes it and re-writes the block.

A fifth possibility is contention. Maybe there was just more happening at the same time as the delete.

Lots of possibilities. If you can reproduce it, then do a trace with wait events and run it through TKPROF.

Answer 2

If your table previously was filled with a large amount of data, then Oracle will scan it up to the high water mark, even if it has no data NOW. You may use TRUNCATE statement to reset HWM.

Also on AskTom

Answer 3

Select is just doing a full table scan. Delete on the other hand (in Oracle ) will have to store the whole deleted rows in the rollback segments in order to allow you to undo the changes later (so it can be slower even than the insert).

You can find a very long and useful discussion related to that on Ask Tom forum. Depending on your business case maybe you can apply more techniques.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2345591157689