Using Autotrace

Using SQLPLUS autotrace.

Autotrace is a feature of SQLPLUS and can be used to report on DML statistics and explain plans to help diagnose SQL performance issues.

Autotrace has a number of different levels which can be turned on before issuing a DML statement. The following table details the different options and syntax for the command.

SYNTAX DESCRIPTION
set autotrace on explain Display the execution plan
set autotrace on statistics Displays execution statistics
set autotrace on Displays both execution plan and execution statistics
set autotrace traceonly Stops the query output from being printed.
set autotrace traceonly explain Shows the execution plan, but does not execute the query
set autotrace traceonly statistics Shows the execution statistics. Note: This runs the query, but does not display the results.
set autotrace off Turns autotrace off
set autotrace off explain Turns off execution plan reporting
set autotrace off statistics Turns off execution statistics reporting

When autotrace is enabled with statistics, then the following statistics are displayed

  • recursive calls ~ Number of recursive calls generated at both the user and system level. i.e. SQL generated internally by Oracle to satisfy the user SQL.
  • db block gets ~ Number of block gets. i.e. The most up-to-date copy of the data block.
  • consistent gets ~ Number of consistent gets. i.e. A block which is consistent with a given point in time, or SCN.
  • physical reads ~ Total number of data blocks read from disk. This number equals the value of "physical reads direct" plus all reads into buffer cache.
  • redo size ~ Amount of REDO generated. (Bytes).
  • bytes sent via SQL*Net to client ~ Total number of bytes sent to the client from the foreground processes.
  • bytes received via SQL*Net from client ~ Total number of bytes received from the client.
  • SQL*Net roundtrips to/from client ~ Total number of Oracle Net messages sent to and received from the client.
  • sorts (memory) ~ Number of sort operations performed in memory.
  • sorts (disk) ~ Number of sort operations performed on disk.
  • rows processed ~ Number of rows processed during the operation.

Example

The following output shows an example of autotrace in operation

sqlplus '/ as sysdba'
SQL> set autotrace on statistics
SQL> delete from mytable where rownum < 1000;

999 rows deleted.

Statistics
----------------------------------------------------------
          1  recursive calls
       1173  db block gets
         34  consistent gets
         31  physical reads
     589496  redo size
        822  bytes sent via SQL*Net to client
        730  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        999  rows processed
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License