We encountered the following phenomena with dynamic SQL insert-statements
using ORACLE 184.108.40.206 on DEC ALPHA VMS 7.1.
A table with aprox. 30 attributes, rowsize aprox. 160 bytes, 4 indexes
aprox 100 bytes.
A double loop like
for i in 1..100
for j in 1..100
INSERT INTO TABLE
is aprox. 3 TIMES FASTER
than a loop that isses a call to another package that uses DBMS_SQL to
insert the data via a dynamic statement.
To prevent the first question - OF COURSE the statement is PARSED only
once. All calls after the first one only use DBMS_SQL.BIND_VALUE and
What I found out so far is that the BIND-Statements (and there are 30 of
them) take a HELL LOT OF CPU - the dynamic SQL insert is CPU-bounded.
I wonder if this is the designed behaviour or if any of you ever
encountered a similar CPU-eating performance problem.
Do you have any clues for speeding up this dynamic PL/SQL stuff or do we
have to go back to OCI ???
Regards and thank in advance,