sqlplus 两种看计划的语句

第一种

SQL> set autot
用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
SQL> set autot on
SQL> select *from test where c1=1;

C1 C2 C3
---------- ---------- ----------
1 cc

执行计划
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 33 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 33 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("C1"=1)

Note
-----
- dynamic sampling used for this statement

统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
514 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

第一种有执行查询语句，然后在输出了计划及统计信息。但有时有些sql 执行时间较长，而已只是想看计划就可以采用第二种方式。

第二种

SQL> set autotrace off;
SQL> explain plan for select *from test where c1=1;

已解释。

SQL> select *from table(DBMS_XPLAN.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 33 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 33 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

1 - filter("C1"=1)

Note
-----
- dynamic sampling used for this statement

已选择17行。
第二种方式没有统计信息，但省去了你的等待时间，你不用等着sql 执行就能看计划了。