Fastest way to batch delete data from a table with 1 billion rows

Introducing PL/SQL code to process the output of a SQL cursor, introduces an overhead. A lot of cursor looping just moves data from the cursor to another cursor - and this is mostly stupid.

You can control parallel execution via the DBMS_PARALLEL interface - see https://community.oracle.com/tech/developers/discussion/comment/13727146#Comment_13727146 for an example.

That said, deleting LOB segments in parallel could introduce wait event latency and slow this parallel approach down.

There is no generic answer to the question of "how to delete a lot of data"; but there are factors that point you towards the right tests.

Key features in your case

• you're on standard edition so no question of on-line restructuring or partitioning
• you want to delete about 1% of the data, every day
• you have a (securefile) LOB column
• your target data is time-dependent (created more than ca, 3 months ago).

On the plus side (and assuming no disruption because of historical activity),, the 9M rows you want to delete will be very well clustered in a set of consecutive blocks in the table, they won't scattered all over the table. (That's implied by the "created date" column that you're using for the delete.)

One of the problems you might have seen with your first code sample - parallel select of rowids - is that the batch of rows you deleted might have been fed in smaller batches from the 4 parallel server processes which could mean that the query co-ordinator was having to jump around the section of the table for that day a little randomly - leading to extra work on buffer latches, consistent gets and current gets. The side effects of using the dbms_parallel_execute package (which is not the same as executing a single statement with a set of parallel servers processes) might make the side effects of parallelism even worse.

Key point to investigate: where does the time go when you try each method. To determine this in Standard Edition you could try taking a statspack snapshot just before and just after running a smaller delete cycle when there is little other activity; alternatively just take a snapshot of either the session or system level activity and wait events before and after the run (v$sesstat / v$sesstat for the session) and v$system_event / v$session_event.

A few possibilities are:

• CPU
• Waiting for log file writes to complete
• db file sequential reads - possible of randomly scattered index leaf blocks if you have several indexes
• db file sequential reads - of undo blocks if you're having read-consistency problems
• enqueue waits - if there are collisions between processes doing updates and your delete

The CPU time is something you can from the session/system activity; the other from the session/system events. The underlying cause of time spent in the events may be something that can be inferred from the session/system activity (e.g. from the stats named like '%undo records applied"). If necessary you could get extra detail from other dynamic performance views, or enable extended SQL trace for a run.

Regards

Jonathan Lewis

Good move with the 4GB redo logs (assuming it cost very little cash - you might want to check the archived log file size if space is a consideration).

One option to bear in mind is that if you drive through an index in ascending order to delete rows Oracle can optimize the index maintenance - it will track the rows (and values) deleted then do a sort and then do array updates into each index in turn to delete the index entries. This can make a big difference to the volume of random I/O, undo and redo (because each leaf block updated is visited just once and your data may mean many rows are deleted from each leaf block as a single undo/redo entry).

If your delete is driven by tablescan oracle will update each index as it deletes each row.

There are some exclusions to the array update mechanism - your two indexes supporting the foreign key indexes will be updated row by row.

Since you have a "created" index you may find a process that does something like the following is as efficient as you can get:

delete /*+ index_rs_asc(a (created)) */  
from itd a 
where created < '27-Nov-2020 12:00:00' 
and rownum < 50000

You'd need to embedd this in a PL/SQL loop that cycles until the number of rows deleted is zero. (obviously you might need to experiment to find an optimum number of rows to delete in one pass, remembering that you'll be sorting 6N keys in memory if you set the number to N.

You'd have to check that the execution path was an index range scan; and you might find that the cost of walking through the index you'd deleted on all the previous passess might add undesirable overheads (which you might avoid by coding the PL/SQL to start where it had last left off (or one second before).

One though - if either of your multiple column indexes starts with the same column eiather of as the foriegn key indexes than the foreign key index is redundant and could be dropped.

Regards

Jonathan Lewis

Footnote: https://jonathanlewis.wordpress.com/2006/11/22/tuning-updates/ is a note I wrote a long time ago about updating through indexes, with a footnote that this applies also to deletes.

delete /*+ index_rs_asc(a (created)) */  
from itd a 
where created < '27-Nov-2020 12:00:00' 
and rownum < 50000

This code also needs to support instrumentation, support restarting failed loop processing, etc.

Rather run this as single "threaded" process via DBMS_PARALLEL_EXECUTE, making use of its instrumentation, transaction, and recovery features.