AUTOTRACE是SQL*Plus的一项功能，其作用是自动跟踪SQL语句，为SQL 语句生成一个 执行计划并且提供与 该语句的处理有关的统计信息。
SQL*Plus AUTOTRACE 可以用来替代 SQL Trace 使用，AUTOTRACE 的好处是不必设置跟踪文件的格式，并且它将自动为 SQL 语句显示执行计划。AUTOTRACE与执行计划的区别是AUTOTRACE 分析和执行语句；而EXPLAIN PLAN仅分析语句，而不负责执行语句。
配置AUTOTRACE
确保表PLAN_TABLE已经创建，如果没有则如下创建
SQL> @/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/utlxplan.sql
Table created.
SQL> create public synonym plan_table$ for plan_table;
Synonym created.
SQL> grant all on plan_table$ to public;
Grant succeeded.
使用AUTOTRACE
SQL> show user
USER is "SYS"
SQL> set autotrace on;
SQL> select * from scott.emp;
15 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 540 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 15 | 540 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
512 recursive calls
0 db block gets
91 consistent gets
5 physical reads
0 redo size
1648 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
8 sorts (memory)
0 sorts (disk)
15 rows processed
AUTOTRACE相关命令
命令 & 解释
SET AUTOTRACE OFF 此为默认值，即关闭Autotrace
SET AUTOTRACE ON 产生结果集和解释计划并列出统计
SET AUTOTRACE ON EXPLAIN 显示结果集和解释计划不显示统计
SET AUTOTRACE TRACEONLY 显示解释计划和统计，尽管执行该语句但将看不到结果集
SET AUTOTRACE TRACEONLY STATISTICS 只显示统计
AUTOTRACE STATISTICS含义
列名 & 解释
recursive call 递归调用SQL的个数；Oracle在执行这个SQL的时候，有时候会生成很多额外的SQL语句，这个就称为递归调用
db block gets 从buffer cache中读取的block的数量
consistent gets 从buffer cache中读取的undo数据的block的数量
physical reads 从磁盘读取的block的数量
redo size DML生成的redo的大小
sorts (memory) 在内存执行的排序量
sorts (disk) 在磁盘上执行的排序量,如果memory空间使用不足，是会使用disk的空间的
bytes sent via SQL*Net to client 利用sql*net传入到client的字节数
bytes received via SQL*Net from client 利用sql*net传出client的字节数
再次执行刚刚的查询，递归调用为0，数据都读取至缓存，逻辑读大量减少，物理磁盘读为0
SQL> set autotrace off;
SQL> set timing on;
SQL> set autotrace on;
SQL>
15 rows selected.
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 540 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 15 | 540 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1648 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
15 rows processed
