Search code examples
sqloracle-databaseoracle11goracle-sqldeveloper

I can't print statistics using Autotrace Traceonly Oracle

I would like to print the execution statistics of sql queries. For this I want to use autotrace. When I use autotrace on explain, I have no problems, it prints everything correctly. However, when I want to use autotrace traceonly, the program says that "is not supported" . My user has all the privileges ... what's the problem then?

set autotrace traceonly;
set timing on;

-- Here i have my 4 Querys

set timing off;

Result:

Traceonly option is currently not supported.

I also want to print something like this:

Image

Whats the command for printing the consisting gets, like in my image?

Any ideas?

I'm using SQL Developer and Oracle 11-G

Solution

  • Update: Just upgrade your copy of SQL Developer (22.2.1 is latest)

    Here is a demo using command line version of SQLDev (SQLcl). Same code would work in SQL Developer's SQL Worksheet with F5.

    SQL> set autotrace traceonly;
Autotrace TraceOnly
 Exhibits the performance statistics with silent query output
SQL> set timing on;
SQL> select 1 from dual;

1 row selected.


PLAN_TABLE_OUTPUT
_____________________________________________
SQL_ID  520mkxqpf15q8, child number 0
-------------------------------------
select 1 from dual

Plan hash value: 1388734953

------------------------------------------
| Id  | Operation        | Name | E-Rows |
------------------------------------------
|   0 | SELECT STATEMENT |      |        |
|   1 |  FAST DUAL       |      |      1 |
------------------------------------------

Note

PLAN_TABLE_OUTPUT
____________________________________________________________________________________
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level


Statistics
-----------------------------------------------------------
               1  CPU used by this session
               1  CPU used when call started
               1  DB time
              42  Requests to/from client
              42  SQL*Net roundtrips to/from client
             573  bytes received via SQL*Net from client
           82982  bytes sent via SQL*Net to client
               2  calls to get snapshot scn: kcmgss
               2  calls to kcmgcs
               2  execute count
              43  non-idle wait count
               2  opened cursors cumulative
               1  opened cursors current
               2  parse count (total)
               1  session cursor cache hits
               1  sorts (memory)
            2010  sorts (rows)
              43  user calls
Elapsed: 00:00:00.058
SQL>

    If you are using an older copy, then -

    You will need to use SQL*Plus.

    [oracle@vbgeneric ~]$ sqlplus demo/oracle

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 29 16:31:12 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Sat Apr 29 2017 09:03:35 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> set autotrace traceonly;
set timing on;

select 1 from dual;
select 2 from dual;
select 3 from dual;
select 4 from dual;

set timing off;SQL> SQL> SQL> 
Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation    | Name | Rows  | Cost (%CPU)| Time|
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |  |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL   |  |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------


Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      0  consistent gets
      0  physical reads
      0  redo size
    535  bytes sent via SQL*Net to client
    552  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

SQL> 
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation    | Name | Rows  | Cost (%CPU)| Time|
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |  |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL   |  |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------


Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      0  consistent gets
      0  physical reads
      0  redo size
    535  bytes sent via SQL*Net to client
    552  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

SQL> 
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation    | Name | Rows  | Cost (%CPU)| Time|
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |  |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL   |  |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------


Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      0  consistent gets
      0  physical reads
      0  redo size
    535  bytes sent via SQL*Net to client
    552  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

SQL> 
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation    | Name | Rows  | Cost (%CPU)| Time|
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |  |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL   |  |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------


Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      0  consistent gets
      0  physical reads
      0  redo size
    535  bytes sent via SQL*Net to client
    552  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

SQL> SQL>

    Disclaimer: I work for Oracle and am the product manager for SQL Developer.