获取和解读Oracle中SQL的执行计划（译文）

oracleace 2023-02-20

697

摘要：本文介绍了获取Oracle中SQL的执行计划的方法，和如何解读执行计划。

原文网址：https://blogs.oracle.com/optimizer/post/how-do-i-display-and-read-the-execution-plans-for-a-sql-statement

作者：Maria Colgan，Oracle公司的Distinguished Product Manager，自1996年7.3版发布以来一直在Oracle公司工作。Maria的核心职责是编写关于Oracle数据库的资料和课件，以及这些资料在客户环境中的最佳实践。她还负责将客户和合作伙伴的反馈纳入产品的未来版本中。在此角色之前，她是Oracle数据库内存和Oracle查询优化器的产品经理。

译者，姚远：

Oracle ACE（Oracle和MySQL数据库方向）
华为云MVP
《MySQL 8.0运维与优化》的作者
中国唯一一位Oracle高可用大师
拥有包括 Oracle 10g和12c OCM在内的20+数据库相关认证。
曾任IBM公司数据库部门经理
现在一家第三方公司任首席数据库专家，服务2万+客户。

生成和显示 SQL 语句的执行计划是大多数 DBA、SQL 开发人员和性能专家的常见任务，因为它提供了 SQL 语句执行性能相关的信息。执行计划显示执行 SQL 语句的详细步骤，这些步骤表示为一组使用和生成行的数据库运算符。运算符的顺序和实现由查询优化器根据查询转换和物理优化技术来决定。

虽然显示通常以表格格式显示，但计划实际上是树形的。例如，以下是基于 SH 架构（销售历史记录）的查询：

select prod_category, avg(amount_sold)
from sales s, products p
where p.prod_id = s.prod_id
group by prod_category;

此查询计划的表格表示形式为：

------------------------------------------
 Id   Operation              Name   
------------------------------------------
   0  SELECT STATEMENT              
   1   HASH GROUP BY                
   2    HASH JOIN                   
   3     TABLE ACCESS FULL   PRODUCTS
   4     PARTITION RANGE ALL        
   5      TABLE ACCESS FULL  SALES  
------------------------------------------

而计划的树形表示是：

GROUP BY
      |
     JOIN
 _____|_______
 |            |
ACCESS     ACCESS
(PRODUCTS) (SALES)

当你阅读计划树时，你应该从下到上开始。在上面的示例中，首先查看ACCESS运算符（或树叶）。在这种情况下，ACESS运算符是使用全表扫描实现的。这些表扫描生成的行将交给JOIN运算符使用。这里的JOIN运算符是哈希(HASH)连接（其他替代方法包括嵌套循环 nested-loop或排序合并 sort-merge连接）。最后，使用哈希实现的GROUP BY运算符（替代方法是排序）处理JOIN运算符生成的行。

SQL 语句执行时采用的执行计划只是查询优化器考虑的众多执行计划中的一个。查询优化器根据执行过程的 IO、CPU 和网络使用情况计算成本，然后选择开销最低的执行计划。

有两种不同的方法可用于查看 SQL 语句的执行计划：

EXPLAIN PLAN 命令：显示 SQL 语句的执行计划，而不实际执行该语句。
V$SQL_PLAN：一个字典视图，显示已编译为保存在游标缓存中的游标的 SQL 语句的执行计划。

在某些情况下，使用 EXPLAIN PLAN 命令时显示的计划可能与使用 V$SQL_PLAN显示的计划不同，例如前者在计划生成过程中不会考虑绑定变量值，而后者会。

使用DBMS_XPLAN包可以很容易显示执行计划，这个软件包提供了几个PL SQL存储过程来显示不同来源的计划：

EXPLAIN PLAN 命令
V$SQL_PLAN
自动工作负载存储库（AWR）
SQL 优化集（STS）
SQL 计划基线（SPM）

以下示例说明了如何使用 dbms_xplan 包中提供的不同函数生成和显示原始 SQL 语句的执行计划。
示例 1：使用EXPLAIN PLAN命令和DBMS_XPLAN.DISPLAY函数。

SQL> EXPLAIN PLAN FOR
 2   select prod_category, avg(amount_sold)
 3   from sales s, products p
 4   where p.prod_id = s.prod_id
 5   group by prod_category;


Explained.


SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'basic'));


------------------------------------------
 Id   Operation              Name   
------------------------------------------
   0  SELECT STATEMENT              
   1   HASH GROUP BY                
   2    HASH JOIN                   
   3     TABLE ACCESS FULL   PRODUCTS
   4     PARTITION RANGE ALL        
   5      TABLE ACCESS FULL  SALES  
------------------------------------------

DBMS_XPLAN.DISPLAY的参数：

Plan table name（默认为“PLAN_TABLE”）
Statement_id（默认空）
format（默认为“TYPICAL”）

更多详细信息可以在 $ORACLE_HOME/rdbms/admin/dbmsxpln.sql 中找到。

示例 2：生成并显示会话中执行的最后一个 SQL 语句的执行计划：

SQL> select prod_category, avg(amount_sold)
 2   from sales s, products p
 3   where p.prod_id = s.prod_id
 4   group by prod_category;


no rows selected


SQL> select plan_table_output
 2    from table(dbms_xplan.display_cursor(null,null,'basic'));
------------------------------------------
 Id   Operation              Name   
------------------------------------------
   0  SELECT STATEMENT              
   1   HASH GROUP BY                
   2    HASH JOIN                   
   3     TABLE ACCESS FULL   PRODUCTS
   4     PARTITION RANGE ALL        
   5      TABLE ACCESS FULL  SALES  
------------------------------------------

DBMS_XPLAN.DISPLAY_CURSOR的参数：

SQL_ID（默认 NULL，表示在此会话中执行的最后一个 SQL 语句）
Child Number（默认为 0）
format（默认为“TYPICAL”）

详细信息位于 $ORACLE_HOME/rdbms/admin/dbmsxpln.sql。

示例 3：显示任何其他语句的执行计划需要直接或间接提供 SQL_ID：
直接：

SQL> select plan_table_output from
 2   table(dbms_xplan.display_cursor('fnrtqw9c233tt',null,'basic'));

间接：

SQL> select plan_table_output
 2   from v$sql s,
 3   table(dbms_xplan.display_cursor(s.sql_id,
 4                                  s.child_number, 'basic')) t
 5   where s.sql_text like 'select PROD_CATEGORY%';

例4：显示与 SQL 计划基线对应的执行计划。

SQL> alter session set optimizer_capture_sql_plan_baselines=true;


Session altered.


SQL> select prod_category, avg(amount_sold)
 2   from sales s, products p
 3   where p.prod_id = s.prod_id
 4   group by prod_category;


no rows selected

如果上述语句已执行多次，则将为其创建 SQL 计划基线，您可以使用以下查询进行验证：

SQL> select SQL_HANDLE, PLAN_NAME, ACCEPTED
 2   from dba_sql_plan_baselines
 3   where sql_text like 'select prod_category%';


SQL_HANDLE                     PLAN_NAME                      ACC
------------------------------ ------------------------------ ---
SYS_SQL_1899bb9331ed7772       SYS_SQL_PLAN_31ed7772f2c7a4c2  YES

上面创建的 SQL 计划基线的执行计划可以直接或间接显示：

直接：

select t.* from
table(dbms_xplan.display_sql_plan_baseline('SYS_SQL_1899bb9331ed7772',
                                           format => 'basic')) t

间接：

select t.*

    from (select distinct sql_handle
          from dba_sql_plan_baselines
          where sql_text like 'select prod_category%') pb,
         table(dbms_xplan.display_sql_plan_baseline(pb.sql_handle,
                                                    null,'basic')) t;

这两个语句中的任何一个的输出是：

----------------------------------------------------------------------------
SQL handle: SYS_SQL_1899bb9331ed7772
SQL text: select prod_category, avg(amount_sold) from sales s, products p
          where p.prod_id = s.prod_id group by prod_category
----------------------------------------------------------------------------
----------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_31ed7772f2c7a4c2
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
----------------------------------------------------------------------------
Plan hash value: 4073170114
---------------------------------------------------------
 Id   Operation                 Name               
---------------------------------------------------------
   0  SELECT STATEMENT                             
   1   HASH GROUP BY                               
   2    HASH JOIN                                  
   3     VIEW                   index$_join$_002   
   4      HASH JOIN                                
   5       INDEX FAST FULL SCAN PRODUCTS_PK        
   6       INDEX FAST FULL SCAN PRODUCTS_PROD_CAT_IX
   7     PARTITION RANGE ALL                       
   8      TABLE ACCESS FULL     SALES              
---------------------------------------------------------

格式（Formatting）

格式参数是高度可定制的，允许您在计划输出中看到您想要的尽可能少的（高级）或尽可能多的（低级）详细信息。高级选项包括：

1.BASIC

计划包括操作、选项和对象名称（表、索引、物理视图等）

2.TYPICAL

它包括 BASIC 中显示的信息以及其他与优化器相关的内部信息，如成本、大小、基数等。显示每步的操作成本、生成的行数等。它还显示对操作的谓词估算，有两种类型的谓词：ACCESS 和 FILTER。索引的 ACCESS 谓词用于搜索的相关块，FILTER 谓词用于获取的块。

3.ALL

它包括 TYPICAL 中显示的信息以及每个操作生成的表达式（列）列表、hint别名和操作所属的查询块名称。最后两条信息可以用作向语句添加提示的参数。

低级选项指定包含或排除的细节，例如谓词和成本。

select plan_table_output
from table(dbms_xplan.display('plan_table',null,'basic +predicate +cost'));


-------------------------------------------------------
 Id   Operation              Name      Cost (%CPU)
-------------------------------------------------------
   0  SELECT STATEMENT                    17  (18)
   1   HASH GROUP BY                      17  (18)
*  2    HASH JOIN                         15   (7)
   3     TABLE ACCESS FULL   PRODUCTS      9   (0)
   4     PARTITION RANGE ALL               5   (0)
   5      TABLE ACCESS FULL  SALES         5   (0)
-------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("P"."PROD_ID"="S"."PROD_ID")

select plan_table_output from
table(dbms_xplan.display('plan_table',null,'typical -cost -bytes'));


----------------------------------------------------------------------------
 Id   Operation              Name      Rows  Time      Pstart Pstop
----------------------------------------------------------------------------
   0  SELECT STATEMENT                    4  00:00:01             
   1   HASH GROUP BY                      4  00:00:01             
*  2    HASH JOIN                       960  00:00:01             
   3     TABLE ACCESS FULL   PRODUCTS   766  00:00:01             
   4     PARTITION RANGE ALL            960  00:00:01      1     16
   5      TABLE ACCESS FULL  SALES      960  00:00:01      1     16
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("P"."PROD_ID"="S"."PROD_ID")

Note部分

除了计划之外，包还会在“Note”部分中显示说明，例如在查询优化期间使用了动态采样或对查询应用了星形转换。

例如，如果表 SALES 没有统计信息，则优化程序将使用动态抽样，计划显示将按如下方式报告（请参阅查询中的 '+note' 详细信息）：

select plan_table_output
from table(dbms_xplan.display('plan_table',null,'basic +note'));


------------------------------------------
 Id   Operation              Name   
------------------------------------------
   0  SELECT STATEMENT              
   1   HASH GROUP BY                
   2    HASH JOIN                   
   3     TABLE ACCESS FULL   PRODUCTS
   4     PARTITION RANGE ALL        
   5      TABLE ACCESS FULL  SALES  
------------------------------------------
Note
-----
- dynamic sampling used for this statement

绑定偷窥

查询优化器在生成执行计划时会考虑绑定变量值的值。它执行通常称为绑定偷窥的操作。请参阅关于绑定窥视及其对计划和 SQL 语句性能的影响的文章(https://blogs.oracle.com/optimizer/why-are-there-more-cursors-in-11g-for-my-query-containing-bind-variables)。

如前所述，V$SQL_PLAN 中显示的计划考虑了绑定变量的值，而使用 EXPLAIN PLAN 显示的计划则没有。DBMS_XPLAN包可显示用于生成计划的绑定变量值，方法是给display_cursor()的format参数添加“+peeked_binds”。

以下示例对此进行了说明：

variable pcat varchar2(50)
exec :pcat := 'Women'


select PROD_CATEGORY, avg(amount_sold)
from sales s, products p
where p.PROD_ID = s.PROD_ID
and prod_category != :pcat
group by PROD_CATEGORY;
select plan_table_output
from table(dbms_xplan.display_cursor(null,null,'basic +PEEKED_BINDS'));


------------------------------------------
 Id   Operation              Name   
------------------------------------------
   0  SELECT STATEMENT              
   1   HASH GROUP BY                
   2    HASH JOIN                   
   3     TABLE ACCESS FULL   PRODUCTS
   4     PARTITION RANGE ALL        
   5      TABLE ACCESS FULL  SALES  
------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :PCAT (VARCHAR2(30), CSID=2): 'Women'