参考盖国强《深入解析oracle》
autotrace可以用于跟踪 SQL 的执行计划,收集统计信息
SQL> set autotrace on
SQL> select * from test.worker;
WNO WNAME
--------- --------------------
001 adam
002 lilith
003 sachiel
Execution Plan
----------------------------------------------------------
Plan hash value: 435679406
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 99 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| WORKER | 3 | 99 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
742 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)
3 rows processed
使用 Autotrace 功能的另外一个好处就是,可以很容易的发现各种视图的底层基础表.
当使用 Autotrace 功能时,在数据库内部,Oracle 实际上是启动了 2 个会话(session)连接,一个 Session 用于执行查询等操作,另外一个 Session 用于记录执行计划和输出最终结果等操作。