oracle 执行计划 方法汇总

一.方法汇总

--环境构造

--研究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);/*

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

SELECT *FROM t1, t2WHERE t1.id = t2.t1_idAND t1.n in(18,19);*/

1. explain planfor获取；

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

 

/*

  步骤1：explain planfor "你的SQL"

  步骤2：select *from table(dbms_xplan.display());

*/

 

set linesize 1000

set pagesize 2000

explain plan for

SELECT *

FROM t1, t2

WHERE t1.id = t2.t1_id

AND t1.n in(18,19);

select * fromtable(dbms_xplan.display());

 

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------------

Plan hash value: 3532430033

-------------------------------------------------------------------------------------------

| Id | Operation                      |Name     | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------

|   0| SELECT STATEMENT               |          |    2 |  8138 |     6  (0)| 00:00:01 |

|   1|  NESTED LOOPS                  |          |       |      |            |          |

|   2|   NESTED LOOPS                 |          |    2 |  8138 |     6  (0)| 00:00:01 |

|   3|    INLIST ITERATOR             |          |       |      |            |          |

|   4|     TABLE ACCESS BY INDEX ROWID|T1       |     2 | 4056 |     2   (0)| 00:00:01 |

|*  5|      INDEX RANGE SCAN          | T1_N     |    1 |       |     1  (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 byoperation 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)

 

已选择24行。

/*

优点：  1.无需真正执行，快捷方便

 

缺陷：  1.没有输出运行时的相关统计信息（产生多少逻辑读，多少次递归调用，多少次物理读的情况）；

       2.无法判断是处理了多少行；

       3.无法判断表被访问了多少次。

       

确实啊，这毕竟都没有真正执行又如何得知真实运行产生的统计信息。

*/

2. set autotraceon ；   

方法2(set autotrace on 方式)

/*

  步骤1：setautotrace on

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

 

另，有如下几种方式：

                     set autotrace on                 (得到执行计划，输出运行结果）

                     set autotracetraceonly          (得到执行计划，不输出运行结果）

                     set autotrace traceonlyexplain  (得到执行计划，不输出运行结果和统计信息部分，仅展现执行计划部分）

                     set autotrace traceonlstatistics(不输出运行结果和执行计划部分，仅展现统计信息部分）

*/

set autotrace on

SELECT *

FROM t1, t2

WHERE t1.id = t2.t1_id

AND t1.n in(18,19);

 

执行计划

----------------------------------------------------------

Plan hash value: 3532430033

-------------------------------------------------------------------------------------------

| Id | Operation                      |Name     | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------

|   0| SELECT STATEMENT               |          |    2 |  8138 |     6  (0)| 00:00:01 |

|   1|  NESTED LOOPS                  |          |       |      |            |          |

|   2|   NESTED LOOPS                 |          |     2 | 8138 |     6   (0)| 00:00:01 |

|   3|    INLIST ITERATOR             |          |       |      |            |          |

|   4|     TABLE ACCESS BY INDEX ROWID|T1       |     2 | 4056 |     2   (0)| 00:00:01 |

|*  5|      INDEX RANGE SCAN          | T1_N     |    1 |       |     1  (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 byoperation 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)

统计信息

----------------------------------------------------------

         0  recursive calls

         0  db block gets

        12  consistent gets

         0  physical reads

          0 redo size

      1032  bytes sent via SQL*Net toclient

       416  bytes received via SQL*Netfrom client

         2  SQL*Net roundtrips to/fromclient

         0  sorts (memory)

         0  sorts (disk)

         2  rows processed

 

/*

--优点：1.可以输出运行时的相关统计信息（产生多少逻辑读，多少次递归调用，多少次物理读的情况）；

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

                 

--缺陷：1.必须要等到语句真正执行完毕后，才可以出结果；

       2.无法看到表被访问了多少次。       

        

*/        

        

3.statistics_level=all;

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

/*

  步骤1：altersession set statistics_level=all ;

  步骤2：在此处执行你的SQL

  步骤3：select *from table(dbms_xplan.display_cursor(null,null,'allstats last'));

 

 另注：

 

  1. 如果你用 /*+gather_plan_statistics */的方法，可以省略步骤1，直接步骤2,3。

  2. 关键字解读（其中OMem、1Mem和User-Mem在后续的课程中会陆续见到）：

   Starts为该sql执行的次数。

   E-Rows为执行计划预计的行数。

   A-Rows为实际返回的行数。A-Rows跟E-Rows做比较，就可以确定哪一步执行计划出了问题。

   A-Time为每一步实际执行的时间（HH：MM：SS.FF），根据这一行可以知道该sql耗时在了哪个地方。

   Buffers为每一步实际执行的逻辑读或一致性读。

   Reads为物理读。

   OMem:当前操作完成所有内存工作区(Work Aera)操作所总共使用私有内存(PGA)中工作区的大小，

        这个数据是由优化器统计数据以及前一次执行的性能数据估算得出的

   1Mem:当工作区大小无法满足操作所需的大小时，需要将部分数据写入临时磁盘空间中(如果仅需要写入一次就可以完成操作，

        就称一次通过，One-Pass;否则为多次通过，Multi_Pass).该列数据为语句最后一次执行中，单次写磁盘所需要的内存

        大小，这个由优化器统计数据以及前一次执行的性能数据估算得出的

   User-Mem:语句最后一次执行中，当前操作所使用的内存工作区大小，括号里面为(发生磁盘交换的次数,1次即为One-Pass,

          大于1次则为Multi_Pass,如果没有使用磁盘，则显示OPTIMAL)

   OMem、1Mem为执行所需的内存评估值，0Mem为最优执行模式所需内存的评估值，1Mem为one-pass模式所需内存的评估值。

   0/1/M 为最优/one-pass/multipass执行的次数。Used-Mem耗的内存

 

*/      

set autotrace off         

alter session set statistics_level=all ;

SELECT *

FROM t1, t2

WHERE t1.id = t2.t1_id

AND t1.n in(18,19);

select * fromtable(dbms_xplan.display_cursor(null,null,'allstats last'));

 

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------------------------

SQL_ID 1a914ws3ggfsn, child number 0

-------------------------------------

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

 

Plan hash value: 3532430033

-----------------------------------------------------------------------------------------------------

| Id | Operation                      |Name     | Starts | E-Rows | A-Rows|   A-Time   | Buffers |

-----------------------------------------------------------------------------------------------------

|   0| SELECT STATEMENT               |          |     1 |        |      2 |00:00:00.01 |      12 |

|   1|  NESTED LOOPS                  |          |     1 |        |      2 |00:00:00.01 |      12 |

|   2|   NESTED LOOPS                 |          |     1 |      2 |      2 |00:00:00.01 |      10 |

|   3|    INLIST ITERATOR             |          |     1 |        |      2 |00:00:00.01 |       5 |

|   4|     TABLE ACCESS BY INDEX ROWID|T1       |      2|      2 |      2 |00:00:00.01 |       5 |

|*  5|      INDEX RANGE SCAN          | T1_N     |     2 |      1 |      2 |00:00:00.01 |       3 |

|*  6|    INDEX RANGE SCAN            | T2_T1_ID |      2 |     1 |      2 |00:00:00.01 |       5 |

|   7|   TABLE ACCESS BY INDEX ROWID  | T2      |      2 |      1 |     2 |00:00:00.01 |       2 |

-----------------------------------------------------------------------------------------------------

Predicate Information (identified byoperation 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)

 

已选择29行。

/*

--优点：1.可以清晰的从STARTS得出表被访问多少。

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

       3.虽然没有专门的输出运行时的相关统计信息，但是执行计划中的BUFFERS就是真实的逻辑读的多少

                

--缺陷：1.必须要等到语句真正执行完毕后，才可以出结果。

       2.无法控制记录输屏打出，不像autotrace有 traceonly 可以控制不将结果打屏输出。

       3.看不出递归调用的次数，看不出物理读的多少（不过逻辑读才是重点）

*/

 

 

 

 

4.通过dbms_xplan.display_cursor输入sql_id参数直接获取

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

 

 

/* 

 

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

 

注：

  1. 还有一个方法，select  * fromtable(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));

 

*/

 

 

select * fromtable(dbms_xplan.display_cursor('1a914ws3ggfsn'));

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------------

SQL_ID 1a914ws3ggfsn, child number 0

-------------------------------------

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

 

Plan hash value: 3532430033

-------------------------------------------------------------------------------------------

| Id | Operation                      |Name     | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------

|   0| SELECT STATEMENT               |          |       |      |     6 (100)|          |

|   1|  NESTED LOOPS                  |          |       |      |            |          |

|   2|   NESTED LOOPS                 |          |    2 |  8138 |     6  (0)| 00:00:01 |

|   3|    INLIST ITERATOR             |          |       |      |            |          |

|   4|     TABLE ACCESS BY INDEX ROWID|T1       |     2 | 4056 |     2   (0)| 00:00:01 |

|*  5|      INDEX RANGE SCAN          | T1_N     |    1 |       |     1  (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 byoperation 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)

  

/*  

--优点：1.知道sql_id立即可得到执行计划，和explainplan for 一样无需执行；

       2.可以得到真实的执行计划。（停，等等，啥真实的，刚才这几个套路中，还有假的执行计划的吗？)

       

                

--缺陷  1.没有输出运行时的相关统计信息（产生多少逻辑读，多少次递归调用，多少次物理读的情况）；

       2.无法判断是处理了多少行； 

       3.无法判断表被访问了多少次。

       

*/       

5. 10046 trace跟踪

方法5(10046TRACE）

/*

 

  步骤1：altersession set events '10046 trace name context forever,level 12'; （开启跟踪）

  步骤2：执行你的语句

  步骤3：altersession set events '10046 trace name context off';   （关闭跟踪）

  步骤4：找到跟踪后产生的文件

  步骤5：tkprof  trc文件  目标文件  sys=no sort=prsela,exeela,fchela  （格式化命令）    

 

*/

set autotace off

alter session setstatistics_level=typical;    

alter session set events '10046 trace namecontext  forever,level 12';

 

SELECT *

FROM t1, t2

WHERE t1.id = t2.t1_id

AND t1.n in(18,19);  

  

alter session set events '10046 trace namecontext off';  

select d.value

|| '/'

|| LOWER (RTRIM(i.INSTANCE, CHR(0)))

|| '_ora_'

|| p.spid

|| '.trc' trace_file_name

from (select p.spid

     from v$mystat m,v$session s, v$process p

     where  m.statistic#=1 ands.sid=m.sid and p.addr=s.paddr) p,

     (select t.INSTANCE

      FROM v$thread t,v$parameter v

      WHERE v.name='thread'

      AND(v.VALUE=0 OR t.thread#=to_number(v.value))) i,

      (select value

      from v$parameter

      where name='user_dump_dest') d;

 

exit     

 

tkprofd:\oracle\diag\rdbms\test11g\test11g\trace/test11g_ora_2492.trc    d:\10046.txt  sys=no sort=prsela,exeela,fchela      

 

SELECT *

FROM t1, t2

WHERE t1.id = t2.t1_id

AND t1.n in(18,19)

 

call    count       cpu    elapsed       disk     query    current        rows

------- ------  -------- ---------- ---------- --------------------  ----------

Parse       1      0.00      0.00          0          0          0           0

Execute     1      0.00       0.00          0          0          0           0

Fetch       2      0.00       0.00          0         12          0           2

------- ------  -------- ---------- ---------- --------------------  ----------

total       4      0.00       0.00          0         12          0           2

 

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: 94 

 

Rows    Row Source Operation

------- ---------------------------------------------------

     2  NESTED LOOPS  (cr=12 pr=0 pw=0 time=0 us)

     2   NESTED LOOPS  (cr=10 pr=0 pw=0 time=48 us cost=6 size=8138card=2)

     2    INLIST ITERATOR  (cr=5 pr=0 pw=0 time=16 us)

      2    TABLE ACCESS BY INDEX ROWID T1 (cr=5 pr=0 pw=0 time=0 us cost=2size=4056 card=2)

     2      INDEX RANGE SCAN T1_N (cr=3pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 108621)

     2    INDEX RANGE SCAN T2_T1_ID(cr=5 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 108622)

     2   TABLE ACCESS BY INDEX ROWID T2(cr=2 pr=0 pw=0 time=0 us cost=2 size=2041 card=1)

 

 

Elapsed times include waiting on followingevents:

 Event waited on                             Times   Max. Wait Total Waited

 ----------------------------------------   Waited ----------  ------------

 SQL*Net message to client                       2        0.00          0.00

 SQL*Net message from client                     2        1.31          1.31

 

/*

--优点：1.可以看出SQL语句对应的等待事件

       2.如果SQL语句中有函数调用，SQL中有SQL，将会都被列出，无处遁形。

       3.可以方便的看出处理的行数，产生的物理逻辑读。

       4.可以方便的看出解析时间和执行时间。

       5.可以跟踪整个程序包

       

       

                

--缺陷: 1.步骤繁琐，比较麻烦

       2.无法判断表被访问了多少次。

       3.执行计划中的谓词部分不能清晰的展现出来。

       

*/  

6. awrsqrpt.sql

/*

  步骤1：@?/rdbms/admin/awrsqrpt.sql

  步骤2：选择你要的断点（beginsnap 和end snap)

  步骤3：输入你的sql_id    

*/

  

二.适用场合分析

   1.如果某SQL执行非常长时间才会出结果，甚至慢到返回不了结果，这时候看执行计划就只能用方法1；

   2.跟踪某条SQL最简单的方法是方法1，其次就是方法2；

   3.如果想观察到某条SQL有多条执行计划的情况，只能用方法4和方法6；

   4.如果SQL中含有多函数，函数中套有SQL等多层递归调用，想准确分析，只能使用方法5；

   5.要想确保看到真实的执行计划，不能用方法1和方法2；

   6.要想获取表被访问的次数，只能使用方法3；