Hi,
A delete job written in PL/SQL that uses a bulk collection to fetch the rowid's and Forall delete construct with rowid is hindered by top wait event - "db file sequential read".
The FORALL statements binds 40000 rows at a time and the collection holds 500000 rows.
The table has 3 indexes on it.
The ASH history shows that the delete is spending,
59% on wait event - "db file sequential read"
21% on CPU
19% on Cluster waits
The AWR Top time events, db file sequential read - Avg wait 870us , min 868.28us and max is 904.58us.
I don't think there is much I can improve on the I/O side.
Instead of FORALL construct, I can code it with a
DELETE FROM T1 WHERE rownum < 40000 and created_date <= (sysdate - 90);
But the developer insists that the above FORALL is much faster since it goes by ROWID.
One of the primary goals of this process is to gradually delete 130 Million of rows. A CTAS parallel would need downtime to complete this, so they are trying to avoid it.
Any thoughts on how can I improve the performance of this SQL?
Environment: Oracle 12.2.0.1.0 EE/ AIX 7.2 / Oracle RAC 3 nodes