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

Look at the number of hard and soft parses taking place for each run.

Daniel Morgan

Joern Dinkla wrote:

> 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 - 11:12:56 CST