Oracle查看SQL语句的执行计划

一、explain plan for 方法

SQL> explain plan for select * from dept;

Explained.

SQL> select * from TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3383998547

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     4 |    80 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

SQL>

二、set autotrace 方法

SQL> set autotrace traceonly explain;
SQL> select * from dept;

Execution Plan
----------------------------------------------------------
Plan hash value: 3383998547

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     4 |    80 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

SQL>

关于set autotrace有几种使用方法

(1)set autotrace off             ------- 不生成autotrace 报告，这是缺省模式
(2)set autotrace on explain      ------- 包含执行计划、查询结果
(3)set autotrace on statistics   ------- 只包含统计信息、查询结果
(4)set autotrace on 			 ------- 包含执行计划、统计信息、查询结果
(5)set autotrace traceonly       ------- 包含执行计划、统计信息
(6)set autotrace traceonly explain;  -------只包含执行计划

三、