该EXPLAIN PLAN
语句使您可以检查优化器为SQL语句选择的执行计划。
6.2.1关于EXPLAIN PLAN声明
该EXPLAIN PLAN
声明显示执行计划优化程序选择了SELECT
,UPDATE
,INSERT
,和DELETE
语句。
EXPLAIN PLAN
输出说明了解释该语句后数据库如何运行SQL语句。由于执行环境和解释计划环境的不同,解释的计划可能与语句执行期间使用的实际计划不同(即不是真实的执行计划)。
EXPLAIN PLAN
发出该语句时,优化器选择一个执行计划,然后将描述执行计划每个步骤的行插入指定的计划表中。您还可以将EXPLAIN PLAN
语句作为SQL跟踪工具的一部分发布。
该EXPLAIN PLAN
语句是DML语句,而不是DDL语句。因此,Oracle数据库不会隐式提交EXPLAIN PLAN
语句所做的更改。
也可以看看:
- “ SQL行源生成 ”
- Oracle Database SQL语言参考以了解该
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语句中进行适当的类型转换来避免这种限制。
也可以看看:
- “ 执行应用程序跟踪 ”
- “ 避免争论陷阱的准则 ”
- Oracle数据库SQL语言参考,以了解有关SQL数据类型的更多信息
6.2.2解释SQL语句:基本步骤
使用EXPLAIN PLAN
存储计划在SQL语句中PLAN_TABLE
。
先决条件
此任务假定PLAN_TABLE
您的模式中存在一个名为的示例输出表。如果该表不存在,请运行SQL脚本catplan.sql
。
要执行EXPLAIN PLAN
,您必须具有以下特权:
- 您必须具有必要的特权,才能将行插入指定用于保存执行计划的现有输出表中
- 您还必须具有执行要为其确定执行计划的SQL语句所必需的特权。如果SQL语句访问视图,则您必须具有访问该视图所基于的任何表和视图的特权。如果该视图基于另一个基于表的视图,则您必须具有访问另一个视图及其基础表的权限。
要检查EXPLAIN PLAN
语句产生的执行计划,您必须具有查询输出表所必需的特权。
解释说明:
- 启动SQL * Plus或SQL Developer,然后以具有必需权限的用户身份登录数据库。
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;
复制- 发出该
EXPLAIN PLAN
语句后,使用Oracle数据库提供的脚本或软件包来显示最新计划表输出。以下示例使用该
DBMS_XPLAN.DISPLAY
函数:SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'ALL'));
复制 - 查看计划输出。
例如,以下计划显示了哈希联接:
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
本章输出中的步骤在数据库上可能有所不同。优化器可以根据数据库配置选择不同的执行计划。
也可以看看:
- “ 关于PLAN_TABLE ”
- “ 关于PLAN_TABLE输出的显示 ”
- 《 Oracle数据库SQL语言参考》中的语法和语义
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;
复制
也可以看看:
- “ PLAN_TABLE Columns ”中有关列的说明
PLAN_TABLE
- Oracle Database SQL语言参考以了解
CREATE SYNONYM