Does Dynamic SQL (DBMS_SQL) performance lacks?

Hi folks!

We encountered the following phenomena with dynamic SQL insert-statements using ORACLE 7.3.2.2 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 .EXECUTE 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,
Jez Kewler Received on Fri Jul 24 1998 - 02:37:27 CDT