生成、查看、理解Oracle的执行计划

原创
02/20 17:31
阅读数 1.7K

SQL是如何被执行的

SQL语句只是定义了要查询什么样的数据,但是没有定义数据是怎样被查询出来。

  1. 当DB接收到一条SQL查询语句(INSERT, UPDATE, SELECT, DELETE)时,DB语法解析器(Parser)会先将其解析成一棵抽象语法树(Abstract Syntax Tree, AST),解析器在不影响执行结果的前提下可能会重写这个SQL来优化执行。
  2. 接下来这棵生成的语法树会被送到DB的基于开销的优化器(Cost-Based Optimizer, CBO),优化器会创建执行计划(Execution Plan),并通过选择join类型(Nested Loops, Merge Join, Hash Join等)、join顺序、是否使用索引、使用什么索引等方式找到使用开销(CPU, IO, 内存)最小的计划作为最佳的执行计划。
  3. 然后这个最佳的执行计划被送到DB的执行器(Executor),执行器执行这个计划,并将取到的数据返回给DB调用方。

SQL statement lifecycle

什么是执行计划

A statement's execution plan is the sequence of operations Oracle performs to run the statement. The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECTUPDATEINSERT, and DELETE statements.

执行计划是DB优化器生成的,为了完成一个SQL查询的一系列必要且有序的数据库操作(Operation)的集合。

生成执行计划

预估执行计划:每执行一次EXPLAIN PLAN,DB优化器(Optimizer)就会生成执行计划而不执行SQL(因此叫“预估”),并写入PLAN_TABLE表中(Oracle默认使用PLAN_TABLE临时表来保存EXPLAIN PLAN的结果):

The PLAN_TABLE is automatically created as a global temporary table to hold the output of an EXPLAIN PLAN statement for all users. PLAN_TABLE is the default sample output table into which the EXPLAIN PLAN statement inserts rows describing execution plans

EXPLAIN PLAN
   SET STATEMENT_ID = 'statement1'  --指定该条语句的statement_id,后续可用于查询PLAN_TABLE表中的执行计划
   INTO my_plan_table               --可以不使用默认的PLAN_TABLE而使用自定义表
FOR
SELECT last_name FROM employees;    --要分析的SQL语句。INSERT, UPDATE, SELECT和DELETE语句。

实际执行计划:在SQL执行的时候由DB优化器(Optimizer)创建的执行计划。

EXPLAIN PLAN的执行结果代表在EXPLAIN这条SQL的时候,DB应该如何去执行这条SQL。而实际在执行的时候可能由于数据数量,结构(例如增加了索引等)等的变化,使得实际的执行计划与预估执行计划略有不同。

查询执行计划

预估执行计划

DBMS_XPLAN.DISPLAY是一个Oracle自带的函数(点击链接查看详情),它可以接受一些选项用于展示执行计划:

--不带选项
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

--这里使用了自定义的表my_plan_table和自定义的SQL ID statement1
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('my_plan_table','statement1','TYPICAL'));

执行结果大概长这样:


Plan hash value: 1906736282

---------------------------------------------------------------------------------------------
| Id  | Operation             | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                     |     1 |    40 |     6   (0)| 00:00:01 |
|   1 |  NESTED LOOPS         |                     |     1 |    40 |     6   (0)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN|                     |     4 |   128 |     6   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL  | PRODUCT_INFORMATION |     1 |    28 |     5   (0)| 00:00:01 |
|   4 |    BUFFER SORT        |                     |   105 |   420 |     1   (0)| 00:00:01 |
|   5 |     INDEX FULL SCAN   | ORDER_PK            |   105 |   420 |     1   (0)| 00:00:01 |
|*  6 |   INDEX UNIQUE SCAN   | ORDER_ITEMS_UK      |     1 |     8 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("MIN_PRICE"<40 AND "LIST_PRICE"<50)
   6 - access("O"."ORDER_ID"="ORDER_ID" AND "P"."PRODUCT_ID"="O"."PRODUCT_ID")

19 rows selected.

实际执行计划

由于实际执行计划要在执行SQL时才会生成,查看执行计划前,要先执行SQL。DBMS_XPLAN.DISPLAY_CURSOR也是一个Oracle自带的函数(点击链接查看详情),它可以展示已经加载到cache中的执行计划:

--不带选项,直接查询最后一条SQL的执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

--指定了sql_id和child_number,查询指定SQL的执行计划。
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('gwp663cqh5qbf',0));

-----------------------------------------------------------

--1.为了区别其他的SQL,需要在执行SQL的时候加入特殊标记的注释,比如:
SELECT /* TOTO */ ename, dname FROM dept d join emp e USING (deptno);

--2.通过SQL标记,查到sql_id和child_number。v$sql是一个系统视图,保存执行过的sql语句
SELECT sql_id, child_number FROM v$sql WHERE sql_text LIKE '%TOTO%';

SQL_ID         CHILD_NUMBER
----------     -----------------------------
gwp663cqh5qbf   0

--3.再查询该SQL的执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('gwp663cqh5qbf',0));

执行结果大概长这样: 

Plan hash value: 3693697075, SQL ID: gwp663cqh5qbf, child number: 0
--------------------------------------------------------
SELECT /* TOTO */ ename, dname 
FROM dept d JOIN emp e USING (deptno);

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |     7 (100)|          |
|   1 |  SORT GROUP BY      |      |     4 |    64 |     7  (43)| 00:00:01 |
|*  2 |   HASH JOIN         |      |    14 |   224 |     6  (34)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPT |     4 |    44 |     3  (34)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| EMP  |    14 |    70 |     3  (34)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("E"."DEPTNO"="D"."DEPTNO")

理解执行计划

Oracle的执行计划看起来是以tab符和行的方式展示的,实际上它是一棵执行树,因此在阅读执行计划的时候,要从树的叶子节点看起,即从执行计划表里Operation列缩进最多的一行看起

假如有以下SQL:

SELECT ename FROM emp e, dept d 
WHERE  e.deptno = d.deptno  
AND    e.empno=7369;

执行之后查询实际执行计划:

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR());

结果:

Plan hash value: 3693697075, SQL hash value: 2096952573, child number: 0
------------------------------------------------------------------
SELECT ename FROM emp e, dept d WHERE e.deptno = d.deptno
AND e.empno=7369

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |            |          |
|*  1 |  HASH JOIN         |      |     1 |    16 |     6  (34)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| EMP  |     1 |    13 |     3  (34)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPT |     4 |    12 |     3  (34)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("E"."DEPTNO"="D"."DEPTNO")
   2 - filter("E"."EMPNO"=7369)

21 rows selected.

可以看到,执行计划包含三部分:执行计划的hash值,以表格展示的执行计划树,以及Predicate信息。

对于这个执行计划,ID=2和ID=3这两行的缩进最多,应该从这两行开始看(注意:表格的ID列中有*的行,代表该行在Predicate Information部分有更详细信息):

  • ID=3,这是一个全表扫描DEPT的操作,有4行记录共12字节。
  • ID=2,全表扫描EMP表,有1行记录共13字节。在Predicate Information中看到,这个步骤还要过滤掉empno=7369的信息,而这正是sql中的where条件。
  • ID=1,两个表进行hash join,得到1行记录共16字节。在Predicate Information中看到,这个步骤join的方式是e.deptno=d.deptno,这也是sql种的where条件。
  • ID=0,执行SELECT操作,获取结果。

 

参考地址:

https://docs.oracle.com/database/121/TGSQL/tgsql_genplan.htm#TGSQL271

https://docs.oracle.com/database/121/ARPLS/d_xplan.htm#ARPLS378

https://vladmihalcea.com/sql-execution-plan-oracle

https://www.brentozar.com/archive/2014/07/getting-oracle-execution-plan/

展开阅读全文
打赏
0
3 收藏
分享
加载中
更多评论
打赏
0 评论
3 收藏
0
分享
返回顶部
顶部