查看oracle执行计划
第一步:登入sql/plus执行命令(无先后顺序)
set time on -- (说明:打开时间显示)
set autotrace on --(说明:打开自动分析统计,并显示SQL语句的运行结果)
set autotrace traceonly --(说明:打开自动分析统计,不显示SQL语句的运行结果)
第二步:输入你要查看的sql执行
先执行
explain plan for select * from cn where rownum < 5;
再
select * from table(DBMS_XPLAN.DISPLAY)
便可以看到oracle的执行计划了
SQL> explain plan for select * from cn where rownum < 5;
已解释。
SQL> select * from table(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4147837678
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 72 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL| CN | 4 | 72 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
1 - filter(ROWNUM<5)
已选择14行。
ps: 使用set autotrace on后会自动解释并给出执行计划
SQL> set autotrace on
SQL> desc cn
名称 是否为空? 类型
----------------------------------------------------------------------------------------- -------- ------------------------------------------------------------
CNID NOT NULL VARCHAR2(20)
CUSTOMERID VARCHAR2(20)
CNNAME VARCHAR2(20)
STATE NUMBER
SQL> select * from cn where rownum < 5;
CNID CUSTOMERID CNNAME STATE
---------------------------------------- ---------------------------------------- ---------------------------------------- ----------
c001 001 baidu 0
c002 002 sina 0
c003 002 yahoo 1
c004 003 sina 1
执行计划
----------------------------------------------------------
Plan hash value: 4147837678
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 72 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL| CN | 4 | 72 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<5)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
727 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
SQL>
可能出现的问题及解决方法:
oracle问题 SP2-0613: 无法验证 PLAN_TABLE 格式或实体
此错误表示还没有创建 plan_table 表
先创建plan_table 表
SQL>@%oracle_home%/rdbms/admin/utlxplan.sql
授权
@%oracle_home%/sqlplus/admin/plustrce.sql
这样普通用户不能用
所以要授权普通用户对 plan_table 表
SQL> conn / as sysdba
SQL>create public synonym plan_table for plan_table; --创建plan_table的同义词plan_table,为public属性表示所有的用户都可见
SQL> grant all onplan_table to public;
如果只对某些用户实现 explain plan
开通explain plan的权限则
grant plustrace to user_name;
ORA-01919: 角色 'PLUSTRACE' 不存在
这是由于角色PLUSTRACE不存在,或没有创建。
只要执行$ORACLE_HOME/sqlplus/admin/plustrce.sql脚本就创建plustrace角色: