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

user591200

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

Jonathan Lewis

There may be better options than improving the performance of SQL.

First, which version of Oracle are you on?

Secondly, if you're ultimately going to delete 130M rows how many rows will be left at the end of the delete ?

As collateral questions: If you're after "sysdate - 90" does this mean you're doing a one-off big delete and will be doing a daily (or maybe weekly) repeat thereafter? How many indexes will have to be maintained as the data is deleted (that's probably where most of your cluster waits and db file sequential reads are coming from).

If it's a one-off big delete and if you're on 12.2 then you might want to read about the option to do an online rebuild of a table (and its indexes) "including" only the rows you want to keep. (This might not be appropriate for subsequent "delete 1 day out of 3 months). There's a note here that I wrote about the option.

Regards

Jonathan Lewis

user591200

Hello @Jonathan Lewis ,

This will be a on going job, running daily to clean this table up. It is a pretty busy table.

The retained rows are also similar , kind of close to 127 Million rows.

My idea was simply to run this for 2 hrs max in a given batch cycle or till no more deletes.

loop

delete from t1 where rownum < 40000 and created_date <= (sysdate - 90);

more := SQL%ROWCOUNT;

commit;

exit when more = 0 and sysdate < start_time + 1/24/60 * 120;

end loop

Could you please point me to the note that you have highlighted. (I am guessing it is related to alter table move online option )

1:22 pm : https://jonathanlewis.wordpress.com/2020/07/12/massive-deletes/ is this the one ?

Jonathan Lewis

You've found the correct note - I've added the link into my previous post now, I don't know what I did wrong the first time around that lost it.

If you're going to have abaout 127M rows left after the delete (i.e. 50/50 delete/keep) it would make sense to consider using the "online move including" strategy for the first big delete. I assume the remaining data would then be roughly 1.4M rows per day (maybe ranging between 0.9 and 2.0M) per day, so the on-going strategy might be a daily delete as a single index-driven statement, or a weekly move at the quietest time of the week.

The main drawback to running a loop for the first big delete is that you can get caught in a read-consistency trap where each cycle of the loop spends more and more time re-reading the undo segments to find out whether the data deleted in the previous cycles has really been deleted or whether the data blocks need to be made read-consistent to check what really exists to be deleted.

A secondary consideration (and one which I will have to check if I've tested 12.2) is that if you delete using an access path that uses an index range scan then there is (or used to be - see this note) an array based optimisation that would result in a lot less undo and redo for big updates and deletes.

Another thought to experiment with is that if you have a suitable index to drive the delete (and you may have to hint the path) you can avoid some of the cost of walking through a load of deleted index entries that gets bigger and bigger each time you go round the loop.

In 12.2 you can use a RETURNING clause, even in SQL*Plus, e.g. (where t1 is a table created from all_objects with an index on object_id) here's a cut-and-paste:

SQL> variable n1 number

SQL> delete /*+ index(t1) */ from t1 where rownum <= 1000 returning max(object_id) into :n1;

1000 rows deleted.

SQL> print n1

        N1
----------
      3390

SQL> delete /*+ index(t1) */ from t1 where object_id >= :n1 and rownum <= 1000 returning max(object_id) into :n1;

1000 rows deleted.

SQL> print n1;

        N1
----------
      5049

Returning into the same variable I used in the predicate is probably a bad idea, of course, I was just being lazy.

Regards

Jonathan Lewis