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

You are replacing literals with bind variables. This can change the access path.

It is also not clear when you say 4000 rows you mean a single insert is inserting 4000 rows or the insert is being run 4000 times.

Joern Dinkla <joern.dinkla_at_promatis.de> wrote in message news:e7534c7b.0202210830.5361aa0c_at_posting.google.com...
> 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 - 16:55:09 CST