《Oracle高性能SQL引擎剖析：SQL优化与调优机制详解》一1.2　显示执行计划

1.2　显示执行计划

我们现在知道，有三个途径可以获取查询计划：v$sql_plan、dba_hist_sql_plan和PLAN_TABLE。如果需要读取一条SQL语句的执行计划，就需要知道该条语句的SQL_ID，如果该语句存在多个游标或者执行计划，则还需要知道游标的CHILD_NUMBER或计划的哈希值（可选）。而无论我们通过哪个途径来获取执行计划，显示方式主要是两种：语句查询和包DBMS_XPLAN显示。

1.2.1　通过查询语句显示计划

通过查询语句从一些视图里读出执行计划并作格式化输出的方法都非常相似，这里以v$sql_plan视图为例，示例程序见代码清单1-1。
代码清单1-1　显示执行计划（查询语句）

HELLODBA.COM>col "Query Plan_Table" format a30

-- 提示：SQL_ID可以从视图v$sql_text和dba_hist_sqltext（或stats$sqltext）等视图中查询获得。

HELLODBA.COM>select id,lpad(' ', 2*(level-1))||operation||' '||options||' '||
  2         object_name||' '||decode(id, 0, 'Cost='||cost) "Query Plan_Table"
  3  from v$sql_plan
  4  start with id = 0
  5         and sql_id = 'dq7gjn1yrpcyz'
  6         and plan_hash_value = 616708042
  7  connect by prior id = parent_id
  8         and sql_id = 'dq7gjn1yrpcyz'
  9         and plan_hash_value = 616708042;

        ID Query Plan_Table
---------- ------------------------------
         0 SELECT STATEMENT   Cost=2
         1   TABLE ACCESS FULL T_USERS

1.2.2　通过包DBMS_XPLAN显示计划

这个包可以根据我们选择的函数以及输入的参数来格式化显示相关的执行计划，在我们随后的内容中，主要会使用（也推荐读者使用）该工具显示执行计划。
DBMS_XPLAN含有5个函数用于输出格式化的执行计划，display、display_cursor、display_awr、display_sqlset和display_sql_plan_baseline，分别用于显示Explain Plan命令解释的计划、内存中的执行计划、AWR历史数据中的计划、SQL优化集中语句的计划、执行计划基线（关于SQL优化集和执行计划基线，我们会在后面第7章中具体介绍）。它们都是管道化表函数（Pipelined Table Function），返回的结果是一个系统自定义的集合数据类型dbms_xplan_type_table。我们可以通过表函数（Table）进行映射后进行查询。

1.2.2.1　DISPLAY

DISPLAY函数用于显示存储在PLAN_TABLE中的执行计划，或与PLAN_TABLE拥有相同结构的表中的执行计划。此外，如果从视图v$sql_plan_statistics_all可以获得该执行计划的相关统计数据，DISPLAY也可以格式化输出这些数据。
参数描述：
q TABLE_NAME：存储查询计划的表名（不区分大小写），默认值为PLAN_TABLE。
q STATEMENT_ID：SQL语句ID。在PLAN_TABLE中，每条语句的执行计划都会有一个唯一的ID来标识。这个ID可以在执行Explain Plan命令时，通过Set Statement_id子句来指定。如果输入为NULL，则会获取最近一条被解释的语句。
q FORMAT：输出格式。在DISPLAY函数中，有以下预定义的格式（模板）可供选择：
m 'BASIC'：基本格式。输出的内容最少，仅仅输出查询计划中每个操作的ID、名称和选项以及操作的对象名。
m 'TYPICAL'：典型格式。输出的内容是我们进行语句调优时大多数情况下所需要的信息。除了基本格式中的内容外，还会输出优化器估算出的每个操作的记录行数、字节数、代价和时间，以及相关的提示信息（如远程SQL、优化器建议等）。如果存在谓词（Predicate）条件，还会输出每个操作中的过滤（Filter）条件和访问（Access）条件。此外，如果查询涉及分区表，还会输出分区裁剪信息；如果查询涉及并行查询，还会输出并行操作的相关信息（如表队列信息、并行查询分布方式等）。这种格式是默认格式。
m 'SERIAL'：串行执行格式。这种格式和典型格式的输出内容基本一致，不同之处在于，对并行查询，它不会输出相关的并行内容。
m 'ALL'：完全格式。输出的内容相对完整。除了典型格式的内容以外，还会输出字段投射信息和别名信息。
除了这些预定义的格式外，用户还可以通过在格式化字符串中添加或者屏蔽一些关键词进行细化输出。每一个细化选项代表了输出内容中的单个信息（可能是执行计划表中的一个列，也可能是一个附加信息）。在DISPLAY函数中，以下细化控制选项可供选择：
m ROWS：优化器估算出的记录行数；
m BYTES：优化器估算出的字节数；
m COST：优化器估算出的代价；
m PARTITION：分区裁剪；
m PARALLEL：并行查询；
m PREDICATE：谓词；
m PROJECTION：字段投射；
m ALIAS：别名；
m REMOTE：分布式查询信息；
m NOTE：相关注释信息。
细化控制选项和预定格式一起使用。例如，如果你希望输出基本格式内容，并输出优化器估算出的记录行数，可以用“BASIC ROWS”作为格式字符串；而如果希望输出典型格式，但不要其中的谓词条件，则可以输入“TYPICAL -PREDICATE”作为格式字符串，即在希望被屏蔽信息的对应控制选项前加上“-”。
q FILTER_PREDS：该参数接收合法的谓词过滤条件（可以是谓词逻辑表达式，也可以包含子查询），以过滤从查询计划表中读取的内容。例如，可以输入“COST > 10”以限制输出所有估算代价大于10的操作。
示例见代码清单1-2。
代码清单1-2　显示执行计划（DISPLAY函数）

HELLODBA.COM>explain plan for select * from t_users where user_id=:A;
Explained.
HELLODBA.COM>select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
Plan hash value: 371495088

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |    86 |     1   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_USERS    |     1 |    86 |     1   (0)| 00:00:02 |
|*  2 |   INDEX UNIQUE SCAN         | T_USERS_PK |     1 |       |     1   (0)| 00:00:02 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("USER_ID"=TO_NUMBER(:A))

14 rows selected.

HELLODBA.COM>select * from table(dbms_xplan.display(null,null,'BASIC ROWS BYTES'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
Plan hash value: 371495088

------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes |
------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |    86 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_USERS    |     1 |    86 |
|   2 |   INDEX UNIQUE SCAN         | T_USERS_PK |     1 |       |
------------------------------------------------------------------

1.2.2.2　DISPLAY_CURSOR

DISPLAY_CURSOR函数可以显示内存中一个或者多个游标的执行计划。同样，可以通过输入参数限定SQL、游标以及输出格式。
用户必须对视图V$SQLV、$SQL_PLAN和V$SQL_PLAN_STATISTICS_ALL的SELECT有权限，才能正常调用DISPLAY_CURSOR函数。
参数描述：
q SQL_ID：所显示执行计划的SQL语句的ID。该ID可以从V$SQL.SQL_ID、V$SESSION.SQL_ID或者V$SESSION.PREV_SQL_ID获得。如果没有指定SQL_ID（指定NULL），则默认会显示当前会话中最后一条执行的SQL语句。
q CURSOR_CHILD_NO：语句的子游标序号。我们知道，受到执行环境的影响，一条SQL语句可能会产生多个版本的子游标，每个子游标都会与一个执行计划相映射（多个子游标也可能映射同一个执行计划）。通过CURSOR_CHILD_NO可以限制仅显示某一个子游标的执行计划。如果不指定该参数，则会显示该语句的所有子游标的执行计划。
q FORMAT：格式化控制字符串。DISPLAY函数的格式化控制字符串的所有选项都适用于DISPLAY_CURSOR函数。由于运行语句还可以通过提示GATHER_PLAN_STATISTICS或设置系统参数STATISTICS_LEVEL为“ALL”收集语句运行的性能统计数据，因此在细化选项中还有额外的选项，以选择是否输出这些数据。
m IOSTATS：是否输出计划的输入输出（IO）统计数据；
m MEMSTATS：在启用了PGA自动管理（参数pga_aggregate_target的值大于0）的情况下，是否输出计划的输入内存统计数据（操作的内存使用量、内存读次数等）；
m ALLSTATS：包含了IOSTATS和MEMSTATS的全部内容；
m LAST：以上三个选项输出的统计数据都是实际产生的数据，而非估算数据，它们是该游标所有执行所产生的数据的总和。你可以增加LAST选项以限定仅显示最后一次运行的统计数据。

 此外，还有一些未公布的选项可用于该函数的输出控制。首先是预定义格式：

m 'ADVANCED'：高级格式。高级格式除了会输出完全格式中的所有内容外，还会视情况输出绑定变量窥视信息和计划概要（Outline）信息；
m OUTLINE：是否以提示（HINT）的方式显示计划概要；
m PEEKED_BINDS：是否显示绑定变量窥视信息；
m BUFFSTATS：是否显示内存读次数（包括一致性读和当前读次数），该信息为IOSTATS的一部分；
m PLAN_HASH：是否显示计划的哈希值，该选项同样适用于DISPLAY函数。
示例见代码清单1-3。

1.2.2.3　DISPLAY_AWR

DISPLAY_AWR函数显示存储在AWR历史数据的执行计划。
提示：要正常调用DISPLAY_AWR参数，必须对以下视图有权限：DBA_HIST_SQL_PLAN和DBA_HIST_SQLTEXT的SELECT。
参数描述：
q SQL_ID：所显示执行计划的SQL语句的ID。该ID可以从DBA_HIST_SQL_PLAN.SQL_ID或DBA_HIST_SQLTEXT.SQL_ID获得，该参数必须指定非空值，没有默认值；
q PLAN_HASH_VALUE：执行计划的哈希值。我们之前提到，每个执行计划都有一个哈希值。通过该值，可以显示SQL语句的特定执行计划。如果该参数未指定或为NULL，则会显示语句的所有执行计划；
q DB_ID：指定显示哪个数据库的执行计划，默认为本地数据库ID；
提示：我们可以将其他数据库的AWR数据导入本地数据库进行分析。
q FORMAT：格式化控制字符串。与DISPLAY的相同选项类似。
示例见代码清单1-4。

1.2.2.4　DISPLAY_SQLSET

DISPLAY_SQLSET函数显示存储在一个SQL调优集中的语句的执行计划。
提示：DBMS_SQLTUNE是Oracle 10g中提供的一个自动调优的工具包，它可以对单条语句进行调优，也可以对一组SQL集进行调优，我们在后面章节会做详细介绍。
参数描述：
q SQLSET_NAME：SQL集的名称。每个SQL集都有一个单独的名称（可以是创建时用户指定的，也可以是系统自动生成的），我们需要指定从哪个SQL集中读取和显示语句的执行计划，该参数没有默认值，必须指定；
q SQL_ID：所显示执行计划的SQL语句的ID。该ID可以从USER/DBA/ALL_SQLSET_PLANS.SQL_ID获得，该参数必须指定非空值，没有默认值；
q PLAN_HASH_VALUE：执行计划的哈希值。如果未指定或为NULL，则会显示语句的所有执行计划；
q FORMAT：格式化控制字符串。与DISPLAY的FORMAT选项相同；
q SQLSET_OWNER：SQL集的所有者，默认为当前用户名。
示例见代码清单1-5。

1.2.2.5　DISPLAY_SQL_PLAN_BASELINE

DISPLAY_SQL_PLAN_BASELINE函数显示存储在数据字典当中SQL执行计划基线的计划。
提示：SQL执行计划管理是Oracle 11g中提供的一个新特性，用于管理SQL语句的一组执行计划（执行计划基线，Plan Baseline），保证语句运行性能稳定性。
参数描述：
q SQL_HANDLE：执行计划基线所属SQL的句柄名称，它由Oracle在创建或载入执行计划到基线当中时自动生成，可以通过视图dba_sql_plan_baselines查询，默认为NULL；
q PLAN_NAME：执行计划基线中某个执行计划的名称，它由Oracle在创建或载入执行计划到基线当中时自动生成，可以通过视图dba_sql_plan_baselines查询，默认为NULL；
q FORMAT：格式化控制字符串。DISPLAY_SQLSET函数的格式化选项与DISPLAY的选项相同。
当SQL_HANDLE和PLAN_NAME都为空时，显示所有基线数据中的全部执行计划。
示例见代码清单1-6。

1.2.3　AUTOTRACE

AUTOTRACE是Oracle自带的客户端工具SQLPlus的一个特性。启用AUTOTRACE后，SQLPLus会自动收集执行过的语句的执行计划、性能统计数据等，并在语句执行结束后显示在SQL*Plus中。
要使用AUTOTRACE，需要先做以下准备，用DBA用户创建角色PLUSTRCE，并将该角色赋予用户：
HELLODBA.COM>conn sys/sys as sysdba
Connected.

HELLODBA.COM>@?/SQLPLUS/ADMIN/PLUSTRCE.SQL
HELLODBA.COM>grant plustrace to demo;
Grant succeeded.
在执行语句之前，在SQL*Plus中打开AUTOTRACE。可以在打开AUTOTRACE时选择不同选项，以控制输出的内容。选项如下所示：
q SET AUTOTRACE ON：打开AUTOTRACE，并输出所有内容，包括语句本身的查询结果、执行计划，以及性能统计数据。
q SET AUTOTRACE ON EXPLAIN：打开AUTOTRACE，并输出语句本身的查询结果和执行计划，不输出性能统计数据。
q SET AUTOTRACE ON STATISTICS：打开AUTOTRACE，并输出语句本身的查询结果和性能统计数据，不输出执行计划。
q SET AUTOTRACE TRACE：打开AUTOTRACE，并输出执行计划和性能统计数据，不输出语句本身的查询结果。
q SET AUTOTRACE TRACE EXPLAIN：打开AUTOTRACE，并输出执行计划，不输出语句本身的查询结果和性能统计数据。
q SET AUTOTRACE TRACE STATISTICS：打开AUTOTRACE，并输出性能统计数据，不输出语句本身的查询结果和执行计划。
q SET AUTOTRACE OFF：关闭AUTOTRACE。
一个完整的AUTOTRACE报告输出包括三个部分：第一部分为SQL本身的执行结果；第二部分为SQL的执行计划；第三部分为SQL实际执行的性能统计数据。由于执行计划和执行的性能数据都是进行SQL调优时的重要参考信息，因此AUTOTRACE是进行SQL语句性能调优的一个非常实用的辅助方法。
提示：当打开AUTOTRACE后，在执行语句之前，Oracle会调用EXPLAIN PLAN命令对语句进行解析；在执行完成后，从PLAN_TABLE中查询和显示执行计划。因此，由于受到共享游标、绑定变量窥视等设置的影响，这一执行计划可能会与实际执行计划不同。

1.2.4　其他方法

除了上述方法外，我们还可以通过其他一些途径获取到语句的执行计划。但在这些方法所产生的数据里，执行计划通常仅是辅助我们解决问题的一个部分，而非重点。

1.2.4.1　SQL_TRACE（或者10046跟踪事件）

SQL_TRACE跟踪的内容由三个部分组成：执行语句时造成的等待事件（Waits）、执行语句时产生的性能统计数据，以及语句的执行计划和绑定变量信息。这里仅介绍执行计划相关部分。
在会话或者系统中启动SQL跟踪后，会话结束或者关闭SQL跟踪之前，会话（或系统）中所有运行的语句的性能统计数据都会记录到UDUMP目录（user_dump_dest参数指定）下一个跟踪文件中（未指定标识字符串tracefile_identifier的情况下，文件名格式为_ORA_.trc），从跟踪文件中，我们可以找到语句的执行计划。示例见代码清单1-7。

1.2.4.2　OPTIMIZER_TRACE（或者10053跟踪事件）

OPTIMIZER_TRACE可以跟踪优化器生成语句执行计划的整个过程，并且，在11g中还可以通过设置事件来指定仅跟踪一个或多个组件的信息。同样，其跟踪内容都会写入UDMP目录下的一个跟踪文件中，文件的命名方式和SQL_TRACE产生的跟踪文件的命名方式相同。示例见代码清单1-8。

时间： 2024-08-30 06:44:49