Statement parsing from stored procedures
Hello,
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
