Performence Hit when using FORALL in parallel
713685Jul 24 2009 — edited Jul 24 2009Hi All,
I am using a BULK COLLECT - FORALL in order to INSERT large amount of data in a table. It works very well and the performance improvement is significant.
BUT when I try to parallelize the process for example 5 instances of the same procedure running in parallel for the same data the performance is not as good as it should be.
For example if one instance is processing the whole amount of data for 30 mins, five instances are processing the same amount of data for 45 mins ???
I started the tests with LIMIT 20000, but I thought that there is some king of locking for the FORALL statement (like APPEND hint), so I increased the LIMIT to 250000 in order to minimize the requests for acquiring the resource lock … No Change …
I could not find in the Oracle Documentation for FORALL Statement any description about exclusive locks, thus why I decided to ask the Experts.
Any Help will be highly appreciated.
Roger