• Oracle 生成和显示执行计划的方法

    Oracle 生成和显示执行计划的方法

    分类： Oracle 执行计划 2013-03-02 08:39 264人阅读 评论(0) 收藏 举报

    Oracle 生成和显示执行计划的方法

    生成执行计划

    一条合法的语句在执行之后，就会在内存中至少产生一条执行计划，可以从视图v$sql_plan查询。每一条执行计划对于一个游标。一条语句生产的第一个游标的CHILD_NUMBER（即v$sql_plan中的CHILD_NUMBER）为0，同一条sql语句可能因为环境或版本等其他因素不同而产生不同的执行计划，也就是说一条sql可能有多个CHILD_NUMBER。

    除执行过的sql会在内存中生成执行计划外，还可以通过explain plan让优化器对sql语句进行解析，生成查询计划。执行explain plan命令后，oracle会将解释生成的执行计划插入sys.plan_table$（10g之前，表名为plan_table；10g之后，通过公共同义词plan_table指向sys.plan_table$）中。explain plan产生的执行计划不会在语句执行时重用，而是以类似explain plan for<SQL>的形式在缓存中。

    显示执行计划

     通过DBMS_XPLAN包显示执行计划（推荐）

    DBMS_XPLAN含有5个函数用于输出格式化的执行计划，如下：

    display：显示explain plan命令解释的执行计划

    display_cursor：显示内存中的执行计划

    display_awr：awr历史数据中的执行计划

    display_sqlset：SQL优化集中语句的执行计划

    display_sql_plan_baseline：SQL执行计划基线

    display的用法

    语法：

    DBMS_XPLAN.DISPLAY(
   table_name    IN  VARCHAR2  DEFAULT 'PLAN_TABLE',
   statement_id  IN  VARCHAR2  DEFAULT  NULL, 
   format        IN  VARCHAR2  DEFAULT  'TYPICAL',
   filter_preds  IN  VARCHAR2 DEFAULT NULL);
    参数：
    ParameterDescription

    table_name

    存储查询计划的表名，默认值为PLAN_TABLE

    statement_id

    要显示查询计划的SQL语句的ID，如果为空，就会获取最近一条被解释的语句

    format

    输出格式，有以下几种预定义的格式可选择，还可以自定义（这里不多说）:

    • BASIC:基本格式，内容最少.

    • TYPICAL: 典型格式，通常够用，默认格式

    • SERIAL: 串行执行格式，这种格式和典型格式输出内容基本一致，只是对于并行查询，不会输出相关的并行内容

    • ALL: 完全格式，输出内容相对完整

    filter_preds

    		 该参数用谓词条件过滤存储执行计划的表，例如'plan_id = 10或cost>100（显示估算代价大于100的操作），该值默认为空


    示例：

    [html] view plaincopyprint?
    1. 用explain plan执行sql语句:  
    2.   
    3. EXPLAIN PLAN FOR  
    4. SELECT * FROM emp e, dept d  
    5.    WHERE e.deptno = d.deptno  
    6.    AND e.ename='benoit';  
    7. Display the plan using the DBMS_XPLAN.DISPLAY table function  
    8.   
    9. SET LINESIZE 130  
    10. SET PAGESIZE 0  
    11. SELECT * FROM table(DBMS_XPLAN.DISPLAY);  
    12. 输出结果如下  
    13.   
    14. Plan hash value: 3693697075  
    15. ---------------------------------------------------------------------------  
    16. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
    17. ---------------------------------------------------------------------------  
    18. |   0 | SELECT STATEMENT   |      |     1 |    57 |     6  (34)| 00:00:01 |  
    19. |*  1 |  HASH JOIN         |      |     1 |    57 |     6  (34)| 00:00:01 |  
    20. |*  2 |   TABLE ACCESS FULL| EMP  |     1 |    37 |     3  (34)| 00:00:01 |  
    21. |   3 |   TABLE ACCESS FULL| DEPT |     4 |    80 |     3  (34)| 00:00:01 |  
    22. ---------------------------------------------------------------------------  
    23.   
    24. Predicate Information (identified by operation id):  
    25. ---------------------------------------------------  
    26.    1 - access("E"."DEPTNO"="D"."DEPTNO")  
    27.    2 - filter("E"."ENAME"='benoit')  
    28.   
    29. 15 rows selected.  
    用explain plan执行sql语句:

EXPLAIN PLAN FOR
SELECT * FROM emp e, dept d
   WHERE e.deptno = d.deptno
   AND e.ename='benoit';
Display the plan using the DBMS_XPLAN.DISPLAY table function

SET LINESIZE 130
SET PAGESIZE 0
SELECT * FROM table(DBMS_XPLAN.DISPLAY);
输出结果如下

Plan hash value: 3693697075
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    57 |     6  (34)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     1 |    57 |     6  (34)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| EMP  |     1 |    37 |     3  (34)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPT |     4 |    80 |     3  (34)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("E"."DEPTNO"="D"."DEPTNO")
   2 - filter("E"."ENAME"='benoit')

15 rows selected.

    display_cursor的用法

    display_cursor可以显示内存中一个或多个游标的执行计划.

    语法：

    DBMS_XPLAN.DISPLAY_CURSOR(
   sql_id        IN  VARCHAR2  DEFAULT  NULL,
   child_number  IN  NUMBER    DEFAULT  NULL, 
   format        IN  VARCHAR2  DEFAULT  'TYPICAL');
    参数：
    ParameterDescription

    sql_id

    要显示执行计划的sql语句的ID

    child_number

    子游标序号

    format

    输出格式

    同于display输出格式
       


    示例：

    [html] view plaincopyprint?
    1. <SPAN style="COLOR: #009900">display_cursor 默认是获得由本session执行的之后一条语句的执行计划:</SPAN>  
    2.   
    3. SELECT ename  FROM  emp e, dept d   
    4.    WHERE   e.deptno = d.deptno    
    5.    AND   e.empno=7369;  
    6.   
    7. ENAME  
    8. ----------  
    9. SMITH  
    10.   
    11. SET PAGESIZE 0  
    12. SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR);  
    13. <SPAN style="COLOR: #009900">输出结果如下：</SPAN>  
    14. Plan hash value: 3693697075, SQL hash value: 2096952573, child number: 0  
    15. ------------------------------------------------------------------  
    16. SELECT ename FROM emp e, dept d WHERE e.deptno = d.deptno  
    17. AND e.empno=7369  
    18.   
    19. ---------------------------------------------------------------------------  
    20. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
    21. ---------------------------------------------------------------------------  
    22. |   0 | SELECT STATEMENT   |      |       |       |            |          |  
    23. |*  1 |  HASH JOIN         |      |     1 |    16 |     6  (34)| 00:00:01 |  
    24. |*  2 |   TABLE ACCESS FULL| EMP  |     1 |    13 |     3  (34)| 00:00:01 |  
    25. |   3 |   TABLE ACCESS FULL| DEPT |     4 |    12 |     3  (34)| 00:00:01 |  
    26. ---------------------------------------------------------------------------  
    27.   
    28. Predicate Information (identified by operation id):  
    29. ---------------------------------------------------  
    30.    1 - access("E"."DEPTNO"="D"."DEPTNO")  
    31.    2 - filter("E"."EMPNO"=7369)  
    32.   
    33. 21 rows selected.  
    34.   
    35.   
    36. <SPAN style="COLOR: #009900">还可以用下面的方式获得SQL_ID和CHILD_NUMBER</SPAN>  
    37.   
    38. SELECT /* TOTO */ ename, dname   
    39. FROM dept d join emp e USING (deptno);  
    40.   
    41. <SPAN style="COLOR: #009900">用上面的语句得到 sql_id和child_number:</SPAN>  
    42.   
    43. SELECT sql_id, child_number  
    44. FROM v$sql   
    45. WHERE sql_text LIKE '%TOTO%';  
    46.   
    47. SQL_ID         CHILD_NUMBER  
    48. ----------     -----------------------------  
    49. gwp663cqh5qbf   0  
    50.   
    51.   
    52. SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(('gwp663cqh5qbf',0));  
    53.   
    54. Plan hash value: 3693697075, SQL ID: gwp663cqh5qbf, child number: 0  
    55. --------------------------------------------------------  
    56. SELECT /* TOTO */ ename, dname   
    57. FROM dept d JOIN emp e USING (deptno);  
    58.   
    59. ----------------------------------------------------------------------------  
    60. | Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
    61. ----------------------------------------------------------------------------  
    62. |   0 | SELECT STATEMENT    |      |       |       |     7 (100)|          |  
    63. |   1 |  SORT GROUP BY      |      |     4 |    64 |     7  (43)| 00:00:01 |  
    64. |*  2 |   HASH JOIN         |      |    14 |   224 |     6  (34)| 00:00:01 |  
    65. |   3 |    TABLE ACCESS FULL| DEPT |     4 |    44 |     3  (34)| 00:00:01 |  
    66. |   4 |    TABLE ACCESS FULL| EMP  |    14 |    70 |     3  (34)| 00:00:01 |  
    67. ----------------------------------------------------------------------------  
    68.   
    69. Predicate Information (identified by operation id):  
    70. ---------------------------------------------------  
    71.    2 - access("E"."DEPTNO"="D"."DEPTNO")  
    72.   
    73. <SPAN style="COLOR: #009900">上边的两步还可以用下面的方法一次性得到</SPAN>  
    74. SELECT t.*  
    75. FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t WHERE sql_text LIKE '%TOTO%';  
  • 相关阅读:
    Linux随笔 DNS搭建
    总算亲自看见了一个网站被黑后的页面。
    [转]ＸＰ如何禁止媒体文件预览
    用Word2007发Blog的配置方法（多图）。
    C#通过http访问olap
    测试Word2007
    用IronPython作为.Net的脚本语言。
    用批处理写的显示磁盘剩余空间的小程序。
    事开机时Num Lock键打开。
    快捷方便的对js文件进行语法检查。
  • 原文地址：https://www.cnblogs.com/weixun/p/3274971.html
Copyright © 2020-2023  润新知