Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Forall delete with rowid- top wait event - db file sequential read further optimization

user591200Mar 30 2021 — edited Mar 30 2021

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

Comments

Post Details

Added on Mar 30 2021
3 comments
547 views