方法一:通过AUTOTRACE显示
1.1、设置autotrace 的几种显示
序号 | 命令 | 解释 |
1 | SET AUTOTRACE OFF | 此为默认值,即关闭Autotrace |
2 | SET AUTOTRACE ON EXPLAIN | 只显示执行计划 |
3 | SET AUTOTRACE ON STATISTICS | 只显示执行的统计信息 |
4 | SET AUTOTRACE ON | 包含2,3两项内容 |
5 | SET AUTOTRACE TRACEONLY | 与ON相似,但不显示语句的执行结果 |
注意:
*autotrace功能只能在SQL*PLUS里使用
v$sql_plan视图解释,详见官方文档reference。
V$SQL_PLAN contains the execution plan information for each child cursor loaded in the library cache.
方法二:通过explain plan for
2.1、创建执行计划表
conn / as sysdba
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql --如果数据库中有plan_table表,不需要执行此脚本
Table created.
2.2、授权并创建public的同义词
SQL> grant all on plan_table to public;
Grant succeeded.
SQL> create public synonym plan_table for plan_table;
create public synonym plan_table for plan_table
*
ERROR at line 1:
ORA-00955: name is already used by an existing object --说明同义词已经存在
SQL>
2.3、创建执行计划
SQL>explain plan set statement_id='ZZB_TEST' for SELECT * FROM SCOTT.EMP WHERE DEPTNO=20;
Explained.
2.4、显示执行计划
SQL> select * from table(dbms_xplan.display()); --直接执行$ORACLE_HOME/rdbms/admin/utlxplp.sql脚本的内容
SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 222 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 6 | 222 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("DEPTNO"=20)
13 rows selected.
SQL> @?/rdbms/admin/utlxplp.sql
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 222 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 6 | 222 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("DEPTNO"=20)
13 rows selected.
方法三:使用第三方工具
PL/SQL中 sql windows 中执行sql语句,然后按f5键就可以显示sql语句的执行计划。
TOAD中在windows窗口选中Explain PlanTAB即可。