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
k_seb
Messages: 1
Registered: May 2007
Location: Poland
Junior Member
Hi,

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
AS
   ret sys_refcursor;
BEGIN
    [...]
/* returning data */
    OPEN ret FOR
     SELECT field1  FROM RD_RINGBACKREQUESTS
    WHERE (a1=par1 AND (a2 = par2 OR a3 = par3)) OR (a1=par1 AND a2 = par2) ORDER BY field1 ASC;
    RETURN ret;
END;


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...

greets.
Sebastian
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
rleishman
Messages: 3724
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: BOM Explosion package
Next Topic: Duplicate Rows
Goto Forum:
  


Current Time: Tue Dec 06 10:36:53 CST 2016

Total time taken to generate the page: 0.10530 seconds