暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

Oracle 19C 使用EXPLAIN PLAN语句生成计划输出

原创 Asher.HU 2021-02-04
1436


EXPLAIN PLAN语句使您可以检查优化器为SQL语句选择的执行计划。

6.2.1关于EXPLAIN PLAN声明

EXPLAIN PLAN声明显示执行计划优化程序选择了SELECTUPDATEINSERT,和DELETE语句。

EXPLAIN PLAN输出说明了解释该语句后数据库如何运行SQL语句。由于执行环境和解释计划环境的不同,解释的计划可能与语句执行期间使用的实际计划不同(即不是真实的执行计划)。

EXPLAIN PLAN发出语句时,优化器选择一个执行计划,然后将描述执行计划每个步骤的行插入指定的计划表中。您还可以将EXPLAIN PLAN语句作为SQL跟踪工具的一部分发布。

EXPLAIN PLAN语句是DML语句,而不是DDL语句。因此,Oracle数据库不会隐式提交EXPLAIN PLAN语句所做的更改


也可以看看:


6.2.1.1关于PLAN_TABLE

PLAN_TABLE是默认的示例输出表,该EXPLAIN PLAN语句在其中插入描述执行计划的行。

Oracle数据库会PLAN_TABLE$SYS架构中自动创建一个全局临时表,并PLAN_TABLE作为同义词创建PLAN_TABLE授予的所有必要特权PUBLIC因此,每个会话都PLAN_TABLE在其临时表空间中获得其自己的私有副本

您可以使用SQL脚本catplan.sql手动创建全局临时表和PLAN_TABLE同义词。该脚本的名称和位置取决于您的操作系统。在UNIX和Linux上,脚本位于$ORACLE_HOME/rdbms/admin目录中。例如,启动一个SQL * Plus会话,以SYSDBA特权连接,然后如下运行脚本:

@$ORACLE_HOME/rdbms/admin/catplan.sql
复制

PLAN_TABLE分发介质上的SQL脚本中提供了示例输出表的定义您的输出表必须具有与此表相同的列名和数据类型。该脚本的通用名称是utlxplan.sql确切的名称和位置取决于您的操作系统。

也可以看看:

《 Oracle数据库SQL语言参考》提供了完整的EXPLAIN PLAN语法说明

 

6.2.1.2说明计划的限制

Oracle数据库不支持EXPLAIN PLAN执行日期绑定变量的隐式类型转换的语句。

通常,对于绑定变量,EXPLAIN PLAN输出可能不代表实际的执行计划

从SQL语句的文本中,TKPROF无法确定绑定变量的类型。它假定类型为VARCHAR,否则给出错误消息。您可以通过在SQL语句中进行适当的类型转换来避免这种限制。

也可以看看:


6.2.2解释SQL语句:基本步骤

使用EXPLAIN PLAN存储计划在SQL语句中PLAN_TABLE

先决条件

此任务假定PLAN_TABLE您的模式中存在一个名为的示例输出表如果该表不存在,请运行SQL脚本catplan.sql

要执行EXPLAIN PLAN,您必须具有以下特权:

  • 您必须具有必要的特权,才能将行插入指定用于保存执行计划的现有输出表中
  • 您还必须具有执行要为其确定执行计划的SQL语句所必需的特权。如果SQL语句访问视图,则您必须具有访问该视图所基于的任何表和视图的特权。如果该视图基于另一个基于表的视图,则您必须具有访问另一个视图及其基础表的权限。

要检查EXPLAIN PLAN语句产生的执行计划,您必须具有查询输出表所必需的特权。

解释说明:

  1. 启动SQL * Plus或SQL Developer,然后以具有必需权限的用户身份登录数据库。
  2. EXPLAIN PLAN FOR在SQL语句之前紧接子句。

    以下示例说明了查询employees的计划

    EXPLAIN PLAN FOR
      SELECT e.last_name, d.department_name, e.salary
      FROM   employees e, departments d
      WHERE  salary < 3000
      AND    e.department_id = d.department_id
      ORDER BY salary DESC;
    复制
  3. 发出该EXPLAIN PLAN语句后,使用Oracle数据库提供的脚本或软件包来显示最新计划表输出。

    以下示例使用该DBMS_XPLAN.DISPLAY函数:

    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'ALL'));
    复制
  4. 查看计划输出。

    例如,以下计划显示了哈希联接:

    SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'ALL'));
    Plan hash value: 3556827125
    
    -----------------------------------------------------------------------------------
    | Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |             |     4 |   124 |     5  (20)| 00:00:01 |
    |   1 |  SORT ORDER BY      |             |     4 |   124 |     5  (20)| 00:00:01 |
    |*  2 |   HASH JOIN         |             |     4 |   124 |     4   (0)| 00:00:01 |
    |*  3 |    TABLE ACCESS FULL| EMPLOYEES   |     4 |    60 |     2   (0)| 00:00:01 |
    |   4 |    TABLE ACCESS FULL| DEPARTMENTS |    27 |   432 |     2   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------
    
    Query Block Name / Object Alias (identified by operation id):
    -------------------------------------------------------------
    
       1 - SEL$1
       3 - SEL$1 / E@SEL$1
       4 - SEL$1 / D@SEL$1
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
       3 - filter("SALARY"<3000)
    
    Column Projection Information (identified by operation id):
    -----------------------------------------------------------
    
       1 - (#keys=1) INTERNAL_FUNCTION("E"."SALARY")[22],
           "E"."LAST_NAME"[VARCHAR2,25], "D"."DEPARTMENT_NAME"[VARCHAR2,30]
       2 - (#keys=1) "E"."LAST_NAME"[VARCHAR2,25], "SALARY"[NUMBER,22],
           "D"."DEPARTMENT_NAME"[VARCHAR2,30], "D"."DEPARTMENT_NAME"[VARCHAR2,30]
       3 - "E"."LAST_NAME"[VARCHAR2,25], "SALARY"[NUMBER,22],
           "E"."DEPARTMENT_ID"[NUMBER,22]
       4 - "D"."DEPARTMENT_ID"[NUMBER,22], "D"."DEPARTMENT_NAME"[VARCHAR2,30]
    
    Note
    -----
       - this is an adaptive plan
    
    复制

    EXPLAIN PLAN输出中的执行顺序从最右端缩进的行开始。下一步是该行的父级。如果两行均等缩进,那么通常首先执行顶行。

    注意:

    EXPLAIN PLAN本章输出中的步骤在数据库上可能有所不同。优化器可以根据数据库配置选择不同的执行计划。

也可以看看:


6.2.3在EXPLAIN PLAN中指定语句ID:示例

对于多条语句,您可以指定一条语句标识符,并使用该标识符标识您的特定执行计划。

在使用之前SET STATEMENT ID,请删除该语句ID的所有现有行。在以下示例中,st1将其指定为语句标识符。

示例6-1将EXPLAIN PLAN与STATEMENT ID子句一起使用

EXPLAIN PLAN
  SET STATEMENT_ID = 'st1' FOR
  SELECT last_name FROM employees;
复制


6.2.4为EXPLAIN PLAN输出指定其他位置:示例

INTO子句EXPLAIN PLAN指定用于存储输出的其他表。

如果您不想使用名称PLAN_TABLE,请在运行catplan.sql脚本后创建一个新的同义词例如:

CREATE OR REPLACE PUBLIC SYNONYM my_plan_table for plan_table$
复制

以下语句将输出定向到my_plan_table

EXPLAIN PLAN
  INTO my_plan_table FOR
  SELECT last_name FROM employees;
复制

您可以在使用INTO子句时指定语句ID ,如以下语句所示:

EXPLAIN PLAN
   SET STATEMENT_ID = 'st1'
   INTO my_plan_table FOR
   SELECT last_name FROM employees;
复制

也可以看看:

「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论