Statements parsing from stored procedures

From: Shailesh <shailesh.saraff_at_gmail.com>
Date: 17 Apr 2006 06:58:13 -0700
Message-ID: <1145282293.701349.279860@z34g2000cwc.googlegroups.com>

I have one query related to subject, we did some trials, please let me know if my interpretatios are wrong.

Using Ref cursor in Oracle 10.1.0.2

CREATE OR REPLACE PACKAGE PkgTestRefPatient AS  TYPE PATIENTCUR IS REF CURSOR;
 PROCEDURE ProGetPatients (CurPat OUT PATIENTCUR); END;
/

CREATE OR REPLACE PACKAGE BODY PkgTestRefPatient AS

PROCEDURE ProGetPatients (CurPat OUT PATIENTCUR) IS BEGIN
    OPEN CurPat FOR SELECT *

                      FROM TblPatient WHERE ROWNUM <15;

END ProGetPatients;

END;
/

DECLARE

           CurPat PkgTestRefPatient.PATIENTCUR;
           rPat TblPatient%ROWTYPE;
BEGIN
           PkgTestRefPatient.ProGetPatients( CurPat);
          LOOP
           FETCH  CurPat INTO  rPat;
          EXIT WHEN CurPat%NOTFOUND;

dbms_output.put_line(rPat.Patientenid||'.....'||rPat.Name||'.........'||rPat.Vorn ame);

          END LOOP;
           CLOSE CurPat;

END;
/

Anonymous block used for retrieving records from cursor. When I executed this
block 3 times in trace I got following info....

DECLARE

           CurPat PkgTestRefPatient.PATIENTCUR;
           rPat TblPatient%ROWTYPE;
BEGIN
           PkgTestRefPatient.ProGetPatients( CurPat);
          LOOP
           FETCH  CurPat INTO  rPat;
          EXIT WHEN CurPat%NOTFOUND;

dbms_output.put_line(rPat.Patientenid||'.....'||rPat.Name||'.........'||rPat.Vorn ame);

          END LOOP;
           CLOSE CurPat;

END; call count cpu elapsed disk query current

    rows
------- ------ -------- ---------- ---------- ---------- ----------

---

Parse        3      0.05       0.06          0          0          0
       0
Execute      3      0.04       0.05          0          0          0
       3
Fetch        0      0.00       0.00          0          0          0
       0

------- ------ -------- ---------- ---------- ---------- ----------

---

total        6      0.09       0.11          0          0          0
       3

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 19

---

SELECT *
FROM
 TBLPATIENT WHERE ROWNUM < 15

call count cpu elapsed disk query current

    rows
------- ------ -------- ---------- ---------- ---------- ----------

---

Parse        3      0.01       0.02          0          0          0
       0
Execute      3      0.00       0.00          0          0          0
       0
Fetch       45      0.03       0.03          0         42         12
      42

------- ------ -------- ---------- ---------- ---------- ----------

---

total       51      0.04       0.05          0         42         12
      42

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 19 (recursive depth: 1)

Rows Row Source Operation

-------  ---------------------------------------------------
     14  COUNT STOPKEY
     14   TABLE ACCESS FULL TBLPATIENT

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

Why does query in stored procedure parse 3 times? I thought query gets parsed at
the time of compiling and storing of this objects in the database.

We are planning to use RefCursor in our .NET application to reduce parsing of
statemets, I thought query will be
Parse:0
Execute: 3
Fetch: 45

am I doing something wrong? Please let me know.

Thanks & Regards,

Shailesh Received on Mon Apr 17 2006 - 08:58:13 CDT