How often do I take in Oracle

Question

I am removing a large amount of data from a large Oracle database. The process that I performed is that I delete the record table A with the reasons for deleting CASCADE in table B, which has CASCADE delete in another table. Thus, basically there are several tables related to each other with the removal of CASCADE.

Currently, this process works in iteration for several records in table A and I only COMMIT at the very end of the iteration (when all data is deleted). The process takes about 30 hours.

I was asked to have regular COMMIT, i.e. COMMIT for each deletion of an entry for table A (including deletion of any subsequent entries in child tables).

I know that regular commit will keep the undo log low, but is there any performance improvement with regular commits? Would I see an improvement in the time needed to complete the script?

Answer 1

Frequent commits would not expect to improve the performance of your code. Running a large number of intermediate commits can significantly slow down your code, forcing you to spend more time on synchronization operations. And if you commit in the middle, you will likely have to write quite a lot of code to make sure your code is completely reloaded.

AWR statspack , , ? 30 - . , , - , . , , , , , , .