CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle11goracle10g
Junaid
Junaid

Reputation: 1755

How often to I commit in Oracle

I am deleting a large amount of data from the a large Oracle database. The process I have followed is that I delete a record table A with causes CASCADE delete on table B which has CASCADE delete on some other table. So basically there are several tables related with each other with CASCADE delete.

Currently this process works in iteration for a number of records in table A and I only COMMIT at the very end of the iteration (when all data is deleted). The process takes around 30 hours to complete.

I have been suggested to have regular COMMIT, i.e. a COMMIT for each record deletion for table A (including deletion on any subsequent records in child tables).

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

Upvotes: 4

Views: 5936

Answers (1)

Justin Cave
Justin Cave

Reputation: 231781

Frequent commits would not be expected to improve the performance of your code. Doing a large number of interim commits could well slow down your code by forcing you to spend more time waiting on sync operations. And if you commit in the middle, you likely have to write quite a bit of code to ensure that your code is fully restartable.

Do you have an AWR or statspack snapshot or a trace file that shows what you're actually waiting on? 30 hours to do anything seems unreasonable. That would lead me to strongly suspect that you're missing some indexes that are causing your cascaded deletes to do full table scans every time a row is deleted. Fixing the missing indexes or doing multi-row deletes so that you have to do full table scans less frequently would seem far more likely to improve performance than worrying about when changes are committed.

Upvotes: 6

Related Questions