出品丨TeacherWhat
题图:Hands@Photo by Toa Heftiba on Unsplash
关键字: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
参考:
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 = ''
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
本文要点
本文介绍了Oracle中获取执行计划最基础的方法,EXPLAIN PLAN命令。
思考
除了EXPLAIN PLAN命令,你常用的还有哪些获取执行计划的方法?各种方法的优缺点是什么?
——End——
专注于技术不限于技术!
用碎片化的时间,一点一滴地提高数据库技术和个人能力。
欢迎关注!
SQL调优和诊断从哪入手?
你知道Oracle数据库除了SGA和PGA,还有MGA么?
Index Unique Scans我们要说的 | Oracle官方博客转载
2019年中国关系型数据库软件市场份额——来源IDC
2020年11月 数据库流行度排名
网罗收集10046的各种Case,方便trace信息的收集 | Oracle官方博客转载