Big PL/SQL Performance Problems

Hi

I have a problem with PL/SQL performance when calling PL/SQL procedures from an OCI program.
The bottom line is if I have an SQL statement it is faster to parse and execute the statement as a dynamic
statement than to execute a PL/SQL procedure containing the same statement. My code is based on the
cdemo5.c sample program. I ran a trace (SQL_TRACE = TRUE and TIMED_STATISTICS= TRUE)
and when I looked at the trace file I was very surprised. Assume I have the following statement
DELETE FROM A WHERE B = :1 and a stored procedure (DEL_1) containing this statement.

In the trace file I see PARSE calls for BEGIN DEL_1('?'); END; which is what I expect as this comes
from my program but later on I see PARSE and EXEC calls for DELETE FROM A WHERE B = :b1. !!!

This is not what I expected, I thought the parsing (of the DELETE) was done once when the stored procedure
was created and not every time I execute the procedure. Looking at the trace it is clear why my program
runs longer when using procedures that using dynamic SQL. Am I missing out on something ? Are there
relevant INIT.ORA parameters of OCI functions that are not in cdemo5.c ?

Secondly, is there a limit to the number of PL/SQL procedure I can run in parallel - say a nested loop
of calls to PL/SQL procedure (each returning reference cursors) about 5 levels deep. I ask this because
if I try to run more that 4 PL/SQL procedures and keep separate cursors for each one, I get the following
errors when executing the fifth one, ORA-01001 and ORA-00816. Are there parameters in INIT.ORA
which might influence this ?

Any help on either of these issues would be greatly appreciated.

regards

Richard Hennessy Received on Tue Aug 18 1998 - 04:16:03 CDT