方式1
SQL> set autotrace on explain;
SQL> select * from dual;
D
-
X
执行计划
----------------------------------------------------------
Plan hash value: 272002086
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL>
方式2
SQL> explain plan for select * from apps.test where id<=10;
已解释。
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 | | 14 | 308 | 26 (4)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 14 | 308 | 26 (4)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter(TO_NUMBER("ID")<=10)
Note
-----
- dynamic sampling used for this statement
已选择17行。
SQL>
----------------------------------------------------------------------------------
如果是默认安装TOAD,在查看执行计划时会报一个错:
ORA-02404: 未找到指定的计划表
创建对应的表
CREATE TABLE TOAD_PLAN_TABLE (
STATEMENT_ID VARCHAR2 (32),
TIMESTAMP DATE,
REMARKS VARCHAR2 (80),
OPERATION VARCHAR2 (30),
OPTIONS VARCHAR2 (30),
OBJECT_NODE VARCHAR2 (128),
OBJECT_OWNER VARCHAR2 (30),
OBJECT_NAME VARCHAR2 (30),
OBJECT_INSTANCE NUMBER,
OBJECT_TYPE VARCHAR2 (30),
SEARCH_COLUMNS NUMBER,
ID NUMBER,
COST NUMBER,
PARENT_ID NUMBER,
POSITION NUMBER,
CARDINALITY NUMBER,
OPTIMIZER VARCHAR2 (255),
BYTES NUMBER,
OTHER_TAG VARCHAR2 (255),
OTHER LONG,
PARTITION NUMBER,
PARTITION_START VARCHAR2 (255),
PARTITION_STOP VARCHAR2 (255),
DISTRIBUTION VARCHAR2 (30) ) tablespace non_SYSTEM_tablepsace
create public synonym TOAD_PLAN_TABLE for TOAD_PLAN_TABLE;
grant all on TOAD_PLAN_TABLE to public;
后就可以正常查看
http://space.itpub.net/42810/viewspace-87158