Hi Experts,
I have one requirement from user where in approx 50k rows to be deleted from table, but delete operation is taking hell lot of time and still unfinished after 7+ hours.However same statement with select is giving o/p in 1 sec,
Details as below
DB Oracle 11gr2
Statement
delete
from T1 SCONV WHERE SCONV.JDOID in
(SELECT STG.JDOID FROM T2 STG
WHERE
TABLE_NAME='T1' AND IS_PARENT='Y')
AND SCONV.PREVIOUSCONFIGURATION IS NULL AND
CREATEDDATE = to_date('20140711', 'YYYYMMDD HH24:MI:SS')
SCONV.JDOID is NOT NULL column where oracle created unique index is already using in plan.
UIM_ROLLBK_IDX is composite index on table T2(TABLE_NAME,IS_PARENT_Y,JDOID)
Explain plan
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 15 | 885 | 1179 (1)| 00:00:15 |
| 1 | DELETE | T1 | | | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 15 | 885 | 1179 (1)| 00:00:15 |
| 4 | SORT UNIQUE | | 1745 | 55840 | 302 (1)| 00:00:04 |
|* 5 | INDEX RANGE SCAN | UIM_ROLLBK_IDX | 1745 | 55840 | 302 (1)| 00:00:04 |
|* 6 | INDEX UNIQUE SCAN | SYS_C0011467 | 1 | | 1 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 27 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
This is quite surprising to me. Fresh stats is available on tables and indexes are also rebuild to support deletion. One more observation is there when i am monitoring deletion its waiting for "db file scattered read" in place of sequential read.
Space is available in UNDO and Temp Tablespace and no significant error in alert log
Can anyone please explain how to speed up deletion process and what all things to check & improve this.
Rgds
Gaurav