程序博客网 > c语言大型程序源代码

[Oracle]获取执行计划的各个方法总结

来源：互联网 发布：c语言大型程序源代码 编辑：程序博客网 时间：2024/06/05 05:26

总的结论：

一.获取执行计划的6种方法(详细步骤已经在每个例子的开头注释部分说明了）：
1. explain plan for获取；
2. set autotrace on ；
3. statistics_level=all;
4. 通过dbms_xplan.display_cursor输入sql_id参数直接获取
5. 10046 trace跟踪
6. awrsqrpt.sql

二.适用场合分析

1.如果某SQL执行非常长时间才会出结果，甚至慢到返回不了结果，这时候看执行计划就只能用方法1,或者方法4调用现成的；
2.跟踪某条SQL最简单的方法是方法1，其次就是方法2；
3.如果想观察到某条SQL有多条执行计划的情况，只能用方法4和方法6；
4.如果SQL中含有多函数，函数中套有SQL等多层递归调用，想准确分析，只能使用方法5；
5.要想确保看到真实的执行计划，不能用方法1和方法2；

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


环境构造

--研究Nested Loops Join访问次数前准备工作

[sql] view plaincopyprint?在CODE上查看代码片派生到我的代码片
  1. DROP TABLE t1CASCADE CONSTRAINTS PURGE;  
  2. DROP TABLE t2CASCADE CONSTRAINTS PURGE;  
  3. CREATE TABLE t1 ( 
  4.      id NUMBER NOT NULL, 
  5.      n NUMBER, 
  6.      contents VARCHAR2(4000) 
  7.    ) 
  8.    ;  
  9. CREATE TABLE t2 ( 
  10.      id NUMBER NOT NULL, 
  11.      t1_id NUMBER NOT NULL, 
  12.      n NUMBER, 
  13.      contents VARCHAR2(4000) 
  14.    ) 
  15.    ;  
  16. execute dbms_random.seed(0);  
  17. INSERT INTO t1 
  18.      SELECT  rownum,  rownum, dbms_random.string('a', 50) 
  19.        FROM dual 
  20.      CONNECT BYlevel <= 1000 
  21.       ORDER BY dbms_random.random;  
  22. INSERT INTO t2SELECT rownum, rownum, rownum, dbms_random.string('b', 50)FROM dual CONNECTBY level <= 100000 
  23.     ORDER BY dbms_random.random;  
  24. COMMIT;  
  25. CREATE INDEX t1_nON t1 (n); 
  26. CREATE INDEX t2_t1_idON t2(t1_id); 

下面我们将会用多种方法来查看如下语句的执行计划
SELECT  *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n in(18,19);

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

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

[sql] view plaincopyprint?在CODE上查看代码片派生到我的代码片
  1. set linesize 1000 
  2. set pagesize 2000 
  3. explain plan for 
  4. SELECT  * 
  5. FROM t1, t2 
  6. WHERE t1.id = t2.t1_id 
  7. AND t1.n in(18,19); 
  8. select * fromtable(dbms_xplan.display()); 
  9.  
  10. PLAN_TABLE_OUTPUT 
  11. ------------------------------------------------------------------------------------------- 
  12. Plan hash value: 3532430033 
  13. ------------------------------------------------------------------------------------------- 
  14. | Id  | Operation                      | Name     |Rows  | Bytes | Cost (%CPU)| Time     | 
  15. ------------------------------------------------------------------------------------------- 
  16. |   0 | SELECT STATEMENT               |          |     2 |  8138 |     6   (0)| 00:00:01 | 
  17. |   1 |  NESTED LOOPS                  |          |       |       |            |          | 
  18. |   2 |   NESTED LOOPS                 |          |     2 |  8138 |     6   (0)| 00:00:01 | 
  19. |   3 |    INLIST ITERATOR             |          |       |       |            |          | 
  20. |   4 |     TABLE ACCESS BYINDEX ROWID| T1       |     2 |  4056 |     2   (0)| 00:00:01 | 
  21. |*  5 |      INDEX RANGE SCAN          | T1_N     |     1 |       |     1   (0)| 00:00:01 | 
  22. |*  6 |    INDEX RANGE SCAN            | T2_T1_ID |     1 |       |     1   (0)| 00:00:01 | 
  23. |   7 |   TABLE ACCESS BY INDEX ROWID  | T2       |     1 |  2041 |     2   (0)| 00:00:01 | 
  24. ------------------------------------------------------------------------------------------- 
  25. Predicate Information (identified by operation id): 
  26. --------------------------------------------------- 
  27.    5 - access("T1"."N"=18OR "T1"."N"=19) 
  28.    6 - access("T1"."ID"="T2"."T1_ID") 
  29. Note 
  30. ----- 
  31.    - dynamic sampling usedfor this statement (level=2) 
  32.  
  33. 已选择24行。 

优点：  1.无需真正执行，快捷方便
缺陷：  1.没有输出运行时的相关统计信息（产生多少逻辑读，多少次递归调用，多少次物理读的情况）；
        2.无法判断是处理了多少行；
        3.无法判断表被访问了多少次。   
确实啊，这毕竟都没有真正执行又如何得知真实运行产生的统计信息。

方法2(set autotrace on 方式)

  步骤1：set autotrace on
  步骤2：在此处执行你的SQL即可，后续自然会有结果输出
 
另，有如下几种方式：
                     set autotrace on                 (得到执行计划，输出运行结果）
                     set autotrace traceonly          (得到执行计划，不输出运行结果）
                     set autotrace traceonly explain  (得到执行计划，不输出运行结果和统计信息部分，仅展现执行计划部分）
                     set autotrace traceonl statistics(不输出运行结果和执行计划部分，仅展现统计信息部分）
[sql] view plaincopyprint?在CODE上查看代码片派生到我的代码片
  1. set autotrace on  
  2. SELECT  * 
  3. FROM t1, t2 
  4. WHERE t1.id = t2.t1_id 
  5. AND t1.n in(18,19); 
  6.  
  7. 执行计划 
  8. ---------------------------------------------------------- 
  9. Plan hash value: 3532430033 
  10. ------------------------------------------------------------------------------------------- 
  11. | Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)|Time     | 
  12. ------------------------------------------------------------------------------------------- 
  13. |   0 | SELECT STATEMENT               |          |     2 |  8138 |     6   (0)| 00:00:01 | 
  14. |   1 |  NESTED LOOPS                  |          |       |       |            |          | 
  15. |   2 |   NESTED LOOPS                 |          |     2 |  8138 |     6   (0)| 00:00:01 | 
  16. |   3 |    INLIST ITERATOR             |          |       |       |            |          | 
  17. |   4 |     TABLE ACCESSBY INDEX ROWID| T1       |     2 |  4056 |     2   (0)| 00:00:01 | 
  18. |*  5 |      INDEX RANGE SCAN          | T1_N     |     1 |       |     1   (0)| 00:00:01 | 
  19. |*  6 |    INDEX RANGE SCAN            | T2_T1_ID |     1 |       |     1   (0)| 00:00:01 | 
  20. |   7 |   TABLE ACCESS BYINDEX ROWID  | T2       |     1 |  2041 |     2   (0)| 00:00:01 | 
  21. ------------------------------------------------------------------------------------------- 
  22. Predicate Information (identified by operation id): 
  23. --------------------------------------------------- 
  24.    5 - access("T1"."N"=18OR "T1"."N"=19) 
  25.    6 - access("T1"."ID"="T2"."T1_ID") 
  26. Note 
  27. ----- 
  28.    - dynamic sampling used for this statement (level=2) 
  29. 统计信息 
  30. ---------------------------------------------------------- 
  31.           0  recursive calls 
  32.           0  db block gets 
  33.          12  consistent gets 
  34.           0  physical reads 
  35.           0  redo size 
  36.        1032  bytes sent via SQL*Net to client 
  37.         416  bytes received via SQL*Net from client 
  38.           2  SQL*Net roundtrips to/from client 
  39.           0  sorts (memory) 
  40.           0  sorts (disk) 
  41.           2  rows processed 

优点：1.可以输出运行时的相关统计信息（产生多少逻辑读，多少次递归调用，多少次物理读的情况）；
        2.虽然必须要等语句执行完毕后才可以输出执行计划，但是可以有traceonly开关来控制返回结果不打屏输出。
                 
缺陷：1.必须要等到语句真正执行完毕后，才可以出结果；
        2.无法看到表被访问了多少次。       

方法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'));
 
另注：

  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耗的内存
 
[sql] view plaincopyprint?在CODE上查看代码片派生到我的代码片
  1. set autotrace off           
  2. alter session set statistics_level=all ; 
  3. SELECT  * 
  4. FROM t1, t2 
  5. WHERE t1.id = t2.t1_id 
  6. AND t1.n in(18,19); 
  7. select * fromtable(dbms_xplan.display_cursor(null,null,'allstats last')); 
  8.  
  9. PLAN_TABLE_OUTPUT 
  10. ------------------------------------------------------------------------------------------------------- 
  11. SQL_ID  1a914ws3ggfsn, child number 0 
  12. ------------------------------------- 
  13. SELECT  * FROM t1, t2WHERE t1.id = t2.t1_id AND t1.nin(18,19) 
  14.  
  15. Plan hash value: 3532430033 
  16. ----------------------------------------------------------------------------------------------------- 
  17. | Id  | Operation                      | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | 
  18. ----------------------------------------------------------------------------------------------------- 
  19. |   0 | SELECT STATEMENT               |          |      1 |        |      2 |00:00:00.01 |      12 | 
  20. |   1 |  NESTED LOOPS                  |          |      1 |        |      2 |00:00:00.01 |      12 | 
  21. |   2 |   NESTED LOOPS                 |          |      1 |      2 |      2 |00:00:00.01 |      10 | 
  22. |   3 |    INLIST ITERATOR             |          |      1 |        |      2 |00:00:00.01 |       5 | 
  23. |   4 |     TABLE ACCESSBY INDEX ROWID| T1       |      2 |      2 |      2 |00:00:00.01 |       5 | 
  24. |*  5 |      INDEX RANGE SCAN          | T1_N     |      2 |      1 |      2 |00:00:00.01 |       3 | 
  25. |*  6 |    INDEX RANGE SCAN            | T2_T1_ID |      2 |      1 |      2 |00:00:00.01 |       5 | 
  26. |   7 |   TABLE ACCESS BYINDEX ROWID  | T2       |      2 |      1 |      2 |00:00:00.01 |       2 | 
  27. ----------------------------------------------------------------------------------------------------- 
  28. Predicate Information (identified by operation id): 
  29. --------------------------------------------------- 
  30.    5 - access(("T1"."N"=18OR "T1"."N"=19)) 
  31.    6 - access("T1"."ID"="T2"."T1_ID") 
  32. Note 
  33. ----- 
  34.    - dynamic sampling used for this statement (level=2) 
  35.  
  36. 已选择29行。 
优点：1.可以清晰的从STARTS得出表被访问多少。
        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));

[sql] view plaincopyprint?在CODE上查看代码片派生到我的代码片
  1. select * fromtable(dbms_xplan.display_cursor('1a914ws3ggfsn')); 
  2. PLAN_TABLE_OUTPUT 
  3. ------------------------------------------------------------------------------------------- 
  4. SQL_ID  1a914ws3ggfsn, child number 0 
  5. ------------------------------------- 
  6. SELECT  * FROM t1, t2WHERE t1.id = t2.t1_id AND t1.nin(18,19) 
  7.  
  8. Plan hash value: 3532430033 
  9. ------------------------------------------------------------------------------------------- 
  10. | Id  | Operation                      | Name     |Rows  | Bytes | Cost (%CPU)| Time     | 
  11. ------------------------------------------------------------------------------------------- 
  12. |   0 | SELECT STATEMENT               |          |       |       |     6 (100)|          | 
  13. |   1 |  NESTED LOOPS                  |          |       |       |            |          | 
  14. |   2 |   NESTED LOOPS                 |          |     2 |  8138 |     6   (0)| 00:00:01 | 
  15. |   3 |    INLIST ITERATOR             |          |       |       |            |          | 
  16. |   4 |     TABLE ACCESS BYINDEX ROWID| T1       |     2 |  4056 |     2   (0)| 00:00:01 | 
  17. |*  5 |      INDEX RANGE SCAN          | T1_N     |     1 |       |     1   (0)| 00:00:01 | 
  18. |*  6 |    INDEX RANGE SCAN            | T2_T1_ID |     1 |       |     1   (0)| 00:00:01 | 
  19. |   7 |   TABLE ACCESS BY INDEX ROWID  | T2       |     1 |  2041 |     2   (0)| 00:00:01 | 
  20. ------------------------------------------------------------------------------------------- 
  21. Predicate Information (identified by operation id): 
  22. --------------------------------------------------- 
  23.  
  24.    5 - access(("T1"."N"=18OR "T1"."N"=19)) 
  25.    6 - access("T1"."ID"="T2"."T1_ID") 
  26.  
  27. Note 
  28. ----- 
  29.    - dynamic sampling usedfor this statement (level=2) 

优点：1.知道sql_id立即可得到执行计划，和explain plan for 一样无需执行；
        2.可以得到真实的执行计划。（停，等等，啥真实的，刚才这几个套路中，还有假的执行计划的吗？)
       
                
缺陷：  1.没有输出运行时的相关统计信息（产生多少逻辑读，多少次递归调用，多少次物理读的情况）；
        2.无法判断是处理了多少行； 
        3.无法判断表被访问了多少次。
       

方法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  （格式化命令）    

[sql] view plaincopyprint?在CODE上查看代码片派生到我的代码片
  1. set autotace off 
  2. alter session set statistics_level=typical;      
  3. alter session set events'10046 trace name context  forever,level 12'; 
  4.  
  5. SELECT  * 
  6. FROM t1, t2 
  7. WHERE t1.id = t2.t1_id 
  8. AND t1.n in(18,19);    
  9.     
  10. alter session set events'10046 trace name context off';    
  11. select d.value 
  12. || '/' 
  13. || LOWER (RTRIM(i.INSTANCE, CHR(0))) 
  14. || '_ora_' 
  15. || p.spid 
  16. || '.trc' trace_file_name 
  17. from (select p.spid 
  18.       from v$mystat m,v$session s, v$process p 
  19.       where  m.statistic#=1and s.sid=m.sid and p.addr=s.paddr) p, 
  20.       (select t.INSTANCE 
  21.        FROM v$thread t,v$parameter v 
  22.        WHERE v.name='thread' 
  23.        AND(v.VALUE=0 OR t.thread#=to_number(v.value))) i, 
  24.        (select value 
  25.        from v$parameter 
  26.        where name='user_dump_dest') d; 
  27.  
  28. exit       
  29.   
  30. tkprof d:\oracle\diag\rdbms\test11g\test11g\trace/test11g_ora_2492.trc    d:\10046.txt  sys=no sort=prsela,exeela,fchela        
  31.  
  32. SELECT  * 
  33. FROM t1, t2 
  34. WHERE t1.id = t2.t1_id 
  35. AND t1.n in(18,19) 
  36.  
  37. call     count       cpu    elapsed       disk      query   current        rows 
  38. ------- ------  -------- ---------- ---------- ---------- ----------  ---------- 
  39. Parse        1      0.00       0.00          0          0          0           0 
  40. Execute      1      0.00       0.00          0          0          0           0 
  41. Fetch        2      0.00       0.00          0         12          0           2 
  42. ------- ------  -------- ---------- ---------- ---------- ----------  ---------- 
  43. total        4      0.00       0.00          0         12          0           2 
  44.  
  45. Misses in library cache during parse: 0 
  46. Optimizer mode: ALL_ROWS 
  47. Parsing user id: 94   
  48.  
  49. Rows     Row Source Operation 
  50. -------  --------------------------------------------------- 
  51.       2  NESTED LOOPS  (cr=12 pr=0 pw=0 time=0 us) 
  52.       2   NESTED LOOPS  (cr=10 pr=0 pw=0 time=48 us cost=6size=8138 card=2) 
  53.       2    INLIST ITERATOR  (cr=5 pr=0 pw=0 time=16 us) 
  54.       2     TABLE ACCESS BYINDEX ROWID T1 (cr=5 pr=0 pw=0 time=0 us cost=2size=4056 card=2) 
  55.       2      INDEX RANGE SCAN T1_N (cr=3 pr=0 pw=0time=0 us cost=1 size=0 card=1)(object id 108621) 
  56.       2    INDEX RANGE SCAN T2_T1_ID (cr=5 pr=0 pw=0time=0 us cost=1 size=0 card=1)(object id 108622) 
  57.       2   TABLE ACCESS BY INDEX ROWID T2 (cr=2 pr=0 pw=0time=0 us cost=2 size=2041 card=1) 
  58.  
  59.  
  60. Elapsed times include waiting on following events: 
  61.   Event waited on                             Times  Max. Wait  Total Waited 
  62.   ----------------------------------------   Waited  ----------  ------------ 
  63.   SQL*Net message to client                       2        0.00          0.00 
  64.   SQL*Net message from client      

优点：1.可以看出SQL语句对应的等待事件
        2.如果SQL语句中有函数调用，SQL中有SQL，将会都被列出，无处遁形。
        3.可以方便的看出处理的行数，产生的物理逻辑读。
        4.可以方便的看出解析时间和执行时间。
        5.可以跟踪整个程序包
                                
缺陷: 1.步骤繁琐，比较麻烦
        2.无法判断表被访问了多少次。
        3.执行计划中的谓词部分不能清晰的展现出来。


方法6. awrsqrpt.sql  

  步骤1：@?/rdbms/admin/awrsqrpt.sql
  步骤2：选择你要的断点（begin snap 和end snap)
  步骤3：输入你的sql_id    
0 0
c语言大型程序源代码 c语言大型程序源代码
原创粉丝点击
热门IT博客
qq蛇蛇争霸网络不稳定qq蛇蛇争霸网络不稳定
java实战项目java实战项目
怎么加入psk网络
网络系统架构师
淘宝到货多久确认收货
程序员的思维修炼 mobi
优酷网络大电影分成
网络直播受众范围
红粉世家 知乎
若宇网络
网络报案中心我要报案
虚拟机安装linux黑屏
windows缺少dll文件
photoshop cc mac
linux强制杀死进程
及时雨淘宝
怀化网络家教招聘
c语言入门自学在线阅读
sweet devil镜头数据
数据结构与算法基础
热门问题 老师的惩罚 人脸识别 我在镇武司摸鱼那些年 重生之率土为王 我在大康的咸鱼生活 盘龙之生命进化 天生仙种 凡人之先天五行 春回大明朝 姑娘不必设防，我是瞎子 复数英语 复数名词 diary的复数 dish复数 bee的复数 复数的乘法 dish的复数 西红柿复数 复数的共轭复数 mouth的复数 you的复数 hen复数 复数虚部 复数相乘 土豆复数 horse复数 food复数 flower复数 hen的复数 radio的复数 milk的复数 friend复数 绵羊的复数 fruit复数 holiday复数 复数平面 kilo复数 goose的复数 sofa复数 bamboo复数 british复数 desk复数 变复数 复数集 giraffe复数 芒果复数 food的复数 skirt复数 单复数同形 单词复数 shoes复数

程序博客网，程序员的互联网技术博客家园。csdn论坛精品 msdn技术资料都在这里