This discussion is archived
5 Replies Latest reply: Jan 17, 2013 11:36 AM by Jeff Smith SQLDev PM RSS

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

Zpuit Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 ACE Moderator
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 ACE Moderator
    Currently Being Moderated
    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 ACE Moderator
    Currently Being Moderated
    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.

Legend

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