Adhoc query v. PL/SQL

Hi

I have a stored procedure as follows

CREATE PACKAGE MYTYPES AS
BEGIN
TYPE MYCUR IS REF CURSOR;
END; CREATE PROCEDURE SEL(CV IN OUT MYTYPES.MYCUR, parm#1 varchar2) AS BEGIN
OPEN CV FOR
SELECT * FROM A WHERE B = parm#1;
END; Now to the problem. There is no performance benefit in executing the procedure and fetching the cursor
compared to doing this using an adhoc query (SELECT * FROM A WHERE B = :1. At best the speed
is the same. If I run the procedure using SQL_TRACE = TRUE, I see that the contents of SEL get
parsed. Do stored procedures not get parsed once, when they get created ? Or do they get parsed
every time they get called ?

Can anyone offer an explanation as to why there is no performance benefit in using the stored procedure ?

(The Oracle version is 7.3.3 and statements are called from an OCI program).

thanks in advance

Richard Hennessy Received on Wed Aug 26 1998 - 04:03:12 CDT