Home » SQL & PL/SQL » SQL & PL/SQL » parse count=execution with cursor ref
parse count=execution with cursor ref [message #240970] Mon, 28 May 2007 02:04 Go to next message
Messages: 1
Registered: May 2007
Location: Poland
Junior Member

While investigating application performance I found that stored procedure returning ref cursor performs with parse count equals to executions (tkprof).

create or replace FUNCTION getQuery
   par1 IN VARCHAR2,
   par2 IN VARCHAR2,
   par3 IN VARCHAR2,
RETURN sys_refcursor
   ret sys_refcursor;
/* returning data */
    OPEN ret FOR
    WHERE (a1=par1 AND (a2 = par2 OR a3 = par3)) OR (a1=par1 AND a2 = par2) ORDER BY field1 ASC;
    RETURN ret;

tkprof output for SELECT query:

call     count       cpu    elapsed       disk      query    current rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      200      0.01       0.01          0          0          0 0
Execute    200      0.02       0.02          0          0          0 0
Fetch        0      0.00       0.00          0          0          0 0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      400      0.04       0.03          0          0          0 0

I couldn't find answer why.. Please, exmplain me why...

Re: parse count=execution with cursor ref [message #240992 is a reply to message #240970] Mon, 28 May 2007 02:54 Go to previous message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
This one's been covered a few times - there is a thread on AskTom that often gets referenced in the answer.

From memory, I think the common respose is "thats the way ref cursors work". It's actually more of a "soft parse" than a "hard parse" - ie. subsequent parses will find the previous parse and piggy-back off it. So it's not really as bad as it looks.

It kind of makes sense: when you OPEN and CLOSE a declared cursor, you are reusing the same cursor declaration in memory; you have to CLOSE it if you want to OPEN it again. When you OPEN a ref cursor, you are creating new memory structures each time, because it is possible to OPEN many identical ref cursors using different cursor variables.

Ross Leishman
Previous Topic: Duplicate Rows
Next Topic: Create External table using Execute Immediate
Goto Forum:

Current Time: Tue Aug 22 04:28:01 CDT 2017

Total time taken to generate the page: 0.04810 seconds