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.
|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.
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