Statement parsing from stored procedures

[shailesh]

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

[tamilselvan]

Can you post the trace file?

I suspect beacuse of rownum pseudo column usage.

Tamil

[pando]

Hi

Can you check if it's soft parse or hard? I suspect they are soft parses, Ref Cursor needs to be soft parsed.

[shailesh]

Hello,

Please find an attached tkprof file of related sql trace, please check and suggest if I am doing anything wrong.

Can Pl/Sql table will stop reparsing?

Thanks & Regards,

Shailesh

[tamilselvan]

Ref cursor and dynamic sql are soft parsed every time they are called.

I said post the trace data file not the tkprof output.

Tamil