- 3,728,544 Users
- 2,245,649 Discussions
- 7,853,590 Comments
- 380.6K All Categories
- 2.1K Data
- 202 Big Data Appliance
- 1.9K Data Science
- 445.7K Databases
- 220.3K General Database Discussions
- 22 Multilingual Engine
- 499 MySQL Community Space
- 458 NoSQL Database
- 7.7K Oracle Database Express Edition (XE)
- 2.8K ORDS, SODA & JSON in the Database
- 426 SQLcl
- 3.9K SQL Developer Data Modeler
- 185.2K SQL & PL/SQL
- 20.7K SQL Developer
- 290.9K Development
- 5 Developer Projects
- 114 Programming Languages
- 287.6K Development Tools
- 94 DevOps
- 3K QA/Testing
- 645.1K Java
- 16 Java Learning Subscription
- 36.9K Database Connectivity
- 145 Java Community Process
- 103 Java 25
- 22.1K Java APIs
- 137.6K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 11 Java Essentials
- 135 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 193 Java User Groups
- 160 LiveLabs
- 33 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 165 Deutsche Oracle Community
- 1.2K Español
- 1.9K Japanese
- 225 Portuguese
Forall delete with rowid- top wait event - db file sequential read further optimization
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 220.127.116.11.0 EE/ AIX 7.2 / Oracle RAC 3 nodes