查看执行计划的6种方法以及各个方法的优缺点

 --环境构造
--研究Nested Loops Join访问次数前准备工作
DROP TABLE t1 CASCADE CONSTRAINTS PURGE;
DROP TABLE t2 CASCADE CONSTRAINTS PURGE;
CREATE TABLE t1 (
     id NUMBER NOT NULL,
     n NUMBER,
     contents VARCHAR2(4000)
   )
   ;
CREATE TABLE t2 (
     id NUMBER NOT NULL,
     t1_id NUMBER NOT NULL,
     n NUMBER,
     contents VARCHAR2(4000)
   )
   ;
execute dbms_random.seed(0);
INSERT INTO t1
     SELECT  rownum,  rownum, dbms_random.string('a', 50)
       FROM dual
     CONNECT BY level <= 1000
      ORDER BY dbms_random.random;
INSERT INTO t2 SELECT rownum, rownum, rownum, dbms_random.string('b', 50) FROM dual CONNECT BY level <= 100000
    ORDER BY dbms_random.random;
COMMIT;
CREATE INDEX t1_n ON t1 (n);
CREATE INDEX t2_t1_id ON t2(t1_id);

下面我们将会用多种方法来查看如下语句的执行计划

----方法1(explain plan for 的方式。类似PLSQL DEVELOPE里的F5)

/*
  步骤1：explain plan for "你的SQL"
  步骤2：select * from table(dbms_xplan.display());
*/

优点：无需真真的执行，预估的执行计划没有真真执行，速度快。

缺点：无法输出输出相关的统计信息，（产生了多少逻辑读，产生了多少物理读，执行了多少递归调用，无法看表被访问了多少次，无法确定处理了多少行数据）

方法二：（set autotrace on 方式）

 步骤1：set autotrace on
 步骤2：在此处执行你的SQL即可，后续自然会有结果输出

set autotrace on
SELECT  *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n in(18,19);

Execution Plan
----------------------------------------------------------
Plan hash value: 3532430033

-------------------------------------------------------------------------------------------
| Id  | Operation               | Name      | Rows  | Bytes | Cost (%CPU)| Time      |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |    2 |  8138 |    8   (0)| 00:00:01 |
|   1 |  NESTED LOOPS               |      |    2 |  8138 |    8   (0)| 00:00:01 |
|   2 |   NESTED LOOPS               |      |    2 |  8138 |    8   (0)| 00:00:01 |
|   3 |    INLIST ITERATOR           |      |      |      |           |      |
|   4 |     TABLE ACCESS BY INDEX ROWID| T1      |    2 |  4056 |    4   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN           | T1_N      |    4 |      |    2   (0)| 00:00:01 |
|*  6 |    INDEX RANGE SCAN           | T2_T1_ID |    1 |      |    1   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID  | T2      |    1 |  2041 |    2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("T1"."N"=18 OR "T1"."N"=19)
   6 - access("T1"."ID"="T2"."T1_ID")

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
     14  consistent gets
      0  physical reads
      0  redo size
       1199  bytes sent via SQL*Net to client
    525  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      2  rows processed

优点：1、可以输出相应的统计信息（产生了多少逻辑读、物理读、是否有递归调用、是否发生了排序等）

          2、必须要等语句执行完毕后才可以输出执行计划，但是可以有traceonly开关来控制返回结果不打屏输出。

缺点：必须等sql执行完之后才能看到结果；无法看到表被访问了多少次。

----方法3（statistics level=all的方式) 

/*
  步骤1：alter session set statistics_level=all ;
  步骤2：在此处执行你的SQL
  步骤3：select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
  注：如果用 /*+ gather_plan_statistics */的方法，可以省略步骤1，直接步骤2,3。

*Starts 代表访问的次数

E-Rows代表了预估计返回的行数

A-Rows代表了实际返回的行数

Buffers代表了产生了多少逻辑读与set autotrace on里面的 consistent gets一样。

优点：1、可以清晰的看到表被访问的次数。

           2、可以清晰的从E-ROWS和A-ROWS中得到预测的行数和真实的行数，从而可以准确判断Oracle评估是否准确。

           3、从Buffers就可以看到产生了多少逻辑读。

缺点：1、必须要等到语句真正执行完毕后，才可以出结果。
           2、无法控制记录输屏打出，不像autotrace有 traceonly 可以控制不将结果打屏输出。
           3、看不出递归调用的次数，看不出物理读的多少（不过逻辑读才是重点）

----方法4(知道sql_id后，直接带入的方式，简单，就步骤1）

步骤1： select  * from table(dbms_xplan.display_cursor('&sq_id')); （该方法是从共享池里得到）

注：
  1. 还有一个方法，select  * from table(dbms_xplan.display_awr('&sq_id'));（这是awr性能视图里获取到的）
  2. 如果有多执行计划，可以用类似方法查出
    select * from table(dbms_xplan.display_cursor('cyzznbykb509s',0));
    select * from table(dbms_xplan.display_cursor('cyzznbykb509s',1));

----方法5(10046TRACE）

  步骤1：alter session set events '10046 trace name context  forever,level 12'; （开启跟踪）
  步骤2：执行你的语句
  步骤3：alter session set events '10046 trace name context off';   （关闭跟踪）
  步骤4：找到跟踪后产生的文件
  步骤5：tkprof  trc文件  目标文件  sys=no sort=prsela,exeela,fchela  （格式化命令）  

步骤1： alter session set events '10046 trace name context forever,level 12';(开启跟踪)

步骤2：执行你的语句

步骤3：alter session set events '10046 trace name contexte off ';(关闭跟踪)

步骤4: oradebug setmypid; oradebug tracefile_name

步骤5：tkprof 文件  sys=no sort=prsela,exeela,fchela(格式化命令)

优点：1、可以看出sql对应的等待事件

          2、如果sql中有递归调用，可以列出来相关sql列出。

          3、可以很方便的看到返回的行数，以及物理读以及逻辑读。

          4、可以看见sql的解析时间和执行时间