前提

SQL执行计划仍在Shared Pool中

函数体

示例：

1
2
3
4
DBMS_XPLAN.DISPLAY_CURSOR(
sql_id        IN  VARCHAR2  DEFAULT  NULL,
child_number  IN  NUMBER    DEFAULT  NULL,
format        IN  VARCHAR2  DEFAULT  'TYPICAL');

参数

  • sql_id
    指位于库缓存执行计划中 SQL 语句的父游标。默认值为 null。当使用默认值时当前会话的最后一条 SQL 语句的执行计划将被返回
    可以通过查询 V$SQL 或 V$SQLAREA 的 SQL_ID 列来获得 SQL 语句的 SQL_ID。
  • child_number
    指定父游标下子游标的序号。即指定被返回执行计划的 SQL 语句的子游标。默认值为 0。如果为 null，则 sql_id 所指父游标下所有子游标的执行计划都将被返回。
  • format
    控制 SQL 语句执行计划的输出部分。

Format 参数详解

  • 官方输出格式
    1. BASIC: 显示最少的信息，只包括操作类型，ID 名称和选项。
    2. TYPICAL: 默认值，显示相关信息以及某些附加的显示选项，如分区和并发使用等。
    3. SERIAL: 与 TYPICAL 类型相似，区别是它不包括并发的信息，即使是并行执行的计划。
    4. ALL: 显示最多的信息，包含了 TYPICAL 的全部以及更多的附加信息,如别名和远程调用等。
  • 附加输出格式（逗号和空格分隔来声明多个关键字，使用”+”和”-”符号来包含或排除相应的显示元素）
    1. ROWS – 显示被优化器估算的记录的行号
    2. BYTES – 显示优化器估算的字节数
    3. COST – 显示优化器计算的成本信息
    4. PARTITION – 显示分区的分割信息
    5. PARALLEL – 显示并行执行信息
    6. PREDICATE – 显示谓语
    7. PROJECTION – 显示列投影部分(每一行的那些列被传递给其父列已经这些列的大小)
    8. ALIAS – 显示查询块名称已经对象别名
    9. REMOTE – 显示分布式查询信息
    10. NOTE – 显示注释
    11. IOSTATS – 显示游标执行的 IO 统计信息
    12. MEMSTATS – 为内存密集运算如散列联结，排序，或一些类型的位图运算显示内存管理统计信息
    13. ALLSTATS – 与’IOSTATS MEMSTATS’等价
    14. LAST – 显示最后执行的执行计划统计信息，默认显示为 ALL 类型，并且可以累积。

拓展

dbms_xplan.display_cursor(null,null,'advanced') 仅用于 SQLplus 中查看刚执行过的 SQL 执行计划，在 PL/SQL Developer 中无法使用，因为工具在执行完 SQL 后还会执行其他的后台语句。

执行计划

dbms_xplan.display_cursor()通常搭配ALTER SESSION SET STATISTICS_LEVEL = ALL或 /*+ GATHER_PLAN_STATISTICS*/

执行计划

  1. starts：SQL 执行的次数；
  2. E-Rows：执行计划预计返回的行数；
  3. A-Rows：执行计划实际返回的行数；
  4. A-Time：每一步执行的时间(HH:MM:SS.FF)，根据这一行可知 SQL 耗时在哪些地方；
  5. Buffers：每一步实际执行的逻辑读或一致性读；
  6. Reads：物理读；
  7. OMem：OMem 为最优执行模式所需的内存评估值， 这个数据是由优化器统计数据以及前一次执行的性能数据估算得出的；
  8. 1Mem：1Mem 为 one-pass 模式所需的内存评估值，当工作区大小无法满足操作所需的大小时，需要将部分数据写入临时磁盘空间中(如果仅需要写入一次就可以完成操作，就称一次通过，One-Pass;否则为多次通过，Multi-Pass).该列数据为语句最后一次执行中，单次写磁盘所需要的内存大小，这个由优化器统计数据以及前一次执行的性能数据估算得出的
  9. Used_Mem：Used-Mem 则为当前操作实际执行时消耗的内存，括号里面为(发生磁盘交换的次数,1 次即为 One-Pass,大于 1 次则为 Multi_Pass,如果没有使用磁盘，则显示 0)

优缺点

  • 优点
    1. 可得到真实的执行计划
    2. 可以看出表被访问了多少次
    3. 可以通过 E-ROWS 和 A-RWS 得到预测行数和实际行数
    4. Buffers 和 Reads 也可以显示真实的逻辑读和物理读
  • 缺点
    1. 只有语句执行完毕才可以看到结果
    2. 看不出递归调用的次数
    3. 无法控制记录打屏输出