Statement parsing from stored procedures
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Statement parsing from stored procedures

  1. #1
    Join Date
    Jul 2000
    Location
    Pune, India
    Posts
    80

    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

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Can you post the trace file?

    I suspect beacuse of rownum pseudo column usage.

    Tamil

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Hi

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

  4. #4
    Join Date
    Jul 2000
    Location
    Pune, India
    Posts
    80
    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
    Attached Files Attached Files
    • File Type: txt 3.txt (8.1 KB, 88 views)

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width