7 Replies Latest reply: Jul 8, 2009 11:55 PM by Jonathan Lewis RSS

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

    592124
      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
          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
            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
              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
                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
                  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
                    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
                      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