Parsing - Refcursors Vs Normal Cursors

Learnt recently, Its all about parsing with Ref-cursors compared with Normal cursors. Here is the snippet of code to demonstrate that.

rajesh@10GR2> create table t_process
  2  as
  3  select rownum as key
  4  from all_objects
  5  where rownum <= 10;

Table created.

Elapsed: 00:00:00.14
rajesh@10GR2>

Now a procedure that uses Ref-cursors to get the result sets.

rajesh@10GR2> Create or replace procedure  P1(p_key in number)
  2  as
  3      x sys_refcursor;
  4  begin
  5      open x for
  6      select * from t_process RCur
  7      where key = p_key;
  8
  9      close X;
 10  exception
 11          when others then
 12              if x%isopen then
 13                  close x;
 14              end if;
 15          raise_application_error (-20458,sqlerrm);
 16  end;
 17  /

Procedure created.

Elapsed: 00:00:00.14
rajesh@10GR2>

Now a procedure that uses Normal ( parameter ) cursors to get the result sets.

rajesh@10GR2> Create or replace procedure  P2(p_key in number)
  2  as
  3      cursor c(p_in number) is
  4     select * from t_process Cur
  5     where key = p_in;
  6  begin
  7     open c(p_key);
  8
  9      close c;
 10  exception
 11          when others then
 12              if c%isopen then
 13                  close c;
 14              end if;
 15          raise_application_error (-20458,sqlerrm);
 16  end;
 17  /

Procedure created.

Elapsed: 00:00:00.06
rajesh@10GR2>

After calling the procedure P1, P2 from an Anonymous blocks like this

rajesh@10GR2> begin
  2     for i in 1..500
  3     loop
  4                p1(i);
  5                p2(i);
  6     end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:54.76
rajesh@10GR2>

Now Tkprof shows me

********************************************************************************
SELECT *
FROM
 T_PROCESS RCUR WHERE KEY = :B1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      500      0.00       0.01          0          0          0           0
Execute    500      0.07       0.06          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1000      0.07       0.08          0          0          0           0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 100     (recursive depth: 1)

********************************************************************************

SELECT *
FROM
 T_PROCESS CUR WHERE KEY = :B1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    500      0.09       0.07          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      501      0.09       0.07          0          0          0           0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 100     (recursive depth: 1)

********************************************************************************

A Ref-cursor is always parsed with each and every OPEN call - they are never cached.

If you are NOT returning the cursor to a client, you are NOT doing dynamic-sql then you should not use Ref-cursors.