通过PL/SQL Developer查看
登陆PL/SQL Developer,运行sql窗口,复制所需优化语句,选中语句后点击F5键,就可以看见执行计划。点击preference可以配置所需监控内容。
通过命令行查看
set autotrace on
加上这个set命令后,执行任何SQL语句都会把执行计划和统计信息显示出来。该方式是在执行之后进行统计。
set time on -- (说明:打开时间显示)
set autotrace on --(说明:打开自动分析统计,并显示SQL语句的运行结果)
set autotrace traceonly --(说明:打开自动分析统计,不显示SQL语句的运行结果)
set autotrace on --(说明:打开自动分析统计,并显示SQL语句的运行结果)
set autotrace traceonly --(说明:打开自动分析统计,不显示SQL语句的运行结果)
SQL> set autotrace on
SQL> select * from emp where mgr is not null;
... ...
执行计划
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 481 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 13 | 481 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("MGR" IS NOT NULL)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
1321 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
13 rows processed
SQL> select * from emp where mgr is not null;
... ...
执行计划
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 481 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 13 | 481 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("MGR" IS NOT NULL)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
1321 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
13 rows processed
explain plan
单纯解释SQL并不执行。
SQL> explain plan for select * from emp where mgr is not null;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 481 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 13 | 481 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
1 - filter("MGR" IS NOT NULL)
已选择13行。
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 481 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 13 | 481 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
1 - filter("MGR" IS NOT NULL)
已选择13行。
常见问题
oracle问题 SP2-0613: 无法验证 PLAN_TABLE 格式或实体
此错误表示还没有创建 plan_table 表
先创建plan_table 表
SQL>@%oracle_home%/rdbms/admin/utlxplan.sql
然后授权
SQL>@%oracle_home%/sqlplus/admin/plustrce.sql
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;
或者
grant plustrace to user_name;
如plustrace角色不存在,则需要执行$ORACLE_HOME/sqlplus/admin/plustrce.sql进行创建