Re: Slow PL/SQL bulk bind & forall performance. Is this a bug ?

Michael Carter wrote:

> I've been driven half mad trying to figure out what is happening with
> the following code.

<snipped>

Sorry. We only provide assistance for fully mad and certifiable insane Oracle DBA 's and developers. :-)

I hate reading other people's source code (comes from doing too much software maintenance and becomming qualified fully mad)... so I will not pretend that I really understand what you're doing in PL/SQL.

Just a few shots from the hip. Think about solving it in a single SQL statement. Yeah, I know this is not always possible. But what it does forces you to do is not to think about row-by-row data processing.

Think about doing as much pre-processing as possible. Even to the extend of using work tables (or temp tables as others like to call them). Again, working with tables means a shift in thinking from row processing. For example, doing a MINUS between two data sets is much faster than doing a "does it exist?" check in a PL/SQL or Pro*C loop.

Dry run the PL/SQL code using pencil and paper and pretend your are Oracle. You should quickly get the idea where the potential bottlenecks are.

Parallel processing. When dealing with masses of data and you need to process _all_ the data, then nothing but nothing on this earth will make it faster, but parallel processing. You can throw faster CPU's at it. You can throw more memory at it. You can index until you're blue in the face. Only parallel processing/multi threading, is the answer. In which case the extra memory and CPU will go just down nicely <burp>.

Okay, so how does this really help you with you specific problem? I'll be damned if I know. Just be glad I did not quote some of my Oracle poetry at you. :-)

--
Billy