5 Replies Latest reply: Jan 17, 2013 1:36 PM by Jeff Smith Sqldev Pm-Oracle RSS

    set autotrace on statistics in sqldeveloper (oracle 11g) is wrong

    Zpuit
      This concerns sql developer Version 3.2.09

      When I tried an example in sqldeveloper I got different statistics in sqldeveloper than in sqlplus. here just the crux..
       set autotrace on statistics
      
      insert /*+ append */ into gtt select * from all_objects;
      shows:
       71,772 rows inserted.
         Statistics
      -----------------------------------------------------------
                     3  user calls
                     0  physical read total multi block requests
                     0  physical read total bytes
               8380416  cell physical IO interconnect bytes
                     0  commit cleanout failures: block lost
                     0  IMU commits
                     0  IMU Flushes
                     0  IMU contention
                     0  IMU bind flushes
                     0  IMU mbu flush 
      while in sqllplus it shows (what I expected as well)
      71772 rows created.
      Statistics
      ----------------------------------------------------------
              255  recursive calls
             1040  db block gets
            47344  consistent gets
                0  physical reads
              256  redo size
              529  bytes sent via SQL*Net to client
              499  bytes received via SQL*Net from client
                3  SQL*Net roundtrips to/from client
             1496  sorts (memory)
                0  sorts (disk)
            71772  rows processed
      seems that the statistics shown in sqldeveloper are based on the oracle 10 v$statname ids

      here the banner from v$version
      Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
      PL/SQL Release 11.2.0.2.0 - Production
      "CORE     11.2.0.2.0     Production"
      TNS for Linux: Version 11.2.0.2.0 - Production
      NLSRTL Version 11.2.0.2.0 - Production
        • 1. Re: set autotrace on statistics in sqldeveloper (oracle 11g) is wrong
          Ric Van Dyke
          This is certainly a major problem. The issue is rather simple, SQL Developer is only retrieving as many rows as are specified in the preferences "SQL Array Fetch Size" (under preferences, database, advanced). A simple query on a table with 10,000 rows will only give stats on at most a retrieval of 500 rows since this is the max setting for the array fetch size. I'm not buying the argument that we don't want to wait for the entire result set. I suppose if your are only interested in seeing if the query is syntactically correct then not waiting for the entire set is fine. But the whole idea of AUTO TRACE is to see the statistics of the entire run so you can know what resources were used to compete the SQL. Seeing only the resources use to get the first set of rows is very misleading. And if you change the array fetch size to be smaller, your SQL will appear to run "better" when in reality you don't know who well it's running at all.

          We need to be able to have AUTO TRACE retrieve all rows, not just the setting of array fetch size. There is currently no way to get AUTO TRACE to show the stats of a compete run.
          • 2. Re: set autotrace on statistics in sqldeveloper (oracle 11g) is wrong
            Jeff Smith Sqldev Pm-Oracle
            As a workaround you can use the AutoTrace button in the worksheet toolbar. It will retrieve all stats plus a plan.
            • 3. Re: set autotrace on statistics in sqldeveloper (oracle 11g) is wrong
              Ric Van Dyke
              Jeff the problem there is that those stats are only based on a run which retrieves up to ARRAY FETCH SIZE. Querying from from a set of tables that returns more then 500 rows (which is the max for array size) will give you less then accurate results. For example I run this query with the auto trace feature "select * from aphys1", the table has 10,000 rows. How ever the plan in the output shows for LAST OUTPUTTED ROWS only 500.

              Also these stats that are shown are rather worthless for optimizing a query. Regardless of the values shown, what do those stats really tell me about how well my query ran? What pearls of wisdom can I gain from those stats? What does "commit cleanout failures: block lost" really tell me? And what can I do about that? I know what a block clean out is and it's not something I have any control over.

              This shows me only 10 stats out of 600 and these are hardly the top 10 stats I'd like to know about. The ones in SQLPlus are far more useful, and I'd like to add "buffer is pinned count" to the list of ones from SQLPlus.
              • 4. Re: set autotrace on statistics in sqldeveloper (oracle 11g) is wrong
                Jeff Smith Sqldev Pm-Oracle
                I'm confused - when you execute via F5 - we fetch all rows. How are you executing your query?

                'all rows' - up until the max as defined in the preferences for script output

                Edited by: Jeff Smith SQLDev PM on Jan 16, 2013 5:06 PM
                • 5. Re: set autotrace on statistics in sqldeveloper (oracle 11g) is wrong
                  Jeff Smith Sqldev Pm-Oracle
                  We we're pulling the wrong metrics. Going forward, we will pull ALL non-zero perf metrics when running autotrace via script execution in your example.