This discussion is archived
7 Replies Latest reply: Jul 8, 2009 9:55 PM by Jonathan Lewis RSS

Value for FETCH in TKPROF is 2, though I executed the SQL only once

592124 Newbie
Currently Being Moderated
I am sure I am missing something very simple in here. I am executing just one simple SQL statement with trace on;

SQL:

Select * from app_lic_orders where ........

In TKPROF:

I am seeing a value of 2 for FETCH. The above query returns two records and the arraysize is set to 14. What am I missing...shouldn't see a value of 1 for Fetch instead.

Thanks
SS
  • 1. Re: Value for FETCH in TKPROF is 2, though I executed the SQL only once
    SatishKandi Guru
    Currently Being Moderated
    Which tool have you used for this operation?

    If it was not sql*plus, can you repeat the same from sql*plus and check the trace again?
  • 2. Re: Value for FETCH in TKPROF is 2, though I executed the SQL only once
    592124 Newbie
    Currently Being Moderated
    I did that using SQL*Plus...and I could consistently see similar TKPRof output:

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.00 0.15 0 953 0 0
    Execute 1 0.00 0.00 0 0 0 0
    Fetch 2 0.00 0.13 9 10 0 10
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 4 0.00 0.28 14 971 0 10
  • 3. Re: Value for FETCH in TKPROF is 2, though I executed the SQL only once
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    There is always one more roundtrip for client/server communication needs :
    SQL> select * from dual;
    
    
    Statistics
    ----------------------------------------------------------
             24  recursive calls
              0  db block gets
              7  consistent gets
              0  physical reads
              0  redo size
            202  bytes sent via SQL*Net to client
            276  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    SQL> select * from dual where 1=2;
    
    no rows selected
    
    
    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
              0  consistent gets
              0  physical reads
              0  redo size
            144  bytes sent via SQL*Net to client
            235  bytes received via SQL*Net from client
              1  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              0  rows processed
    
    SQL>
    Nicolas.
  • 4. Re: Value for FETCH in TKPROF is 2, though I executed the SQL only once
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    user589121 wrote:
    I am sure I am missing something very simple in here. I am executing just one simple SQL statement with trace on;

    SQL:

    Select * from app_lic_orders where ........

    In TKPROF:

    I am seeing a value of 2 for FETCH. The above query returns two records and the arraysize is set to 14. What am I missing...shouldn't see a value of 1 for Fetch instead.
    It's just an odd little quirk of the SQL*PLus implementation - the first fetch only collects one row. Here's the trace for a query that returns 50 rows with an arraysize of 25:
    =====================
    PARSING IN CURSOR #17 len=58 dep=0 uid=55 oct=3 lid=55 tim=123248450794 hv=1019831541 ad='6d497060'
    select owner from t1 where object_id between 2001 and 2050
    END OF STMT
    PARSE #17:c=15625,e=2318,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=123248450779
    EXEC #17:c=0,e=109,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=123248452050
    FETCH #17:c=0,e=113,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=1,tim=123248452425
    FETCH #17:c=0,e=152,p=0,cr=7,cu=0,mis=0,r=25,dep=0,og=1,tim=123248453349
    FETCH #17:c=0,e=237,p=0,cr=6,cu=0,mis=0,r=24,dep=0,og=1,tim=123248524923
    =====================
    Notice how the first fetch returns one row - leaving 24 for final fetch.

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk


    "Science is more than a body of knowledge; it is a way of thinking"
    Carl Sagan
  • 5. Re: Value for FETCH in TKPROF is 2, though I executed the SQL only once
    592124 Newbie
    Currently Being Moderated
    Jonathan,

    Thank you so much for the information and clarification. This clears the confusion, I had.

    SS
  • 6. Re: Value for FETCH in TKPROF is 2, though I executed the SQL only once
    640834 Newbie
    Currently Being Moderated
    Hi Jonathan,

    I found someting similar, please see: "Selecting CLOB column makes fetching one by one "
    Selecting CLOB column makes fetching one by one

    Any idea why can be happening?

    Thanks,

    Joaquin Gonzalez
  • 7. Re: Value for FETCH in TKPROF is 2, though I executed the SQL only once
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Joaquín González wrote:
    Hi Jonathan,

    I found someting similar, please see: "Selecting CLOB column makes fetching one by one "
    Selecting CLOB column makes fetching one by one

    Any idea why can be happening?
    I've added a comment to the original thread


    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    "Science is more than a body of knowledge; it is a way of thinking"
    Carl Sagan

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points