DELETE FROM EKSTAT_ID
WHERE
 DATUM = :B1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      5.20     255.66      46028         77     106634       14843
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      5.20     255.66      46028         77     106634       14843

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: 47     (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  DELETE  EKSTAT_ID 
                                    (cr=140 pr=46028 pw=0 time=255656178 us)
     14843      14843      14843   INDEX RANGE SCAN IDX_EKSTAT_DAT 
                                    (cr=67 pr=92 pw=0 time=81738 us 
                                     cost=48 size=405990 card=10410)(object id 53919)

1. the database identifies those rows that need deleting.

2. then it locks and fetches the rows from the underlying table.

at this point the database has access to all columns on the row so that means it can do a simple index lookup on every index to find the index entries that need to be removed.

3. the database accesses each index doing a simple equals access on all columns in the index and modifies the blocks to remove the identified entries.  You can check what is being done by observing recursive SQL that is executed on your behalf to find and remove rows for the indexes.  I have never bothered to do this myself so I could be wrong here.  This is one of those basic assumptions I have just always made.  Maybe it is time for me to some real detective work for myself.  Maybe your should too.

4. it removes rows from the underlying table block.

1. hardware failures.  You are experiencing some form of hardware failure (network / disk / memory / etc.) that is causing lots of attempts.  You can fix this by having your people look at logs for the appropriate hardware.  Once you identify the bad piece of hardware, replace it.

2. locking.  You are waiting on locks.  Unbeknownst to you, someone is locking and later releasing one or more rows you want to delete.  To fix this figure out who is blocking you and time your deletes to avoid them.

3. bad index statistics.  These are misleading the optimizer into doing index fast full scans in order to locate rows to be deleted.  Try updating index statistics.