At 12:27 PM 27/06/2006, Adam wrote:

>* Create an output parameter called recordsprocessed, increment it
>and suspend inside your for select loop.

No, not for this job. This is an executable SP. Its purpose is to
perform DML. We don't want to slow it down just to feed a progress
bar. It's a bit like my Nana, who always use to sew faster when the
thread was running out out. The supposed "gain" from getting output
during processing might provide a way for the application to display
progress but it doesn't do a single thing for the root cause of the
performance attenuation.

A big part of the performance hit here is occurring because the input
file is huge. Processing the entire file (up to half a million
records involved) in one call to the SP will cause the undo log to
build up and build up until the engine simply abandons it, i.e. all
that resource deprivation comes to naught. The undo log is a Good
Thing (TM) for a reasonable-sized batch, because it allows the engine
to clear out new records in case a rollback happens, thus avoiding
the survival of garbage when inserts are abandoned.

But if the undo log itself is abandoned by the engine because it has
consumed more than a reasonable amount of resource, there will be
garbage - a lot in this case. These inserts are already going into a
table that is pushing the limits, even *without* the gross extra garbage.

./heLen