COMMIT statement in oracle since ages marks the end of transaction and makes the changes permanent in the database.
We can use this statement as much we like in the code and it won't tell us it is wrong, it is perfectly legitimate.
Consider a 10 row insert operation I can have a for loop iterate insert one record at a time and commit it. or I can have all the records commited at one go after the end loop.
Which one is better?
Commit makes your changes permanent in the database in other words it has to write to physical storage, via sync write to a file, when a commit is issued. This will cause a process to wait for the I/O.
In simple words when we learn in our older days batch processing speeds up the tasks , the rule still stays committing 10 records in a batch is much faster than issuing a commit after every transaction.
Frequent commit can also lead to the dreadful ora 1555 snapshot too old error.
How :
Well commits basically releases the space in undo , it frees the space up in undo segment. This freeing up the space might look good , but it is not. It is very unacceptable for our long running queries. where the read consistent view at the point of time the query started may have been lost as the undo records are missing from the segment. So our query won't find the data it needs.
Summary perform commit only when your transaction ends i.e. you are done with all logical update delete inserts for the specific logical task you need to perform.
