有同事问我如何看执行计划的执行顺序。这个问题我想很多朋友都会不屑一顾的，这么简单的问题，谁不会看？
但是这里我还是想说一说我是怎样看执行计划的执行顺序的。
我看执行计划的执行顺序的原则就是先从最开头一直往右看，直到看到最右边的并列的地方，对于不并列的，靠右的先执行；如果见到并列的，就从上往下看，对于并列的，靠上的先执行。
好了，我们以上述原则来看一个实例：
SQL*Plus: Release
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn ipra/acca@ipradev;
已连接。
SQL> select count(*) from (select o.oatfna,o.oatprf,o.oatfrm,o.oattkt,o.oatcpn,o.oatpst,substr(u.ute
dsp,1,5) as errnum,o.oatcdt,o.oatcid from owbatn o,uplter u where (o.oatcer='Y' and o.oatcpn='0' and
u.utetkt=o.oattkt and u.rowid= (select max(rowid) from uplter where utetkt = o.oattkt and utefna=o
.oatfna)) or o.oatpst='D' or o.oatpst='I' or o.oatpst='F');
COUNT(*)
----------
17809
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 01nxqdvn71mx5, child number 0
-------------------------------------
select count(*) from (select o.oatfna,o.oatprf,o.oatfrm,o.oattkt,o.oatcpn,o.oatpst,substr(u.u
tedsp,1,5) as errnum,o.oatcdt,o.oatcid from owbatn o,uplter u where (o.oatcer='Y' and
o.oatcpn='0' and u.utetkt=o.oattkt and u.rowid= (select max(rowid) from uplter where utetkt
= o.oattkt and utefna=o.oatfna)) or o.oatpst='D' or o.oatpst='I' or o.oatpst='F')
Plan hash value: 2105702960
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3082 (100)| |
| 1 | SORT AGGREGATE | | 1 | 54 | | |
| 2 | CONCATENATION | | | | | |
| 3 | MERGE JOIN CARTESIAN | | 470K|
|* 4 | TABLE ACCESS FULL | OWBATN | 1 | 34 | 851 (5)| 00:00:11 |
| 5 | BUFFER SORT | | 316K| 6175K| 1118 (3)| 00:00:14 |
| 6 | TABLE ACCESS FULL | UPLTER | 316K| 6175K| 1118 (3)| 00:00:14 |
|* 7 | FILTER | | | | | |
|* 8 | HASH JOIN | | 37522 | 1978K| 1109 (6)| 00:00:14 |
|* 9 | TABLE ACCESS FULL | OWBATN | 20956 | 695K| 852 (5)| 00:00:11 |
| 10 | INDEX FAST FULL SCAN | IDX_UPLTER_UTETKT | 316K| 6175K| 246 (6)| 00:00:03 |
| 11 | SORT AGGREGATE | | 1 | 38 | | |
|* 12 | TABLE ACCESS BY INDEX ROWID| UPLTER | 1 | 38 | 4 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | IDX_UPLTER_UTETKT | 2 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(("O"."OATPST"='D' OR "O"."OATPST"='F' OR "O"."OATPST"='I'))
7 - filter("U".ROWID=)
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
8 - access("U"."UTETKT"="O"."OATTKT")
9 - filter(("O"."OATCPN"=0 AND "O"."OATCER"='Y' AND LNNVL("O"."OATPST"='I') AND
LNNVL("O"."OATPST"='F') AND LNNVL("O"."OATPST"='D')))
12 - filter("UTEFNA"=:B1)
13 - access("UTETKT"=:B1)
已选择39行。
好了，我们现在根据上述原则来解析一下上述执行计划的执行顺序。
先从最开头一直往右看，直到看到最右边的并列的地方，对于不并列的，靠右的先执行：
从上述执行计划的开头一直往右看，直到找到最右边并列的。也就是从SELECT STATEMENT开始往右找，一直找到TABLE ACCESS FULL，这时候发现BUFFER SORT和它并列。
此时我们已经可以知道TABLE ACCESS FULL一定是比BUFFER SORT（包括其右边的所有语句）先执行，因为对于并列的，靠上的先执行。
而对于BUFFER SORT，它右边又有"TABLE ACCESS FULL | UPLTER"，此时根据上述原则我们也可以知道，它比BUFFER SORT先执行。
这样对于上述执行计划里的这三条语句：
|* 4 | TABLE ACCESS FULL | OWBATN | 1 | 34 | 851 (5)| 00:00:11 |
| 5 | BUFFER SORT | | 316K| 6175K| 1118 (3)| 00:00:14 |
| 6 | TABLE ACCESS FULL | UPLTER | 316K| 6175K| 1118 (3)| 00:00:14 |
正确的执行顺序是：
先执行TABLE ACCESS FULL | OWBATN | 1 | 34 | 851 (5)| 00:00:11 |
再执行TABLE ACCESS FULL | UPLTER | 316K| 6175K| 1118 (3)| 00:00:14 |
最后执行BUFFER SORT | | 316K| 6175K| 1118 (3)| 00:00:14 |
这样我们就解析出来了最内层上述三条语句的执行顺序，然后我们再应用上述原则解析其外层语句，一层一层剥离，就很容易得到了整个执行计划的顺序，这里我把按照上述原则解析出来的执行计划的顺序paste出来，如下所示：
第一步：TABLE ACCESS FULL | OWBATN | 1 | 34 | 851 (5)| 00:00:11 |
第二步：TABLE ACCESS FULL | UPLTER | 316K| 6175K| 1118 (3)| 00:00:14 |
第三步：BUFFER SORT | | 316K| 6175K| 1118 (3)| 00:00:14 |
第四步：MERGE JOIN CARTESIAN | | 470K|
第五步：TABLE ACCESS FULL | OWBATN | 20956 | 695K| 852 (5)| 00:00:11 |
第六步：INDEX FAST FULL SCAN | IDX_UPLTER_UTETKT | 316K| 6175K| 246 (6)| 00:00:03 |
第七步：HASH JOIN | | 37522 | 1978K| 1109 (6)| 00:00:14 |
第八步：INDEX RANGE SCAN | IDX_UPLTER_UTETKT | 2 | | 3 (0)| 00:00:01 |
第九步：TABLE ACCESS BY INDEX ROWID| UPLTER | 1 | 38 | 4 (0)| 00:00:01 |
第十步：SORT AGGREGATE | | 1 | 38 | | |
第十一步：FILTER | | | | | |
第十二步：CONCATENATION | | | | | |
第十三步：SORT AGGREGATE | | 1 | 54 | | |
第十四步：SELECT STATEMENT | | | | 3082 (100)| |
如果把握了上述原则，则无论多复杂的执行计划也能知道其正确的执行顺序。
这里给还不知道怎样看执行计划顺序的朋友留一个作业，请应用上述原则，正确解析出下述执行计划的执行顺序：
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 220 | 17191 | | |
| 1 | NESTED LOOPS | | 1 | 220 | 17191 | | |
| 2 | NESTED LOOPS | | 1 | 199 | 17189 | | |
| 3 | NESTED LOOPS | | 1 | 169 | 17187 | | |
| 4 | NESTED LOOPS | | 1 | 155 | 17186 | | |
|* 5 | HASH JOIN SEMI | | 1 | 116 | 17184 | | |
| 6 | NESTED LOOPS | | 411 | 42333 | 17017 | | |
| 7 | MERGE JOIN | | 411 | 27126 | 16195 | | |
| 8 | PARTITION RANGE ALL | | | | | 1 | 14 |
|* 9 | TABLE ACCESS FULL | SERV_ACCT | 8667K|
|* 10 | SORT JOIN | | 1 | 39 | 18 | | |
| 11 | TABLE ACCESS BY INDEX ROWID | SYS_DOMAIN | 1 | 39 | 2 | | |
|* 12 | INDEX RANGE SCAN | IDX_SYS_DOMAIN_1 | 1 | | 1 | | |
| 13 | PARTITION RANGE ITERATOR | | | | | KEY | KEY |
|* 14 | TABLE ACCESS BY LOCAL INDEX ROWID| SERV | 1 | 37 | 2 | KEY | KEY |
|* 15 | INDEX UNIQUE SCAN | PK_SERV | 1 | | 1 | KEY | KEY |
| 16 | VIEW | VW_NSO_1 | 4977 | 64701 | 166 | | |
| 17 | TABLE ACCESS BY LOCAL INDEX ROWID | SERV_ATTR | 1991 | 37829 | 53 | 1 | 1 |
| 18 | NESTED LOOPS | | 4977 | 136K| 166 | | |
|* 19 | TABLE ACCESS FULL | A_QUERY_ACCT_ATTR | 3 | 27 | 7 | | |
| 20 | PARTITION RANGE ALL | | | | | 1 | 14 |
|* 21 | INDEX RANGE SCAN | IDX_SERV_ATTR_INTERNET_NEW | 1991 | | 42 | 1 | 14 |
| 22 | TABLE ACCESS BY INDEX ROWID | SYS_DOMAIN | 1 | 39 | 2 | | |
|* 23 | INDEX RANGE SCAN | IDX_SYS_DOMAIN_1 | 1 | | 1 | | |
| 24 | TABLE ACCESS BY INDEX ROWID | PRODUCT | 1 | 14 | 1 | | |
|* 25 | INDEX UNIQUE SCAN | PK_PRODUCT | 1 | | | | |
|* 26 | TABLE ACCESS BY GLOBAL INDEX ROWID | ACCT | 1 | 30 | 2 | ROWID | ROW L |
|* 27 | INDEX UNIQUE SCAN | PK_ACCT | 1 | | 1 | | |
| 28 | TABLE ACCESS BY GLOBAL INDEX ROWID | CUST | 1 | 21 | 2 | ROWID | ROW L |
|* 29 | INDEX UNIQUE SCAN | PK_CUST | 1 | | 1 | | |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("A"."SERV_ID"="VW_NSO_1″."SERV_ID")
9 - filter("B"."STATE"='
10 - access("B"."STATE"="SYS_DOMAIN"."DOMAIN")
filter("B"."STATE"="SYS_DOMAIN"."DOMAIN")
12 - access("SYS_DOMAIN"."TABLE_NAME"='SERV_ACCT' AND "SYS_DOMAIN"."FIELD_NAME"='STATE' AND
"SYS_DOMAIN"."DOMAIN"='
14 - filter("A"."STATE"='
"A"."STATE"='2HN' OR "A"."STATE"='2HS')
15 - access("A"."SERV_ID"="B"."SERV_ID")
19 - filter("A_QUERY_ACCT_ATTR"."STATE"='A
21 - access("SERV_ATTR"."ATTR_VAL"='0xx833xxxxx' AND "SERV_ATTR"."ATTR_ID"="A_QUERY_ACCT_ATTR"."ATTR_ID")
23 - access("SYS_ALIAS_0000″."TABLE_NAME"='SERV' AND "SYS_ALIAS_0000″."FIELD_NAME"='STATE' AND
"A"."STATE"="SYS_ALIAS_0000″."DOMAIN")
filter("SYS_ALIAS_0000″."DOMAIN"='
"SYS_ALIAS_0000″."DOMAIN"='2HE' OR "SYS_ALIAS_0000″."DOMAIN"='2HH' OR "SYS_ALIAS_0000″."DOMAIN"='2HN' OR
"SYS_ALIAS_0000″."DOMAIN"='2HS')
25 - access("A"."PRODUCT_ID"="H"."PRODUCT_ID")
26 - filter("C"."STATE"='
27 - access("B"."ACCT_ID"="C"."ACCT_ID")
29 - access("A"."CUST_ID"="D"."CUST_ID")
Good.
Troubleshooting Oracle Performance这本书里面对执行计划的查看方法解释得也很好。
请问一下，对于不并列的，靠右的先执行
TABLE ACCESS FULL UPLTER
在
TABLE ACCESS FULL OWBATN和
BUFFER SORT 的最右边
是不是应该先执行
TABLE ACCESS FULL UPLTER
再执行
TABLE ACCESS FULL OWBATN
然后是
BUFFER SORT
不是呀，你仔细看我写的原则——“先从最开头一直往右看，直到看到最右边的并列的地方”。注意，这里往右看的过程一定是连续的。
TABLE ACCESS FULL UPLTER虽然在TABLE ACCESS FULL OWBATN和BUFFER SORT 的右边，但是它是从属于BUFFER SORT的，而BUFFER SORT和TABLE ACCESS FULL OWBATN在位置上是并列的。所以TABLE ACCESS FULL OWBATN比BUFFER SORT和TABLE ACCESS FULL UPLTER都要先执行。
看来我一直都误解了，谢谢指正。O(∩_∩)O
介绍的很详细，不过格式上有点乱……
怎么没有人公布答案呢？