Re: Optimising PL/SQL Script. Takes 2 days to run.

From: Vladimir M. Zakharychev <bob_at_dpsp-yes.com>
Date: Thu, 23 Jan 2003 10:33:35 +0300
Message-ID: <b0o5sp$5r0$1@babylon.agtel.net>

> I disagree with your suggestion about incremental commits.
> Can't think a better way to kill performance and
> generate ORA-01555.

Life is full of compromises... If there is no parallel activity on the server and if there is not enough undo space - incremental commits seem to be an adequate solution (especially minding that OP is on 9.2 and probably with automatic undo management.) But one has to invent an effective way to restart partially complete process in this case. If there's enough undo - do not commit until everything's done. But one has to bear with the fact that single error may cause the whole process to be rolled back and the whole lot of work already done prior to the failure will have to be redone.

By the way, one further speed improvement suggestion:

instead of doing seven consecutive inserts (or doing them in a loop, which seems the same to me), do them in a FORALL bulk-bind insert, like this:

TYPE dates_tab IS TABLE OF DATE;
-- initialize this once if start_date does not change ctr_tab dates_tab := dates_tab(start_date, start_date+1,

                                             start_date+2, start_date+3,
                                             start_date+4, start_date+5,
                                             start_date+6);

.....
FORALL ctr IN ctr_tab.FIRST..ctr_tab.LAST  insert into FORECAST

    values(this_item.item, item.location, ctr_tab(ctr),'0');

With FORALL, you have single insert that will insert seven rows, not seven inserts each inserting single row. Result: 6 less context switches and 6 less SQL DML statements per source cursor row.
Of course, single SQL statement for the whole process would do better, but if OP wants this in PL/SQL, FORALL may show significant increase in performance.

Corrections and additions welcome.

-- 
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.

Received on Thu Jan 23 2003 - 01:33:35 CST