查看执行计划

如何产生执行计划？

1） autotrace

准备PLAN_TABLE，/rdbms/admin/utlxplan.sql  /sqlplus/admin/plustrace.sql

grant plustrace to user_name;

用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

举例：SET AUTOT [RACE] OFF 

         停止AutoTrace

         SET AUTOT [RACE] ON 

         开启AutoTrace，显示AUTOTRACE信息和SQL执行结果

         SET AUTOT[RACE] TRACEONLY 

         开启AutoTrace，仅显示AUTOTRACE信息

         SET AUTOT[RACE] ON EXPLAIN  

         开启AutoTrace，仅显示AUTOTRACE的EXPLAIN信息

         SET AUTOT[RACE] ON STATISTICS

          开启AutoTrace，仅显示AUTOTRACE的STATISTICS信息

SQL> set autotrace on; SQL> select * from dual; D - X Execution Plan ---------------------------------------------------------- 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 | -------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 407 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) 1 rows processed

SQL> set autotrace traceonly; SQL> select * from dual; Execution Plan ---------------------------------------------------------- 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 | -------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 407 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) 1 rows processed

SQL> set autotrace on explain; SQL> select * from dual; D - X Execution Plan ---------------------------------------------------------- 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> set autotrace on statistics; SQL> / D - X Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 407 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) 1 rows processed

 recursive calls：递归调用统计数据（指由于你需要执行其他SQL语句而必须执行的SQL）；

db block gets：通过update/delete/select for update读的次数；

consistent gets : 通过不带for update的select 读的次数；

physical reads：物理读，从磁盘上读取数据块的数量；

redo size：重做尺寸统计信息显示在执行过程中产生了多少重做数据；

bytes sent via SQL*Net to client：Total number of bytes sent to the client from the foreground processes；

bytes received via SQL*Net from client：Total number of bytes received from the client over Oracle Net；

SQL*Net roundtrips to/from client：Total number of Oracle Net messages sent to and received from the client；

sorts ( memory)：在内存中排序的次数；

sorts(disk)：在磁盘中排序的次数；

rows processed：结果返回的行数。

2） explain plan for

准备PLAN_TABLE，/rdbms/admin/utlxplan.sql

用法：explain plan for set statement= ‘…..’

          explain plan for select ….

SQL> explain plan for SELECT /*+ordered use_hash(d,e) */e.ename,d.deptno 2 from lpx_emp e,lpx_dept d 3 WHERE e.deptno = d.deptno; Explained SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 2846336464 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 462 | 7 (15)| 00:00:01 | |* 1 | HASH JOIN | | 14 | 462 | 7 (15)| 00:00:01 | | 2 | TABLE ACCESS FULL| LPX_EMP | 14 | 280 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| LPX_DEPT | 4 | 52 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("E"."DEPTNO"="D"."DEPTNO") Note ----- - dynamic sampling used for this statement 19 rows selected

 

 

3） tkprof      

        oracle在打开跟踪功能后，将被跟踪session中正在执行的SQL的性能状态数据都收集到一个跟踪文件中。这个跟踪文件提供了许多有用的信息，例如一个sql的解析次数、执行次数、fetch次数、物理读次数、逻辑读次数、CPU使用时间等，利用这些信息可以诊断你的sql的问题，从而用来优化你的系统。TKPROF工具对其进行转换。