Forum Stats

  • 3,750,080 Users
  • 2,250,104 Discussions
  • 7,866,770 Comments

Discussions

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

2

Answers

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,568 Red Diamond

    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.

  • OraC
    OraC Member Posts: 70 Blue Ribbon

    Thanks @Billy Verreynne

    What about this:

     https://docs.oracle.com/en/database/oracle/oracle-database/12.2/vldbg/types-parallelism.html it states:

    "For non-partitioned tables with LOB columns, parallel INSERT operations are supported provided that the LOB columns are declared as SecureFiles LOBs. Parallel UPDATEDELETE, and MERGE operations on such tables are not supported."

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,729 Gold Crown

    I'm still trying to find out the optimal way to do both for comparison purposes.

    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

  • OraC
    OraC Member Posts: 70 Blue Ribbon

    Thank you @Jonathan Lewis

    I'm running on AWS Oracle RDS and using Performance Insights I've already been able to determine that during the deletion the time is spent in CPU and db file sequential read, mostly the latter.

    I have 8 indexes on this table:

    6 single column indexes (2 of which are there for foreign key constraint links)

    1 two column index

    1 three column unique index (unique constraint with using index clause)

    In Oracle RDS a log switch happens automatically every 5 minutes (this cannot be changed because of AWS recovery restrictions) and I was finding that logs were switching a lot more often than that. I have now sized my 12 redo log files to be 4GB each in size and the log switch happens roughly every 5 minutes now, with the occasional 5 minute period where there is more than 1 switch. With this amount of log switching, what should my UNDO tablespace size be?

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,729 Gold Crown
    edited Jan 31, 2021 7:38PM


    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.

    OraC
  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,568 Red Diamond

    DBMS_PARALLEL_EXECUTE is a method allowing you to "multi-thread" your SQL and/or PL/SQL code, with "threads" executed as DBMS_SCHEDULER background processes.

    Oracle Parallel Query (PQ) is not used and is not relevant in this case.

    However, as using this approach will simulate (as non-PQ non single transaction processes) what PQ does - deleting securefile LOB rows from the same table at the same time - this approach could run into securefile related cross process contention.

    OraC
  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,568 Red Diamond

    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.

    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.

    OraC
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,729 Gold Crown


    For a bit of background reading on "massive deletes", there's a short series I wrote for redgate, and a few articles on my blog that cover quite a lot of ground. Catalogued here: https://jonathanlewis.wordpress.com/2018/06/08/massive-delete/


    One very specific point in your case, that I overlooked in my first response: you have a LOB column, but the resources needed to deal with that column will vary greatly depending on whether the actual value is (frequently) stored in-row or out of row. If you have allowed in-row and a lot of your LOB values are less than the (approx) 4,000 byte limit then deleting them will generate a lot of undo and redo because they are simply part of a standard row delete. Moreover if the LOB values are in-row and subject to update you may find that a lot of the rows have ended up being migrated, leading to a lot more random I/O to take you from the original location of the row to the migrated row. (Keep an eye on the session activity statistic "table fetch continued rows" when testing.)


    Regards

    Jonathan Lewis

    OraC
  • OraC
    OraC Member Posts: 70 Blue Ribbon
    edited Feb 8, 2021 7:03PM

    Thank you all so much for all your suggestions... Its really appreciated!

    So there have been a few changes since and we're now testing the delete from created_dtm directly. 

    We now have some additional search criteria. The delete from ITD now has to include specific values from another table - SPECS so the delete looks like this now:


    delete from ITD 

    where DATA_ID in (select DATA_ID from SPECS where SERVICE_ID = 9) 

    and CREATED < '27-Nov-2020 12:00:00' 

    and rownum <= 10,000;


    The SPECS table only has 78 records of which 24 match the SERVICE_ID of 9

    DATA_ID is NUMBER(20)

    SERVICE_ID is NUMBER(20)


    Even though there are potentially 24 different DATA_IDs for SERVICE_ID 9, only 3 are currently used in this installation - DATA_IDs (56, 76, 77) 


    ITD_CIDS_7 is an index on ITD - SERVICE_ID and CREATED

    A_SPECS_U1 is a unique key constraint index on SPECS - SERVICE_ID and another column. 

    SERVICE_ID is indexed separately but its not being used. 


    @Jonathan Lewis Thank you for posting your Redgate article. There is a lot of useful information in there! Just to let you know the tricky BLOB situation we have:

    There is a single securefile BLOB_VALUE column in the ITD table which unfortunately holds the data from a number of different data elements, each one identified by the values returned from the (select DATA_ID from SPECS where SERVICE_ID = 9) subquery.


    The 3 DATA_IDs and corresponding length(BLOB_VALUE) sizes are as follows:

    DATA_ID - 56: typically either 784 or 880 bytes, number of rows around 270m

    DATA_ID - 76: typically either 3440 or 4384 bytes, number of rows around 270m

    DATA_ID - 77: typically either 3440 or 4384 bytes, number of rows around 270m

    so theres a real mix of blobs being stored inline and out of line.

    We are introducing compresssion in the application that will half the length of these elements so that will definitely help longer term.


    ITD table now has 835 million rows.

    Average row length from this table is 2232.

    BLOB_VALUE is defined as: 

    BLOB_VALUE BLOB

    lob (BLOB_VALUE) store as securefile BLOBSEG_ITD_BLOBVAL

    Each transaction inserts one row for each of the 3 DATA_ID elements above. So we have approximately 270 million transactions in the table at the moment. 270 x 3 rows = 810 million rows

    The remaining BLOB_VALUEs in the table are a mixture of other entries which don't ever get deleted and they will be inserted as other transactions are happening in the system. 

    Any discussions around breaking this table up will have to come later as that would require a big re-engineering project.


    The execution plan now looks like:

    ----------------------------------------------------------------------------------------------------------------

    | Id | Operation               | Name         | Rows | Bytes | Cost (%CPU)| Time   |

    |  0 | DELETE STATEMENT            |            |    |    | 1152K(100)|     |

    |  1 | DELETE                | ITD     |    |    |      |     |

    |* 2 |  COUNT STOPKEY            |            |    |    |      |     |

    |* 3 |  HASH JOIN              |            |  188M|  12G| 1152K (1)| 00:00:46 |

    |  4 |   TABLE ACCESS BY INDEX ROWID BATCHED| SPECS         |  24 |  192 |   2  (0)| 00:00:01 |

    |* 5 |   INDEX RANGE SCAN         | A_SPECS_U1      |  24 |    |   1  (0)| 00:00:01 |

    |* 6 |   INDEX FAST FULL SCAN        | ITD_CIDS_7      |  188M|  10G| 1151K (1)| 00:00:45 |

    ----------------------------------------------------------------------------------------------------------------


    Predicate Information (identified by operation id):

      2 - filter(ROWNUM<=:3)

      3 - access("DATA_ID"="DATA_ID")

      5 - access("SERVICE_ID"=:1)

      6 - filter("CREATED"<:2)


    Is there much else that can be done here?

    Also, after running a daily delete of 3 million transactions, (9 million ITD rows) how do we run statistics efficiently without it impacting normal user transactions when we are out of our maintenance window? Should it be run across the entire table with cascade true or should we only run stats manually for the ITD_CIDS_7 index?


    Thanks again!

    OraC

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,729 Gold Crown

    we're now testing the delete from created_dtm directly.

    You seem to have referred to this column as CREATED elsewhere - are there two different columns, one with and one without a time component ? You've also mentioned "created_on", and you've compared "created" with a literal that looks like a timestamp. Could you please give us a definitive statement of what you actually have..

    ITD_CIDS_7 is an index on ITD - SERVICE_ID and CREATED

    A_SPECS_U1 is a unique key constraint index on SPECS - SERVICE_ID and another column. 

    SERVICE_ID is indexed separately but its not being used.

    Another problem with your descriptions - ITD_CIDS_7 is on the ITD table - SERVICE_ID is a column on the SPECS table. Did you mean (data_id, created) for ITD_CIDS_7? Since nearly 100% of the data is evenly spread between 3 values of data_id then that's a bad choice of index - especially if a process inserts three rows at the same time to cover all three data_ids. However you may be have reduced the buffer busy waits on inserts by creating that index. However you've also said that (created) is separately indexed anyway which means you need to think carefully about your whole indexing strategy.

    Regards

    Jonathan Lewis


    PS I would say more but the forum softtware is behaving so badly at the moment that it's a major disincentive to continue.

    If you want further strategy suggestions please supply a list of your index definitions, and clarify what "created" really is.