Delete Operation Slows down intermittently oracle 11g

We have a audit Table in production environment where we delete almost 15 millions data everyday(data which is older than some days)in PL/SQL batch. On a good day this query takes 1.5 hours to delete the same data. But intermittently some days this is not deleting the same amount of data even in 4 hours. There is no trigger on this table and Created column is indexed.

DELETE FROM SIEBEL.CX_AUDIT_SEARCH
WHERE CREATED < '20-MAy-2020' AND rownum <= 10000;

Explain pla:

DELETE STATEMENT    ALL_ROWS    6   10000   180000                  
DELETE SIEBEL.CX_AUDIT_SEARCH                                   
COUNT(STOPKEY)                                  ROWNUM<=10000
INDEX(RANGE SCAN) SIEBEL.CX_AUDIT_SEARCH_U1 ANALYZED    6   170692  3072456             "CREATED"<'20-MAy-2020' 

Could you please suggest any possible reason.

Answer

It’s obvious that this is a very busy table. 15M rows/day means about 10416 rows/minute, which means that at the time you are deleting these rows, thousands of records are being inserted. While Oracle tries to delete the old rows, and update the CREATED index, more rows are inserted and committed.

I see you are trying to DELETE 10000 rows at a time. I assume you commit after these 10000 rows, so the following approach may be a bit faster because the SELECT is running only once and you don’t rescan 1500 times the busy table…

declare 
cursor c_rowids is
  SELECT T.ROWID
      FROM SIEBEL.CX_AUDIT_SEARCH T
     WHERE CREATED < '20-MAy-2020';
type t_tbl_rowids is table of rowid;
tbl_rowids t_tbl_rowids;
begin
  open c_rowids;
  Loop
    fetch c_rowids bulk collect into tbl_rowids limit 10000;
    exit when tbl_rowids.count = 0;
    forall i in 1..tbl_rowids.count
      DELETE FROM SIEBEL.CX_AUDIT_SEARCH
        WHERE ROWID = tbl_rowids(i);

    COMMIT;
  End loop;
  close c_rowids;
end;