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