获取SQL执行计划最基础的方法是啥？

SQLplusDB

发布于 2020-11-12 10:43:46

5240

发布于 2020-11-12 10:43:46

文章被收录于专栏：Oracle数据库技术Oracle数据库技术

出品丨TeacherWhat

关键字：Oracle、SQL、调优、诊断、手把手数据库入门、Database、Explain

正文约4500字，建议阅读时间6分钟

目录结构：

1. 使用EXPLAIN PLAN命令的准备

2. EXPLAIN PLAN FOR命令的使用方法

3. 查看EXPLAIN PLAN生成的执行计划

4. EXPLAIN PLAN使用例

5. 注意事项

6. 本文要点&思考

本公众号文章仅代表个人观点，与任何公司无关。

其他系列文章：SQL调优和诊断从哪入手？

使用EXPLAIN PLAN命令

获取执行计划最基础的方法是使用EXPLAIN PLAN命令。通过EXPLAIN PLAN命令，可以不实际执行SQL的前提下，查看某条SQL根据统计信息生成的执行计划。

使用EXPLAIN PLAN命令的准备

要使用EXPLAIN PLAN命令，需要首先创建执行计划表PLAN_TABLE 。这样通过EXPLAIN PLAN命令生成的执行计划会保存在表PLAN_TABLE 中。

一般情况下，执行计划表PLAN_TABLE是已经默认创建好的，也可以根据需要手动创建。

创建方法如下：

--10G之前的版本执行以下脚本
SQL> @@?/rdbms/admin/utlxplan

--10G以后的版本执行以下脚本
SQL> @@?/rdbms/admin/catplan

EXPLAIN PLAN FOR命令的使用方法

EXPLAIN PLAN FOR命令的语法如下：

▲引用自Database SQL Language Reference

例：SQL> explain plan for <SQL语句>

参考：

Database SQL Language Reference
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/EXPLAIN-PLAN.html#GUID-FD540872-4ED3-4936-96A2-362539931BA0

查看EXPLAIN PLAN生成的执行计划

EXPLAIN PLAN FOR命令执行后，可以通过以下方式查看生成的执行计划。

1 utlxpls.sql脚本
显示一般串行处理的执行计划。
SQL> @@?/rdbms/admin/utlxpls.sql

2 utlxplp.sql脚本
显示并行处理的执行计划。
@@?/rdbms/admin/utlxplp.sql

3 DBMS_XPLAN.DISPLAY包

3.1
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

3.2 
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL,'ALL'));

4 通过指定statement_id，直接查询PLAN_TABLE表。

SELECT cardinality "Rows",
lpad(' ',level-1)||operation||' '||options||' '||object_name "Plan"
FROM PLAN_TABLE
CONNECT BY prior id = parent_id
AND prior statement_id = statement_id
START WITH id = 0
AND statement_id = '<指定的statement_id >'
ORDER BY id;

EXPLAIN PLAN使用例

以下时EXPLAIN PLAN的使用例

--执行SQL
SQL> explain plan for
 select * from dual;  2

Explained.

---1.通过utlxpls.sql脚本显示执行计划
SQL> @@?/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

--2.通过utlxplp.sql脚本显示执行计划
SQL> @@?/rdbms/admin/utlxplp.sql

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.


--3.1 通过DBMS_XPLAN.DISPLAY()显示执行计划
SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

--3.2 通过DBMS_XPLAN.DISPLAY()显示执行计划
SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL,'ALL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - SEL$1 / DUAL@SEL$1

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "DUAL"."DUMMY"[VARCHAR2,1]

18 rows selected.

通过指定的statement_id，直接查询PLAN_TABLE表

--执行SQL
SQL> explain plan set statement_id = 'test' for
 select * from dual;  2

Explained.

--查询PLAN_TABLE表查看执行计划
SQL> set linesize 200
SQL> col Plan format a80
SQL> SELECT cardinality "Rows",
  2  lpad(' ',level-1)||operation||' '||options||' '||object_name "Plan"
FROM PLAN_TABLE
  3    4  CONNECT BY prior id = parent_id
AND prior statement_id = statement_id
  5    6  START WITH id = 0
  7  AND statement_id = 'test'
ORDER BY id;
  8
      Rows Plan
---------- --------------------------------------------------------------------------------
         1 SELECT STATEMENT
         1  TABLE ACCESS FULL DUAL

注意事项

EXPLAIN PLAN命令能够在不真正执行SQL的前提下获取执行计划，从而在最小的系统影响的前提下，帮助我们进行SQL调优和诊断。但是，EXPLAIN PLAN命令得到的是根据各种统计信息预估的执行计划，可能和实际执行时的执行计划不同。例如：包含绑定的SQL文或者在版本12c上，由于自适应执行计划(Adaptive Execution Plans)的影响等，EXPLAIN PLAN命令并不能反映出实际的执行计划。

其他详细请参考在线文档：

Database SQL Language Reference
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/EXPLAIN-PLAN.html#GUID-FD540872-4ED3-4936-96A2-362539931BA0