论坛徽章:: 7

电梯直达

1楼 [收藏(0)] [报告]

发表于 2013-08-04 12:24 |只看该作者 |倒序浏览

本帖最后由 www_xylove 于 2013-08-05 09:22 编辑

执行计划的执行顺序精解

阅读执行计划的规则：
1. 缩进最深的，最先执行
2. 缩进深度相同的，先上后下
举例说明：
需要注意的是，这里使用解释执行计划来举例说明
解释执行计划
explain plan for
select * from hr.employees a,hr.departments b,hr.jobs c,hr.locations d
where a.department_id = b.department_id
and a.job_id = c.job_id
and b.location_id = d.location_id
and b.department_id = 30;

已解释。

获得解释执行计划
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
Plan hash value: 706249465
---------------------------------------------------------------------| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT             |          |    6 |  1032 |    9  (12)| 00:00:01 |
|*  1 |  HASH JOIN                   |          |    6 |  1032 |    9  (12)| 00:00:01 |
| 2 | NESTED LOOPS                |          |    6 | 834 |    5 (0)| 00:00:01 |
| 3 | NESTED LOOPS             |          |    1 | 70 |    2 (0)| 00:00:01 |
| 4 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |    1 | 21 |    1 (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN       | DEPT_ID_PK  |    1 |    |    0 (0)| 00:00:01 |
| 6 |    TABLE ACCESS BY INDEX ROWID| LOCATIONS | 23 |  1127 |    1 (0)| 00:00:01 |
|*  7 |    INDEX UNIQUE SCAN       | LOC_ID_PK |    1 |    |    0 (0)| 00:00:01 |
|*  8 | TABLE ACCESS FULL          | EMPLOYEES |    6 | 414 |    3 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL          | JOBS       | 19 | 627 |    3 (0)| 00:00:01 |
---------------------------------------------------------------------

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

1 - access("A"."JOB_ID"="C"."JOB_ID")
5 - access("B"."DEPARTMENT_ID"=30)
7 - access("B"."LOCATION_ID"="D"."LOCATION_ID")
8 - filter("A"."DEPARTMENT_ID"=30)

已选择24行。
为了看清缩进关系，我特地把这ID,Operation,Name列单独拿出来显示。

Id  | Operation                   | Name
----------------------------------------------------
0 | SELECT STATEMENT             |
*  1 |  HASH JOIN                   |
2 | NESTED LOOPS                |
3 | NESTED LOOPS             |
4 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS
*  5 |    INDEX UNIQUE SCAN       | DEPT_ID_PK
6 |    TABLE ACCESS BY INDEX ROWID| LOCATIONS
*  7 |    INDEX UNIQUE SCAN       | LOC_ID_PK
*  8 | TABLE ACCESS FULL          | EMPLOYEES
9 | TABLE ACCESS FULL          | JOBS

格式化显示执行计划的父子关系，通过父子关系，分析执行计划的执行顺序
select id, parent_id, operation
  from (select level lvl,
            id,
            parent_id,
            lpad(' ', level) || operation || ' ' || options || ' ' ||
            object_name as operation
      from plan_table
      start with id = 0
      connect by prior id = parent_id)
order by lvl desc, id;
ID PARENT_ID OPERATION
5 4       INDEX UNIQUE SCAN DEPT_ID_PK
7 6       INDEX UNIQUE SCAN LOC_ID_PK
4 3       TABLE ACCESS BY INDEX ROWID DEPARTMENTS
6 3       TABLE ACCESS BY INDEX ROWID LOCATIONS
3 2       NESTED LOOPS
8 2       TABLE ACCESS FULL EMPLOYEES
2 1    NESTED LOOPS
9 1    TABLE ACCESS FULL JOBS
1 0    HASH JOIN
0    SELECT STATEMENT

分析执行计划顺序
ID 显示的为子ID,parent_id 显示的为父ID. 查看缩进关系，最深缩进的ID为5和7，那么它们先执行，因为缩进的深度一致，根据先上后下的原则，ID=5的最先执行，ID=4第二个执行。依据父子关系图，ID=5的父亲为parent_id=4，ID=7的父亲为parent_id=6，
因为缩进的深度一致，根据先上后下的原则，ID=7的第三个执行，ID=6的第四个执行。依据父子关系图，ID=4的parent_id父亲为3，ID=6的父亲parent_id也为3，ID=8因为缩进深度与ID=3的缩进深度一致，根据先上后下的原则，ID=3的第五个执行，ID=8的第六个执行。依据父子关系图，ID=3的父亲为parent_id=2，ID=9的因为缩进的深度与ID=2一致，根据先上后下的原则，ID=2的第七个执行，ID=9的第八个执行。ID=2与ID=9的父亲都是1，第九个执行的是ID=1，最后执行的是ID=0。

执行的顺序ID为：5>4>7>6>3>8>2>9>1>0

具体分析一下每一步都做了些什么：
摘录执行计划部分内容，用来分析
Id  | Operation                   | Name       Starts    E-Rows A-Rows
0 | SELECT STATEMENT             |          |    1 |          6
1 |  HASH JOIN                   |          |    1 |    6
2 | NESTED LOOPS                |          |    1 |    6
3 | NESTED LOOPS             |          |    1 |    1
4 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |1 |    1
5 |    INDEX UNIQUE SCAN       | DEPT_ID_PK  | 1 |    1
6 |    TABLE ACCESS BY INDEX ROWID| LOCATIONS | 1 |    23
7 |    INDEX UNIQUE SCAN       | LOC_ID_PK | 1 |    1
8 | TABLE ACCESS FULL       | EMPLOYEES | 1 |    6
9 | TABLE ACCESS FULL          | JOBS       |    1 |    19

（5）oracle进程访问表的索引结构，依据索引DEPT_ID_PK（访问where条件后面的b.department_id = 30）对索引结构进行索引唯一扫描，执行一次，得到索引列值与该列所在行的rowid。
（4）首先依据rowid，执行一次，回表DEPARTMENTS得到中间结果集
（DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
   30                Purchasing             114       1700    ）
因为表DEPARTMENTS where条件后面的索引列DEPARTMENT_ID是一个限制列，而非连接列，故而所得部门ID=30的中间结果集，就一条记录，从执行计划的rows=1也可验证。
（7）oracle进程继续访问另外一张表的索引结构，依据索引LOC_ID_PK（访问where条件后面的d.location_id）对索引结构进行唯一性扫描，执行一次，得到该索引列值与该列所在行的所有的rowid,oracle进程接下来进行回表操作。
（6）再依据索引列的所有的rowid，回表LOCATIONS 得到中间结果集
   （LOCATION_ID STREET_ADDRESS POSTAL_CODE CITY STATE_PROVINCE COUNTRY_ID
1000 1297 Via Cola di Rie 00989 Roma IT
1100 93091 Calle della Testa 10934 Venice IT
1200 2017 Shinjuku-ku 1689 Tokyo Tokyo Prefecture JP
1300 9450 Kamiya-cho 6823 Hiroshima JP
1400 2014 Jabberwocky Rd 26192 Southlake Texas US
1500 2011 Interiors Blvd 99236 South San Francisco California US
1600 2007 Zagora St 50090 South Brunswick New Jersey US
1700 2004 Charade Rd 98199 Seattle Washington US
1800 147 Spadina Ave M5V 2L7 Toronto Ontario CA
1900 6092 Boxwood St YSW 9T2 Whitehorse Yukon CA
2000 40-5-12 Laogianggen 190518 Beijing CN
2100 1298 Vileparle (E) 490231 Bombay Maharashtra IN
2200 12-98 Victoria Street 2901 Sydney New South Wales AU
2300 198 Clementi North 540198 Singapore SG
2400 8204 Arthur St London UK
2500 Magdalen Centre, The Oxford Science Park OX9 9ZB Oxford Oxford UK
2600 9702 Chester Road 09629850293 Stretford Manchester UK
2700 Schwanthalerstr. 7031 80925 Munich Bavaria DE
2800 Rua Frei Caneca 1360 01307-002 Sao Paulo Sao Paulo BR
2900 20 Rue des Corps-Saints 1730 Geneva Geneve CH
3000 Murtenstrasse 921 3095 Bern BE CH
3100 Pieter Breughelstraat 837 3029SK Utrecht Utrecht NL
3200 Mariano Escobedo 9991 11932 Mexico City Distrito Federal, MX）
   因为表LOCATIONS where 后面的的索引列LOCATION_ID是一个连接列，而非限制列，因此访问表的所有的rowid所得的中间结果集，就是全表的23条记录数，从执行计划的rows=23也可验证，值得注意的是，索引唯一性扫描，执行一次，得到了该表的所有的rowid.
（3）,oracle进程得到两张表的中间结果集，DEPARTMENTS 表的一条记录，LOCATIONS表的23条记录，oracle优化器将计算成本损耗（如何计算成本损耗，有待进一步研究），决定使用哪种连接方式，本例使用nested loops嵌套循环的方式访问，从执行计划看，驱动表为DEPARTMENTS，即外层表，被驱动表为LOCATIONS，即内层表，优化器这样选择是正确的，因为DEPARTMENTS的结果集少，一条记录，而LOCATIONS的结果集多，23条记录，优化器使用这一条记录去匹配23条记录中的某一条记录，执行一次就可以了，如果，相反的话，需要执行23次，这样效率就低很多。这里执行b.location_id = d.location_id，注意，这一对连接列的数据源就是上述的两个中间结果集，具体，拆分上述的sql，就是下面的语句：
select * from hr.departments b,hr.locations d
where  b.location_id = d.location_id
and b.department_id = 30;
得到的结果集就一条记录：
   DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID LOCATION_ID STREET_ADDRESS POSTAL_CODE CITY STATE_PROVINCE COUNTRY_ID
30 Purchasing 114 1700 1700 2004 Charade Rd 98199 Seattle Washington US
这就是第一次nested loops产生的中间结果集，就一条记录，注意，使用DEPARTMENTS. LOCATION_ID=LOCATIONS.LOCATION_ID去匹配，即LOCATION_ID=1700就一条记录。
（8）oracle进程全表扫描EMPLOYEES，注意使用了a.department_id=30来过滤，导致得到的中间结果集为6条记录.
得到的结果集如下：
   EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
114 Den Raphaely DRAPHEAL 515.127.4561 1994/12/7 PU_MAN 11000.00 100 30
115 Alexander Khoo AKHOO 515.127.4562 1995/5/18 PU_CLERK 3100.00 114 30
116 Shelli Baida SBAIDA 515.127.4563 1997/12/24 PU_CLERK 2900.00 114 30
117 Sigal Tobias STOBIAS 515.127.4564 1997/7/24 PU_CLERK 2800.00 114 30
118 Guy Himuro GHIMURO 515.127.4565 1998/11/15 PU_CLERK 2600.00 114 30
119 Karen Colmenares KCOLMENA 515.127.4566 1999/8/10 PU_CLERK 2500.00 114 30

(2) oracle优化器将进行第二次nested loops，来产生中间结果集，数据源为（3）的一条记录和（8）的6条记录，拆分上述sql，使用如下sql进行nested loops:
select *
  from hr.employees a, hr.departments b
where a.department_id = b.department_id
and b.department_id = 30;
驱动表为的行源为第一次nested loops的结果集，其实即使部门ID=30的这一条记录，被驱动表为employees，其实就是部门ID=30的6条记录，执行一次，匹配6条记录，因为employees的结果集内的6条记录的部门ID都是30，故而匹配6条记录。
（9）oracle进程接着对jobs表执行全表扫描，得到结果集为19条记录。
（1）oracle优化器将计算成本损耗（如何计算成本损耗，有待进一步研究），进行hash jion，将第二次nested loops的产生的结果集，即6条记录，为驱动行源，运用哈希函数对连接列（department_id）进行计算产生一张哈希表，放入PGA内存区的hash area 区域，接着扫描第二张表，即jobs，称为探测表，到内存区匹配记录数，显然，一条都没有匹配，该19条记录数被全部丢弃。
（0）SELECT STATEMENT 的最终结果集为6条记录数：
114 Den Raphaely DRAPHEAL 515.127.4561 1994/12/7 PU_MAN 11000.00 100 30 30 Purchasing 114 1700 PU_MAN Purchasing Manager 8000 15000 1700 2004 Charade Rd 98199 Seattle Washington US
119 Karen Colmenares KCOLMENA 515.127.4566 1999/8/10 PU_CLERK 2500.00 114 30 30 Purchasing 114 1700 PU_CLERK Purchasing Clerk 2500 5500 1700 2004 Charade Rd 98199 Seattle Washington US
118 Guy Himuro GHIMURO 515.127.4565 1998/11/15 PU_CLERK 2600.00 114 30 30 Purchasing 114 1700 PU_CLERK Purchasing Clerk 2500 5500 1700 2004 Charade Rd 98199 Seattle Washington US
117 Sigal Tobias STOBIAS 515.127.4564 1997/7/24 PU_CLERK 2800.00 114 30 30 Purchasing 114 1700 PU_CLERK Purchasing Clerk 2500 5500 1700 2004 Charade Rd 98199 Seattle Washington US
116 Shelli Baida SBAIDA 515.127.4563 1997/12/24 PU_CLERK 2900.00 114 30 30 Purchasing 114 1700 PU_CLERK Purchasing Clerk 2500 5500 1700 2004 Charade Rd 98199 Seattle Washington US
115 Alexander Khoo AKHOO 515.127.4562 1995/5/18 PU_CLERK 3100.00 114 30 30 Purchasing 114 1700 PU_CLERK Purchasing Clerk 2500 5500 1700 2004 Charade Rd 98199 Seattle Washington US

end.