Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2004
    Location
    France
    Posts
    754

    Unanswered: Strange Parse to Execute ratio using REF CURSORS

    Hello everyone,

    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 :

    Code:
    PROCEDURE Proc1(
    		iParam1 in Number,
    		dParam2 in Date,
    		result in out rc,
    		iNbErreurs in out Number
    		) IS
    		
    	BEGIN
    		iNbErreurs := 0;
    		
    		open result for
    			SELECT  f1,f2
                            FROM table1
                            WHERE datefield > dParam2
                                      AND numberfield = iParam1;
    				
    		EXCEPTION
    			WHEN OTHERS THEN
    				iNbErreurs := 1;
    				-- Some error logging
    				
    	END Proc1;
    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,

    RBARAER

    Oracle Standard Edition 9.2.0.1 (development instance), Solaris 9
    Last edited by RBARAER; 02-28-05 at 14:13.

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Is it such a dumb question ?

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Probably best to time both solutions to see whether the oiverhead of a soft parse is significant. From Ask Tom:-

    REF CURSORS must be parsed each time -- by definition. They cannot be cached
    (they are potentially DIFFERENT with each execute!)

    search this site for session_cached_cursors, that'll help you.

    They are being SOFT PARSED, much better then a hard parse -- much worse then no
    parse or a session_cached_cursor hit.

    Alan

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    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 ?

    Regards,

    RBARAER

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •