Unanswered: Strange Parse to Execute ratio using REF CURSORS
I'm experiencing something strange. I had queries (selects) with bind variables in my OCCI app, and when looking at v$sql I had PARSE_CALLS=1, EXECUTIONS=n (n>=1). Now I decided to put all these queries inside PLSQL stored procedures returning REF CURSORs. Basically I have a REF CURSOR rc (type defined in the package, I could use SYS_REFCURSOR) as IN/OUT parameter and just do an OPEN rc FOR SELECT...; in my procedure. Now what I get is PC=1 / EXEC=n for the stored proc calls (using bind variables, normal), but PC=n / EXEC=n for the SQL within the stored proc... Here is an example :
iParam1 in Number,
dParam2 in Date,
result in out rc,
iNbErreurs in out Number
iNbErreurs := 0;
open result for
WHERE datefield > dParam2
AND numberfield = iParam1;
WHEN OTHERS THEN
iNbErreurs := 1;
-- Some error logging
PC=1 / EXEC = N for "BEGIN Proc1(:1,:2,:3,:4); END;"
PC=N / EXEC = N for "SELECT f1,f2 FROM table1 WHERE datefield > :b1 AND numberfield = :b2"
Is that normal ? Shouldn't it be 1/N too ? Am I missing something ?
Thanks & Regards,
Oracle Standard Edition 18.104.22.168 (development instance), Solaris 9
Thanks Alan, I see what I missed. It's not always so easy to have everything clear in mind... So with session_cached_cursors, I'll do "softer soft parses" (as Tom Kyte calls them) instead of "soft parses", is that right ?