Performance of Multiple Inserts

A friend of mine has written a simple Oracle program that he thinks should run in a couple of hours, that now appears to need 48 days to complete! I offered to ask for advice here....

The program is a simple load utility, loading a lot of payroll data into a database. The program is generating test data, so it just does inserts. At present, it COMMITs after every insert. In an effort to speed things up, the program was run simultaneously from four clients accessing the same server, and there was about 3 x speedup, but we are still talking about 16 days to build our test database!

A couple of questions--

1. I suspected that the program might run faster if it committed about a whole pageful of data and then COMMITted, rather than issuing a COMMIT after each INSERT. What do you think?
2. Would that same strategy help for the multiple-client case? Are the multiple clients each going to work with the same page of data, hence have to wait on each other, or will Oracle give each of them a separate page?

Thanks

Dave Roberts Received on Thu Aug 10 1995 - 00:00:00 CEST