- 論壇徽章:
- 7
|
本帖最后由 www_xylove 于 2013-08-05 09:22 編輯
執(zhí)行計劃的執(zhí)行順序精解
閱讀執(zhí)行計劃的規(guī)則：
1. 縮進最深的，最先執(zhí)行
2. 縮進深度相同的，先上后下
舉例說明：
需要注意的是，這里使用解釋執(zhí)行計劃來舉例說明
解釋執(zhí)行計劃
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;
已解釋。
獲得解釋執(zhí)行計劃
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行。
為了看清縮進關(guān)系，我特地把這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
格式化顯示執(zhí)行計劃的父子關(guān)系，通過父子關(guān)系，分析執(zhí)行計劃的執(zhí)行順序
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
分析執(zhí)行計劃順序
ID 顯示的為子ID,parent_id 顯示的為父ID. 查看縮進關(guān)系，最深縮進的ID為5和7，那么它們先執(zhí)行，因為縮進的深度一致，根據(jù)先上后下的原則，ID=5的最先執(zhí)行，ID=4第二個執(zhí)行。依據(jù)父子關(guān)系圖，ID=5的父親為parent_id=4，ID=7的父親為parent_id=6，
因為縮進的深度一致，根據(jù)先上后下的原則，ID=7的第三個執(zhí)行，ID=6的第四個執(zhí)行。依據(jù)父子關(guān)系圖，ID=4的parent_id父親為3，ID=6的父親parent_id也為3，ID=8因為縮進深度與ID=3的縮進深度一致，根據(jù)先上后下的原則，ID=3的第五個執(zhí)行，ID=8的第六個執(zhí)行。依據(jù)父子關(guān)系圖，ID=3的父親為parent_id=2，ID=9的因為縮進的深度與ID=2一致，根據(jù)先上后下的原則，ID=2的第七個執(zhí)行，ID=9的第八個執(zhí)行。ID=2與ID=9的父親都是1，第九個執(zhí)行的是ID=1，最后執(zhí)行的是ID=0。
執(zhí)行的順序ID為：5>4>7>6>3>8>2>9>1>0
具體分析一下每一步都做了些什么：
摘錄執(zhí)行計劃部分內(nèi)容，用來分析
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進程訪問表的索引結(jié)構(gòu)，依據(jù)索引DEPT_ID_PK（訪問where條件后面的b.department_id = 30）對索引結(jié)構(gòu)進行索引唯一掃描，執(zhí)行一次，得到索引列值與該列所在行的rowid。
（4）首先依據(jù)rowid，執(zhí)行一次，回表DEPARTMENTS得到中間結(jié)果集
（DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
30 Purchasing 114 1700 ）
因為表DEPARTMENTS where條件后面的索引列DEPARTMENT_ID是一個限制列，而非連接列，故而所得部門ID=30的中間結(jié)果集，就一條記錄，從執(zhí)行計劃的rows=1也可驗證。
（7）oracle進程繼續(xù)訪問另外一張表的索引結(jié)構(gòu)，依據(jù)索引LOC_ID_PK（訪問where條件后面的d.location_id）對索引結(jié)構(gòu)進行唯一性掃描，執(zhí)行一次，得到該索引列值與該列所在行的所有的rowid,oracle進程接下來進行回表操作。
（6）再依據(jù)索引列的所有的rowid，回表LOCATIONS 得到中間結(jié)果集
（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所得的中間結(jié)果集，就是全表的23條記錄數(shù)，從執(zhí)行計劃的rows=23也可驗證，值得注意的是，索引唯一性掃描，執(zhí)行一次，得到了該表的所有的rowid.
（3）,oracle進程得到兩張表的中間結(jié)果集，DEPARTMENTS 表的一條記錄，LOCATIONS表的23條記錄，oracle優(yōu)化器將計算成本損耗（如何計算成本損耗，有待進一步研究），決定使用哪種連接方式，本例使用nested loops嵌套循環(huán)的方式訪問，從執(zhí)行計劃看，驅(qū)動表為DEPARTMENTS，即外層表，被驅(qū)動表為LOCATIONS，即內(nèi)層表，優(yōu)化器這樣選擇是正確的，因為DEPARTMENTS的結(jié)果集少，一條記錄，而LOCATIONS的結(jié)果集多，23條記錄，優(yōu)化器使用這一條記錄去匹配23條記錄中的某一條記錄，執(zhí)行一次就可以了，如果，相反的話，需要執(zhí)行23次，這樣效率就低很多。這里執(zhí)行b.location_id = d.location_id，注意，這一對連接列的數(shù)據(jù)源就是上述的兩個中間結(jié)果集，具體，拆分上述的sql，就是下面的語句：
select * from hr.departments b,hr.locations d
where b.location_id = d.location_id
and b.department_id = 30;
得到的結(jié)果集就一條記錄：
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產(chǎn)生的中間結(jié)果集，就一條記錄，注意，使用DEPARTMENTS. LOCATION_ID=LOCATIONS.LOCATION_ID去匹配，即LOCATION_ID=1700就一條記錄。
（8）oracle進程全表掃描EMPLOYEES，注意使用了a.department_id=30來過濾，導(dǎo)致得到的中間結(jié)果集為6條記錄.
得到的結(jié)果集如下：
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優(yōu)化器將進行第二次nested loops，來產(chǎn)生中間結(jié)果集，數(shù)據(jù)源為（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;
驅(qū)動表為的行源為第一次nested loops的結(jié)果集，其實即使部門ID=30的這一條記錄，被驅(qū)動表為employees，其實就是部門ID=30的6條記錄，執(zhí)行一次，匹配6條記錄，因為employees的結(jié)果集內(nèi)的6條記錄的部門ID都是30，故而匹配6條記錄。
（9）oracle進程接著對jobs表執(zhí)行全表掃描，得到結(jié)果集為19條記錄。
（1）oracle優(yōu)化器將計算成本損耗（如何計算成本損耗，有待進一步研究），進行hash jion，將第二次nested loops的產(chǎn)生的結(jié)果集，即6條記錄，為驅(qū)動行源，運用哈希函數(shù)對連接列（department_id）進行計算產(chǎn)生一張哈希表，放入PGA內(nèi)存區(qū)的hash area 區(qū)域，接著掃描第二張表，即jobs，稱為探測表，到內(nèi)存區(qū)匹配記錄數(shù)，顯然，一條都沒有匹配，該19條記錄數(shù)被全部丟棄。
（0）SELECT STATEMENT 的最終結(jié)果集為6條記錄數(shù)：
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.
|
評分
-
查看全部評分
|
免費注冊
發(fā)表于 2013-08-04 12:24
發(fā)表于 2013-11-13 19:19