Oracle SQL优化与调优之显示执行计划(上)

• 通过查询语句显示计划
      通过查询语句从这些视图里面读出执行计划并作格式化输出的方法都非常相似，我们这里以
  sql_plan 为例给出示例。
  

• 通过包 DBMS_XPLAN 显示计划
      这个包可以根据我们选择的函数以及输入的参数来格式化显示相关的执行计划，在我们随后的 内容中，主要会使用（也推荐读者使用）该工具显示执行计划。
  DBMS_XPLAN 含有 4 个函数用于格式化的执行计划的输出，display、display_cursor、
  display_awr 和 display_sqlset，分别用于显示 Explain Plan 命令解释的计划、内存中的执行计划、
  AWR 历史数据中的计划已经 SQL 优化集中语句的计划（关于 SQL 优化集，我们会在后面章节中具体介绍）。它们都是管道化表函数（Pipelined Table Function），返回的结果是一个系统自定的集合数据类型 dbms_xplan_type_table。我们可以通过表函数（Table）进行映射后进行查询。
  DISPLAY
      DISPLAY 函数用于显示存储在 PLAN_TABLE 或和 PLAN_TABLE 拥有相同结构的表中的执行计划。此外，如果从视图 v$sql_plan_statistics_all 可以获得该执行计划的相关统计数据，DISPLAY 也可以格式化输出这些数据。
  参数描述：
  o TABLE_NAME：存储查询计划的表名（不区分大小写），默认值为 PLAN_TABLE；
  o STATEMENT_ID：SQL 语句 ID。在 PLAN_TABLE 中，每条语句的执行计划都会有一个唯一的
  ID 来标识。这个 ID 可以在执行 Explain Plat 命令时，通过 Set Statement_id 子句来指定。如果输入为 NULL，则会获取最近一条被解释的语句。
  o FORMAT：输出格式。在 DISPLAY 函数中，有三种预定义的格式（模版）可供选择。
   ‘BASIC’：基本格式。输出的内容最少，仅仅输出查询计划中每个操作的 ID、名称和选项以及操作的对象名；
   ‘TYPICAL’：典型格式。输出的内容是我们进行语句调优的大多数情况下所需要的信息。 除了基本格式中的内容外，还会输出优化器估算出的每个操作的记录行数、字节数、代价和时间，以及相关的提示信息（如远程 SQL、优化器建议等），同时，如果存在谓词（Predicate）条件，还会输出每个操作中的过滤（Filter）条件和访问（Access）条 件。此外，如果查询涉及到分区表，还会输出分区裁剪信息；如果查询涉及到并行查询，还会输出并行操作的相关信息（如表队列信息、并行查询分布方式等）。这种格式是默认格式；
   ‘SERIAL’：串行执行格式。这种格式和典型格式的输出内容基本一致，不同之处在于， 对并行查询，它不会输出相关的并行内容。
   ‘ALL’：完全格式。输出的内容相对完整。除了典型格式的内容以外，还会输出字段投 射信息和别名信息。
  除了这预定义的格式模版外，用户还可以通过在格式化字符串中添加或者屏蔽一些关键词进行细化输出。每一个细化选项代表了输出内容中的单个信息（可能是执行计划表中 的一个列，也可能是一个附加信息）。DISPLAY 函数中，以下细化控制选项可供选择。
   ROWS：优化器估算出的记录行数；
   BYTES：优化器估算出的字节数；
   COST：优化器估算出的代价；
   PARTITION：分区裁剪信息；
   PARALLEL：并行查询信息；
   PREDICATE：谓词信息；
   PROJECTION：字段投影信息；
   ALIAS：别名信息；
   REMOTE：分布式查询信息；
   NOTE：相关注释信息。

    细化控制选项和预定格式模版一起使用。例如，如果你希望输出基本格式内容，并输出优化器估算出的记录行数，可以以’BASIC ROWS’作为格式字符串；而如果希望输出典型格式，但不要其中的谓词条件，则可以输入’TYPICAL -PREDICATION’作为格式字符串，即在希望被屏蔽信息的对应控制选项前加上’-’。

o FILTER_PREDS：该参数接收合法的谓词过滤条件（可以是谓词逻辑表达式，也可以包含子 查询），以过滤从查询计划表中读取的内容。例如，可以输入’COST > 10’以限制输出所有估算代价大于 10 的操作。

示例：

DISPLAY_CURSOR

    DISPLAY_CURSOR 函数可以显示内存中一个或者多个游标的执行计划。同样，可以通过输入参数限定 SQL、游标以及输出格式。
用户必须有对视图 VSQLV、SQL_PLAN 和 VSQL_PLAN_STATISTICS_ALL 的 SELECT 权限才能正常调用 DISPLAY_CURSOR 函数。 参数描述： o SQL_ID：需要被显示执行计划的 SQL 语句的 ID。该 ID 可以从 VSQL.SQL_ID、
VSESSION.SQL_ID 或者 VSESSION.PREV_SQL_ID 获得。如果没有指定 SQL_ID（指定 NULL）， 则默认会显示当前会话中最后一条被执行的 SQL 语句。
o CURSOR_CHILD_NO：语句的子游标序号。我们知道，收到执行环境的影响，一条 SQL 语句可能会产生多个版本的子游标，每个子游标都会与一个执行计划相映射（多个子游标也可

能映射同一个执行计划）。通过 CURSOR_CHILD_NO 可以限制仅显示某一个子游标的执行计划。如果不指定，则会显示该语句的所有子游标的执行计划。
o FORMAT：格式化控制字符串。DISPLAY 函数的格式化控制字符串的所有选项都适用于
DISPLAY_CURSOR 函数。由于运行语句还可以通过提示 GATHER_PLAN_STATISTICS 或设置系统参数 STATISTICS_LEVEL 为”ALL”来收集语句运行的性能统计数据，因此在细化选项中还有而外的选项选择是否输出这些数据。
 IOSTATS：是否输出计划的输入输出（IO）统计数据；
 MEMSTATS：在启用了 PGA 自动管理（参数 pga_aggregate_target 的值大于 0）的情况下，是否输出计划的输入内存统计数据（操作的内存使用量、内存读次数等）；
 ALLSTATS：包含了 IOSTATS 和 MEMSTATS 的全部内容。
 LAST：以上三个选项输出的统计数据都是实际产生的数据，而非估算数据，它们是该游标所有执行所产生的数据的总和。你可以增加 LAST 选项以限定仅显示最后一次运行的统计数据。

此外，还有一些未公布的选项可用于该函数的输出控制。首先是预定义格式。。
 ‘ADVANCED’：高级格式。高级格式除了会输出完全格式中的所有内容外，还会视情况 输出绑定变量窥视信息和计划纲要（Outline）信息。

同样，细化参数中也有一些未公布的选项。
 OUTLINE：是否以提示（HINT）的方式显示计划纲要；
 PEEKED_BINDS：是否显示绑定变量窥视信息；
 BUFFSTATS：是否显示内存内存读次数（包括一致性读和当前读次数），该信息为
IOSTATS 的一部分；
 PLAN_HASH：是否显示计划的哈希值，该选项同样适用于 DISPLAY 函数。

示例：