Differences in performance of an INSERT-statement in SQL and PL/SQL

Hi,

I stumbled into the following on Oracle 8.1.6.0.0.

The following statement

INSERT INTO table
SELECT bla
FROM ...
WHERE currency = 'EUR' and period = '01-01' and sid = 1

takes about 320 seconds for a table with 4000 rows and 15 columns when called from sqlplus or sql navigator.

Ok, but when put it into a pl/sql package and abstract some parameters

FOR rec IN cur_rec
LOOP
    INSERT INTO table
    SELECT bla
    FROM ...
    WHERE currency = rec.currency and period = rec.period and sid = rec.sid END LOOP; and call it the insert-statement takes 500 seconds for the same table.

What could be the cause for the difference of 180 seconds ?

This may be a faq, but i did not find an answer on orafaq or with a search machine.

Regards,

Joern

Joern Dinkla
Hamburg, Germany
http://dinkla.net Received on Thu Feb 21 2002 - 10:30:31 CST