本站的合作过的网站：北风网极客学院柠檬学院图灵学院
开启辅助访问 最新回复帖子最新发表帖子

Java学习者论坛

 找回密码
 立即注册

QQ登录

只需一步，快速开始

手机号码，快捷登录

恭喜Java学习者论坛（https://www.javaxxz.com）已经为数万Java学习者服务超过8年了！积累会员资料超过10000G+！
成为本站VIP会员，下载本站10000G+会员资源，购买链接：点击进入购买VIP会员
Java学习者论坛»论坛 › 论坛互动 › 经验心得总结 › Oracle之SQL优化专题01-查看SQL执行计划的方法 ...
JAVA高级面试进阶视频教程Java架构师系统进阶VIP课程

分布式高可用全栈开发微服务教程

Go语言视频零基础入门到精通

Java架构师3期(课件+源码)

Java开发全终端实战租房项目视频教程

SpringBoot2.X入门到高级使用教程

大数据培训第六期全套视频教程

深度学习（CNN RNN GAN）算法原理

Java亿级流量电商系统视频教程

互联网架构师视频教程

年薪50万Spark2.0从入门到精通

年薪50万！人工智能学习路线教程

年薪50万！大数据从入门到精通学习路线年薪50万！机器学习入门到精通视频教程
仿小米商城类app和小程序视频教程深度学习数据分析基础到实战最新黑马javaEE2.1就业课程从 0到JVM实战高手教程 MySQL入门到精通教程
返回列表 发新帖
查看: 560|回复: 0

[默认分类] Oracle之SQL优化专题01-查看SQL执行计划的方法

[复制链接]
admin
  • TA的每日心情
    开心
    2021-12-13 21:45

    • 签到天数: 15 天

    [LV.4]偶尔看看III
    发表于 2018-4-2 11:33:38 | 显示全部楼层 |阅读模式
    本站已经为数万初学者服务超过7年！成为本站VIP会员，免金币下载本站10000G+VIP会员资料！
    在我2014年总结的“SQL Tuning 基础概述”中，其实已经介绍了一些查看SQL执行计划的方法，但是不够系统和全面，所以本次SQL优化专题，就首先要系统的介绍一下查看SQL执行计划的方法。
    本文示例SQL为：
    1. [code]--set lines 1000 pages 1000
    2. select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788;
    复制代码
    [/code]

    1.explain plan for
    2.set autotrace
    3.dbms_xplan
    3.1 dbms_xplan.display_cursor(null,null,"allstats last")
    3.2 dbms_xplan.display_cursor("&sql_id",null,"advanced")
    3.3 dbms_xplan.display_awr("&sql_id")
    4.awrsqrpt
    5.10046 event
    reference

    1.explain plan for SQL;
    通过explain plan for命令查看SQL的执行计划，这种方法SQL并不真实执行，一般适用于上线前的SQL预审，尤其对DML语句，由于SQL不执行，不用担心对生产数据造成影响。这种方法查看的执行计划有Predicate Information，无Statistics，查看到的执行计划不一定真实，第三方工具PL/SQL Developer中F5查看执行计划也是调用的这个方法。
    使用explain plan for命令查看SQL的执行计划：
    1. [code]explain plan for
    2. select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788;
    4. select * from table(dbms_xplan.display);
    复制代码
    [/code]
    示例如下：
    1. [code]JINGYU@jyzhao1 >explain plan for
    2.   2  select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788;
    4. Explained.
    6. Elapsed: 00:00:00.01
    7. JINGYU@jyzhao1 >select * from table(dbms_xplan.display);
    9. PLAN_TABLE_OUTPUT
    10. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    11. Plan hash value: 1123238657
    13. ---------------------------------------------------------------------------
    14. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    15. ---------------------------------------------------------------------------
    16. |   0 | SELECT STATEMENT   |      |     1 |    74 |     6   (0)| 00:00:01 |
    17. |*  1 |  HASH JOIN         |      |     1 |    74 |     6   (0)| 00:00:01 |
    18. |*  2 |   TABLE ACCESS FULL| EMP  |     1 |    52 |     3   (0)| 00:00:01 |
    19. |   3 |   TABLE ACCESS FULL| DEPT |     4 |    88 |     3   (0)| 00:00:01 |
    20. ---------------------------------------------------------------------------
    22. Predicate Information (identified by operation id):
    23. ---------------------------------------------------
    25.    1 - access("A"."DEPTNO"="B"."DEPTNO")
    26.    2 - filter("EMPNO"=7788)
    28. Note
    29. -----
    30.    - dynamic sampling used for this statement (level=2)
    32. 20 rows selected.
    34. Elapsed: 00:00:00.03
    35. JINGYU@jyzhao1 >
    复制代码
    [/code]
    2.set autotrace
    通过set autotrace查看SQL的执行计划，这种方法SQL真实执行（除set autot trace exp针对select语句也不执行，但对DML语句还是会执行的），这种方法查看的执行计划有Predicate Information，有Statistics，查看的执行计划是准确的。但由于SQL需要真实执行，所以对于有绑定变量值的SQL，还需要输入对应的变量值才可以执行，比较麻烦。
    使用set autotrace查看SQL的执行计划：
    1. [code]set autotrace on
    2. select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788;
    3. set autotrace off
    复制代码
    [/code]
    示例如下：
    1. [code]JINGYU@jyzhao1 >set autotrace on
    2. JINGYU@jyzhao1 >select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788;
    4.      EMPNO ENAME      DNAME          JOB              SAL
    5. ---------- ---------- -------------- --------- ----------
    6.       7788 SCOTT      RESEARCH       ANALYST         3000
    8. Elapsed: 00:00:00.01
    10. Execution Plan
    11. ----------------------------------------------------------
    12. Plan hash value: 1123238657
    14. ---------------------------------------------------------------------------
    15. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    16. ---------------------------------------------------------------------------
    17. |   0 | SELECT STATEMENT   |      |     1 |    74 |     6   (0)| 00:00:01 |
    18. |*  1 |  HASH JOIN         |      |     1 |    74 |     6   (0)| 00:00:01 |
    19. |*  2 |   TABLE ACCESS FULL| EMP  |     1 |    52 |     3   (0)| 00:00:01 |
    20. |   3 |   TABLE ACCESS FULL| DEPT |     4 |    88 |     3   (0)| 00:00:01 |
    21. ---------------------------------------------------------------------------
    23. Predicate Information (identified by operation id):
    24. ---------------------------------------------------
    26.    1 - access("A"."DEPTNO"="B"."DEPTNO")
    27.    2 - filter("EMPNO"=7788)
    29. Note
    30. -----
    31.    - dynamic sampling used for this statement (level=2)
    34. Statistics
    35. ----------------------------------------------------------
    36.           7  recursive calls
    37.           0  db block gets
    38.          15  consistent gets
    39.           0  physical reads
    40.           0  redo size
    41.         814  bytes sent via SQL*Net to client
    42.         520  bytes received via SQL*Net from client
    43.           2  SQL*Net roundtrips to/from client
    44.           0  sorts (memory)
    45.           0  sorts (disk)
    46.           1  rows processed
    48. JINGYU@jyzhao1 >set autotrace off
    49. JINGYU@jyzhao1 >
    复制代码
    [/code]
    3.dbms_xplan
    dbms_xplan下面有很多函数可以调用，其中最常用的是display_cursor和display_awr函数，下面依次介绍。

    3.1 dbms_xplan.display_cursor(null,null,"allstats last")

    通过dbms_xplan.display_cursor(null,null,"allstats last")查看SQL的执行计划，SQL会真实执行（对应的缺点不再赘述），这种方法查看的执行计划
    有Predicate Information，无Statistics，查看的执行计划是准确的，并且有每一步真实处理行数和时间。

    使用dbms_xplan.display_cursor(null,null,"allstats last")查看SQL的执行计划：
    1. [code]--需要确认statistics_level设置为ALL，否则SQL语句第一个关键字后就需要加上/*+ gather_plan_statistics */，我习惯用前者
    2. show parameter statistics_level（一般数据库默认是TYPICAL）
    3. alter session set statistics_level = ALL;
    4. select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788;
    5. select * from table(dbms_xplan.display_cursor(null,null,"allstats last"));
    复制代码
    [/code]
    示例如下：
    1. [code]JINGYU@jyzhao1 >alter session set statistics_level = ALL;
    3. Session altered.
    5. JINGYU@jyzhao1 >select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788;
    7.      EMPNO ENAME      DNAME          JOB              SAL
    8. ---------- ---------- -------------- --------- ----------
    9.       7788 SCOTT      RESEARCH       ANALYST         3000
    11. JINGYU@jyzhao1 >select * from table(dbms_xplan.display_cursor(null,null,"allstats last"));
    13. PLAN_TABLE_OUTPUT
    14. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    15. SQL_ID  39dv3d8jkzyuw, child number 1
    16. -------------------------------------
    17. select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where
    18. a.deptno = b.deptno and empno = 7788
    20. Plan hash value: 1123238657
    22. ----------------------------------------------------------------------------------------------------------------
    23. | Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    24. ----------------------------------------------------------------------------------------------------------------
    25. |   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |       7 |       |       |          |
    26. |*  1 |  HASH JOIN         |      |      1 |      1 |      1 |00:00:00.01 |       7 |  1214K|  1214K|  762K (0)|
    27. |*  2 |   TABLE ACCESS FULL| EMP  |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
    28. |   3 |   TABLE ACCESS FULL| DEPT |      1 |      4 |      4 |00:00:00.01 |       4 |       |       |          |
    29. ----------------------------------------------------------------------------------------------------------------
    31. Predicate Information (identified by operation id):
    32. ---------------------------------------------------
    34.    1 - access("A"."DEPTNO"="B"."DEPTNO")
    35.    2 - filter("EMPNO"=7788)
    37. Note
    38. -----
    39.    - dynamic sampling used for this statement (level=2)
    42. 26 rows selected.
    44. JINGYU@jyzhao1 >
    复制代码
    [/code]
    3.2 dbms_xplan.display_cursor("&sql_id",null,"advanced")
    通过dbms_xplan.display_cursor("&sql_id",null,"advanced")查看SQL的执行计划，这种方法查看的执行计划有Predicate Information，无Statistics，SQL是数据库中之前真实执行过的，对应执行计划是之前SQL真实执行过的执行计划，是准确的。使用这种方法的前提是需要SQL的cursor没有被刷出shared pool，否则查不到结果。
    使用dbms_xplan.display_cursor("&sql_id",null,"advanced")查看SQL的执行计划：
    1. [code]--查询sql_id
    2. select sql_id, sql_text from v$sql where sql_text like "select a.empno%";
    3. 39dv3d8jkzyuw
    4. --根据查询的sql_id查看执行计划
    5. select * from table(dbms_xplan.display_cursor("&sql_id",null,"advanced"));
    6. --附：函数DISPLAY_CURSOR的参数说明
    7. FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE
    8. Argument Name                  Type                    In/Out Default?
    9. ------------------------------ ----------------------- ------ --------
    10. SQL_ID                         VARCHAR2                IN     DEFAULT
    11. CURSOR_CHILD_NO                NUMBER(38)              IN     DEFAULT
    12. FORMAT                         VARCHAR2                IN     DEFAULT
    复制代码
    [/code]
    示例如下：
    1. [code]JINGYU@jyzhao1 >select * from table(dbms_xplan.display_cursor("&sql_id",null,"advanced"));
    2. Enter value for sql_id: 39dv3d8jkzyuw
    3. old   1: select * from table(dbms_xplan.display_cursor("&sql_id",null,"advanced"))
    4. new   1: select * from table(dbms_xplan.display_cursor("39dv3d8jkzyuw",null,"advanced"))
    6. PLAN_TABLE_OUTPUT
    7. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    8. SQL_ID  39dv3d8jkzyuw, child number 0
    9. -------------------------------------
    10. select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where
    11. a.deptno = b.deptno and empno = 7788
    13. Plan hash value: 1123238657
    15. ---------------------------------------------------------------------------
    16. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    17. ---------------------------------------------------------------------------
    18. |   0 | SELECT STATEMENT   |      |       |       |     6 (100)|          |
    19. |*  1 |  HASH JOIN         |      |     1 |    74 |     6   (0)| 00:00:01 |
    20. |*  2 |   TABLE ACCESS FULL| EMP  |     1 |    52 |     3   (0)| 00:00:01 |
    21. |   3 |   TABLE ACCESS FULL| DEPT |     4 |    88 |     3   (0)| 00:00:01 |
    22. ---------------------------------------------------------------------------
    24. Query Block Name / Object Alias (identified by operation id):
    25. -------------------------------------------------------------
    27.    1 - SEL$1
    28.    2 - SEL$1 / A@SEL$1
    29.    3 - SEL$1 / B@SEL$1
    31. Outline Data
    32. -------------
    34.   /*+
    35.       BEGIN_OUTLINE_DATA
    36.       IGNORE_OPTIM_EMBEDDED_HINTS
    37.       OPTIMIZER_FEATURES_ENABLE("11.2.0.4")
    38.       DB_VERSION("11.2.0.4")
    39.       ALL_ROWS
    40.       OUTLINE_LEAF(@"SEL$1")
    41.       FULL(@"SEL$1" "A"@"SEL$1")
    42.       FULL(@"SEL$1" "B"@"SEL$1")
    43.       LEADING(@"SEL$1" "A"@"SEL$1" "B"@"SEL$1")
    44.       USE_HASH(@"SEL$1" "B"@"SEL$1")
    45.       END_OUTLINE_DATA
    46.   */
    48. Predicate Information (identified by operation id):
    49. ---------------------------------------------------
    51.    1 - access("A"."DEPTNO"="B"."DEPTNO")
    52.    2 - filter("EMPNO"=7788)
    54. Column Projection Information (identified by operation id):
    55. -----------------------------------------------------------
    57.    1 - (#keys=1) "EMPNO"[NUMBER,22], "A"."ENAME"[VARCHAR2,10],
    58.        "A"."JOB"[VARCHAR2,9], "A"."SAL"[NUMBER,22], "B"."DNAME"[VARCHAR2,14]
    59.    2 - "EMPNO"[NUMBER,22], "A"."ENAME"[VARCHAR2,10],
    60.        "A"."JOB"[VARCHAR2,9], "A"."SAL"[NUMBER,22], "A"."DEPTNO"[NUMBER,22]
    61.    3 - "B"."DEPTNO"[NUMBER,22], "B"."DNAME"[VARCHAR2,14]
    63. Note
    64. -----
    65.    - dynamic sampling used for this statement (level=2)
    67. SQL_ID  39dv3d8jkzyuw, child number 1
    68. -------------------------------------
    69. select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where
    70. a.deptno = b.deptno and empno = 7788
    72. Plan hash value: 1123238657
    74. ---------------------------------------------------------------------------
    75. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    76. ---------------------------------------------------------------------------
    77. |   0 | SELECT STATEMENT   |      |       |       |     6 (100)|          |
    78. |*  1 |  HASH JOIN         |      |     1 |    74 |     6   (0)| 00:00:01 |
    79. |*  2 |   TABLE ACCESS FULL| EMP  |     1 |    52 |     3   (0)| 00:00:01 |
    80. |   3 |   TABLE ACCESS FULL| DEPT |     4 |    88 |     3   (0)| 00:00:01 |
    81. ---------------------------------------------------------------------------
    83. Query Block Name / Object Alias (identified by operation id):
    84. -------------------------------------------------------------
    86.    1 - SEL$1
    87.    2 - SEL$1 / A@SEL$1
    88.    3 - SEL$1 / B@SEL$1
    90. Outline Data
    91. -------------
    93.   /*+
    94.       BEGIN_OUTLINE_DATA
    95.       IGNORE_OPTIM_EMBEDDED_HINTS
    96.       OPTIMIZER_FEATURES_ENABLE("11.2.0.4")
    97.       DB_VERSION("11.2.0.4")
    98.       ALL_ROWS
    99.       OUTLINE_LEAF(@"SEL$1")
    100.       FULL(@"SEL$1" "A"@"SEL$1")
    101.       FULL(@"SEL$1" "B"@"SEL$1")
    102.       LEADING(@"SEL$1" "A"@"SEL$1" "B"@"SEL$1")
    103.       USE_HASH(@"SEL$1" "B"@"SEL$1")
    104.       END_OUTLINE_DATA
    105.   */
    107. Predicate Information (identified by operation id):
    108. ---------------------------------------------------
    110.    1 - access("A"."DEPTNO"="B"."DEPTNO")
    111.    2 - filter("EMPNO"=7788)
    113. Column Projection Information (identified by operation id):
    114. -----------------------------------------------------------
    116.    1 - (#keys=1) "EMPNO"[NUMBER,22], "A"."ENAME"[VARCHAR2,10],
    117.        "A"."JOB"[VARCHAR2,9], "A"."SAL"[NUMBER,22], "B"."DNAME"[VARCHAR2,14]
    118.    2 - "EMPNO"[NUMBER,22], "A"."ENAME"[VARCHAR2,10],
    119.        "A"."JOB"[VARCHAR2,9], "A"."SAL"[NUMBER,22], "A"."DEPTNO"[NUMBER,22]
    120.    3 - "B"."DEPTNO"[NUMBER,22], "B"."DNAME"[VARCHAR2,14]
    122. Note
    123. -----
    124.    - dynamic sampling used for this statement (level=2)
    126. SQL_ID  39dv3d8jkzyuw, child number 2
    127. -------------------------------------
    128. select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where
    129. a.deptno = b.deptno and empno = 7788
    131. Plan hash value: 1123238657
    133. ---------------------------------------------------------------------------
    134. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    135. ---------------------------------------------------------------------------
    136. |   0 | SELECT STATEMENT   |      |       |       |     6 (100)|          |
    137. |*  1 |  HASH JOIN         |      |     1 |    74 |     6   (0)| 00:00:01 |
    138. |*  2 |   TABLE ACCESS FULL| EMP  |     1 |    52 |     3   (0)| 00:00:01 |
    139. |   3 |   TABLE ACCESS FULL| DEPT |     4 |    88 |     3   (0)| 00:00:01 |
    140. ---------------------------------------------------------------------------
    142. Query Block Name / Object Alias (identified by operation id):
    143. -------------------------------------------------------------
    145.    1 - SEL$1
    146.    2 - SEL$1 / A@SEL$1
    147.    3 - SEL$1 / B@SEL$1
    149. Outline Data
    150. -------------
    152.   /*+
    153.       BEGIN_OUTLINE_DATA
    154.       IGNORE_OPTIM_EMBEDDED_HINTS
    155.       OPTIMIZER_FEATURES_ENABLE("11.2.0.4")
    156.       DB_VERSION("11.2.0.4")
    157.       ALL_ROWS
    158.       OUTLINE_LEAF(@"SEL$1")
    159.       FULL(@"SEL$1" "A"@"SEL$1")
    160.       FULL(@"SEL$1" "B"@"SEL$1")
    161.       LEADING(@"SEL$1" "A"@"SEL$1" "B"@"SEL$1")
    162.       USE_HASH(@"SEL$1" "B"@"SEL$1")
    163.       END_OUTLINE_DATA
    164.   */
    166. Predicate Information (identified by operation id):
    167. ---------------------------------------------------
    169.    1 - access("A"."DEPTNO"="B"."DEPTNO")
    170.    2 - filter("EMPNO"=7788)
    172. Column Projection Information (identified by operation id):
    173. -----------------------------------------------------------
    175.    1 - (#keys=1) "EMPNO"[NUMBER,22], "A"."ENAME"[VARCHAR2,10],
    176.        "A"."JOB"[VARCHAR2,9], "A"."SAL"[NUMBER,22], "B"."DNAME"[VARCHAR2,14]
    177.    2 - "EMPNO"[NUMBER,22], "A"."ENAME"[VARCHAR2,10],
    178.        "A"."JOB"[VARCHAR2,9], "A"."SAL"[NUMBER,22], "A"."DEPTNO"[NUMBER,22]
    179.    3 - "B"."DEPTNO"[NUMBER,22], "B"."DNAME"[VARCHAR2,14]
    181. Note
    182. -----
    183.    - dynamic sampling used for this statement (level=2)
    186. 177 rows selected.
    188. JINGYU@jyzhao1 >
    复制代码
    [/code]
    由于上面没有指定CURSOR_CHILD_NO，所以全部都会显示出来，当然也可以指定某个CURSOR_CHILD_NO，比如说：
    1. [code]select * from table(dbms_xplan.display_cursor("&sql_id",0,"advanced"));
    复制代码
    [/code]
    这样结果就只会显示CURSOR_CHILD_NO=0的执行计划。
    3.3 dbms_xplan.display_awr("&sql_id")
    通过dbms_xplan.display_awr("&sql_id")查看SQL的执行计划，这种方法查看的执行计划无Predicate Information，无Statistics，查看的执行计划是准确的。如果对应SQL没有被抓取到AWR中，就查不到结果。
    使用dbms_xplan.display_awr("&sql_id")查看SQL的执行计划：
    1. [code]--查询sql_id
    2. 39dv3d8jkzyuw
    3. --根据查询的sql_id查看执行计划
    4. select * from table(dbms_xplan.display_awr("&sql_id"));
    5. --附：函数DISPLAY_AWR的参数说明
    6. FUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE
    7. Argument Name                  Type                    In/Out Default?
    8. ------------------------------ ----------------------- ------ --------
    9. SQL_ID                         VARCHAR2                IN
    10. PLAN_HASH_VALUE                NUMBER(38)              IN     DEFAULT
    11. DB_ID                          NUMBER(38)              IN     DEFAULT
    12. FORMAT                         VARCHAR2                IN     DEFAULT
    复制代码
    [/code]
    示例如下：
    1. [code]JINGYU@jyzhao1 >select * from table(dbms_xplan.display_awr("&sql_id"));
    2. Enter value for sql_id: 39dv3d8jkzyuw
    4. PLAN_TABLE_OUTPUT
    5. ------------------------------------------------------------------------------
    6. SQL_ID 39dv3d8jkzyuw
    7. --------------------
    8. select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where
    9. a.deptno = b.deptno and empno = 7788
    11. Plan hash value: 1123238657
    13. ---------------------------------------------------------------------------
    14. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    15. ---------------------------------------------------------------------------
    16. |   0 | SELECT STATEMENT   |      |       |       |     6 (100)|          |
    17. |   1 |  HASH JOIN         |      |     1 |    74 |     6   (0)| 00:00:01 |
    18. |   2 |   TABLE ACCESS FULL| EMP  |     1 |    52 |     3   (0)| 00:00:01 |
    19. |   3 |   TABLE ACCESS FULL| DEPT |     4 |    88 |     3   (0)| 00:00:01 |
    20. ---------------------------------------------------------------------------
    22. Note
    23. -----
    24.    - dynamic sampling used for this statement (level=2)
    27. 20 rows selected.
    29. JINGYU@jyzhao1 >
    复制代码
    [/code]
    也可以加参数显示更多的信息，比如可以显示出Outline Data，但没有Predicate Information，没有Statistics：
    1. [code]JINGYU@jyzhao1 >select * from table(dbms_xplan.display_awr("&sql_id",NULL,NULL,"advanced"));
    2. Enter value for sql_id: 39dv3d8jkzyuw
    4. PLAN_TABLE_OUTPUT
    5. ------------------------------------------------------------------------------
    6. SQL_ID 39dv3d8jkzyuw
    7. --------------------
    8. select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where
    9. a.deptno = b.deptno and empno = 7788
    11. Plan hash value: 1123238657
    13. ---------------------------------------------------------------------------
    14. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    15. ---------------------------------------------------------------------------
    16. |   0 | SELECT STATEMENT   |      |       |       |     6 (100)|          |
    17. |   1 |  HASH JOIN         |      |     1 |    74 |     6   (0)| 00:00:01 |
    18. |   2 |   TABLE ACCESS FULL| EMP  |     1 |    52 |     3   (0)| 00:00:01 |
    19. |   3 |   TABLE ACCESS FULL| DEPT |     4 |    88 |     3   (0)| 00:00:01 |
    20. ---------------------------------------------------------------------------
    22. Query Block Name / Object Alias (identified by operation id):
    23. -------------------------------------------------------------
    25.    1 - SEL$1
    26.    2 - SEL$1 / A@SEL$1
    27.    3 - SEL$1 / B@SEL$1
    29. Outline Data
    30. -------------
    32.   /*+
    33.       BEGIN_OUTLINE_DATA
    34.       IGNORE_OPTIM_EMBEDDED_HINTS
    35.       OPTIMIZER_FEATURES_ENABLE("11.2.0.4")
    36.       DB_VERSION("11.2.0.4")
    37.       ALL_ROWS
    38.       OUTLINE_LEAF(@"SEL$1")
    39.       FULL(@"SEL$1" "A"@"SEL$1")
    40.       FULL(@"SEL$1" "B"@"SEL$1")
    41.       LEADING(@"SEL$1" "A"@"SEL$1" "B"@"SEL$1")
    42.       USE_HASH(@"SEL$1" "B"@"SEL$1")
    43.       END_OUTLINE_DATA
    44.   */
    46. Note
    47. -----
    48.    - dynamic sampling used for this statement (level=2)
    51. 44 rows selected.
    53. JINGYU@jyzhao1 >
    复制代码
    [/code]
    4.awrsqrpt
    通过awrsqrpt查看SQL的执行计划，这种方式是导出SQL的awr报告，这种方法查看的执行计划无Predicate Information，但有Statistics，并且同时包含SQL单次和整体的统计信息，查看的执行计划是准确的。
    使用awrsqrpt查看SQL的执行计划：
    @?/rdbms/admin/awrsqrpt
    1. [code]Specify the Begin and End Snapshot Ids
    2. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    3. Enter value for begin_snap: 240
    4. Begin Snapshot Id specified: 240
    6. Enter value for end_snap: 244
    7. End   Snapshot Id specified: 244
    11. Specify the SQL Id
    12. ~~~~~~~~~~~~~~~~~~
    13. Enter value for sql_id: 39dv3d8jkzyuw
    14. SQL ID specified:  39dv3d8jkzyuw
    15. ...
    16. Report written to awrsqlrpt_1_240_244.html
    复制代码
    [/code]
    这种执行计划的显示结果如下图：

    5.10046 event
    通过10046 event的跟踪文件查看SQL的执行计划，这种方法查看的执行计划是准确的，语句中函数，递归调用都会被详细列出，但没有谓词信息，而且这种方法获取执行计划比较麻烦。
    使用10046 event查看SQL的执行计划：
    1. [code]alter session set events "10046 trace name context forever, level 12";
    2. select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788;
    3. alter session set events "10046 trace name context off";
    4. --使用tkprof美化10046 event的跟踪文件，使其更易读：
    5. tkprof jyzhao1_ora_14139.trc jyzhao1_ora_14139.txt sys=no
    复制代码
    [/code]
    示例如下：
    1. [code]JINGYU@jyzhao1 >alter session set events "10046 trace name context forever, level 12";
    3. Session altered.
    5. JINGYU@jyzhao1 >select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788;
    7.      EMPNO ENAME      DNAME          JOB              SAL
    8. ---------- ---------- -------------- --------- ----------
    9.       7788 SCOTT      RESEARCH       ANALYST         3000
    11. JINGYU@jyzhao1 >alter session set events "10046 trace name context off";
    13. Session altered.
    15. --使用tkprof美化10046 event的跟踪文件相关内容如下：
    17. SQL ID: 39dv3d8jkzyuw Plan Hash: 1123238657
    19. select a.empno, a.ename, b.dname, a.job, a.sal
    20. from
    21. emp a, dept b where a.deptno = b.deptno and empno = 7788
    24. call     count       cpu    elapsed       disk      query    current        rows
    25. ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    26. Parse        1      0.01       0.02          0          8          0           0
    27. Execute      1      0.00       0.00          0          0          0           0
    28. Fetch        2      0.00       0.00          0          7          0           1
    29. ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    30. total        4      0.01       0.02          0         15          0           1
    32. Misses in library cache during parse: 1
    33. Optimizer mode: ALL_ROWS
    34. Parsing user id: 91  
    35. Number of plan statistics captured: 1
    37. Rows (1st) Rows (avg) Rows (max)  Row Source Operation
    38. ---------- ---------- ----------  ---------------------------------------------------
    39.          1          1          1  HASH JOIN  (cr=7 pr=0 pw=0 time=274 us cost=6 size=74 card=1)
    40.          1          1          1   TABLE ACCESS FULL EMP (cr=3 pr=0 pw=0 time=67 us cost=3 size=52 card=1)
    41.          4          4          4   TABLE ACCESS FULL DEPT (cr=4 pr=0 pw=0 time=15 us cost=3 size=88 card=4)
    44. Elapsed times include waiting on following events:
    45.   Event waited on                             Times   Max. Wait  Total Waited
    46.   ----------------------------------------   Waited  ----------  ------------
    47.   SQL*Net message to client                       2        0.00          0.00
    48.   SQL*Net message from client                     2        6.55          6.56
    49. ********************************************************************************
    复制代码
    [/code]
    关于阅读这个tkprof美化后文件的方法可以参考MOS文档：

    TKProf Interpretation (9i and above) (文档 ID 760786.1)

    reference
    关于这个“Oracle之SQL优化专题”的系列，缘起是听了公司SQL优化专家团队的分享内容，自己想通过实际操作理解并逐渐测试完善，在我个人之前的职业生涯规划中一直都偏重于运维方向的DBA，对SQL优化方面了解的很少，兴趣也不高，是SQL优化专家团队的这次分享让我觉得SQL优化也是很有趣的，所以下决心从今天起单独开一个专题，自己也能逐渐系统的学习相关知识并记录下来。初步考虑该系列后续内容同时还会参考崔华的《基于Oracle的SQL优化》一书。最后感谢SQL优化专家团队的leader勇哥和其团队所有成员，是你们的这次分享让我有了做这个专题的动力。
    数据库

    相关帖子
    回复

    使用道具 举报

    返回列表 发新帖
    高级模式
    B Color Image Link Quote Code Smilies
    您需要登录后才可以回帖 登录 | 立即注册

    本版积分规则

    QQ|手机版|Java学习者论坛 ( 声明：本站资料整理自互联网，用于Java学习者交流学习使用，对资料版权不负任何法律责任，若有侵权请及时联系客服屏蔽删除 )

    GMT+8, 2024-5-17 23:19 , Processed in 0.398493 second(s), 46 queries .

    Powered by Discuz! X3.4

    © 2001-2017 Comsenz Inc.

    快速回复 返回顶部 返回列表