Oracle执行计划阅读不符合"最右最上最先执行"的一例
我们都知道，阅读执行计划的时候，执行顺序是
1.缩进最深的，最先执行
2.缩进相同的，先上后下
有个简要口诀是"最右最上最先执行"。
官方文档(点这)也提到了执行顺序规则：
The execution order in EXPLAIN PLAN output begins with the line that is the furthest indented to the right. The next step is the parent of that line. If two lines are indented equally, then the top line is normally executed first.
现在有SQL语句以及对应的执行计划如下：
数据库版本：11.2.0.4.0
explain plan for
select to_char(t.create_time, 'yyyy-mm') 月份
,count(*) 总数
,(select count(*)
from zkm.test v
where 1 = 1
and v.create_time >= trunc(t.create_time, 'mm')
and v.create_time < add_months(trunc(t.create_time, 'mm'), 1)
and v.result_flag = '11'
) 通过数
from zkm.test t
where 1 = 1
and t.create_time >= to_date('20240101', 'yyyymmdd')
group by to_char(t.create_time, 'yyyy-mm'),trunc(t.create_time, 'mm')
order by to_char(t.create_time, 'yyyy-mm') desc;
执行计划：
Plan hash value: 1567763871
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 132K| 1032K| | 780 (2)| 00:00:10 |
| 1 | SORT AGGREGATE | | 1 | 11 | | | |
|* 2 | FILTER | | | | | | |
|* 3 | TABLE ACCESS BY INDEX ROWID| TEST | 995 | 10945 | | 294 (0)| 00:00:04 |
|* 4 | INDEX RANGE SCAN | IDX_Z_CREATE_TIME | 1794 | | | 7 (0)| 00:00:01 |
| 5 | SORT GROUP BY | | 132K| 1032K| 2104K| 780 (2)| 00:00:10 |
|* 6 | INDEX FAST FULL SCAN | IDX_Z_CREATE_TIME | 133K| 1040K| | 293 (2)| 00:00:04 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ADD_MONTHS(:B1,1)>:B2)
3 - filter("V"."RESULT_FLAG"='11')
4 - access("V"."CREATE_TIME">=:B1 AND "V"."CREATE_TIME"<ADD_MONTHS(:B2,1))
6 - filter("T"."CREATE_TIME">=TO_DATE(' 2024-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
仔细看看上边的执行计划，按照开头所说的规则，那么执行顺序应该是：
4 -> 3 -> 2 -> 1 -> 6 -> 5 -> 0
但这里结合SQL本身以及执行计划的谓词信息看，这个顺序就显得很奇怪了。
按照常规方式得到的执行顺序，是先取的v表的数据，Id = 4 的谓词信息确看的出来明显需要用到t表的t.create_time相关数据信息。
因此很明显，这里用口诀"最右最上最先执行"这种常规方式得到得执行顺序是错误的，更应该是：
6 -> 5 -> 4 -> 3 -> 2 -> 1 -> 0
嗯。。很巧刚好从6倒数至0。
官方介绍的时候也说了，normally(正常情况下)，那么这里就是特殊情况了。
有个大牛 Adrian Billington 写了个脚本，可以在执行计划中显示执行顺序的，找来验证看看脚本实际执行情况。
Github下载：https://github.com/oracle-developer/xplan
脚本分安装包和独立脚本，安装包会在数据库里边创建一些对象以实现功能。
这里用到 xplan.display.sql 独立脚本，此脚本不需要在数据库中执行创建对象，直接调用即可。
效果如下：
16:34:43 SYS@dbname(475)> explain plan for
16:34:43 2 select to_char(t.create_time, 'yyyy-mm') 月份
16:34:43 3 ,count(*) 总数
16:34:43 4 ,(select count(*)
16:34:43 5 from zkm.test v
16:34:43 6 where 1 = 1
16:34:43 7 and v.create_time >= trunc(t.create_time, 'mm')
16:34:43 8 and v.create_time < add_months(trunc(t.create_time, 'mm'), 1)
16:34:43 9 and v.result_flag = '11'
16:34:44 10 ) 通过数
16:34:44 11 from zkm.test t
16:34:44 12 where 1 = 1
16:34:44 13 and t.create_time >= to_date('20240101', 'yyyymmdd')
16:34:44 14 group by to_char(t.create_time, 'yyyy-mm'),trunc(t.create_time, 'mm')
16:34:44 15 order by to_char(t.create_time, 'yyyy-mm') desc;
Explained.
Elapsed: 00:00:00.03
16:34:44 SYS@dbname(475)> @xplan.display.sql
old 3: from &v_xp_plan_table
new 3: from PLAN_TABLE
old 4: where plan_id = &v_xp_plan_id
new 4: where plan_id = 5614
old 33: from table(dbms_xplan.display('&v_xp_plan_table','&v_xp_stmt_id','&v_xp_format')) x
new 33: from table(dbms_xplan.display('PLAN_TABLE','','typical')) x
old 94: ' - XPlan v&v_xp_version by Adrian Billington (http://www.oracle-developer.net)'
new 94: ' - XPlan v1.3 by Adrian Billington (http://www.oracle-developer.net)'
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1567763871
---------------------------------------------------------------------------------------------------------------------------
| Id | Pid | Ord | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------------
| 0 | | 7 | SELECT STATEMENT | | 132K| 1032K| | 780 (2)| 00:00:10 |
| 1 | 0 | 4 | SORT AGGREGATE | | 1 | 11 | | | |
|* 2 | 1 | 3 | FILTER | | | | | | |
|* 3 | 2 | 2 | TABLE ACCESS BY INDEX ROWID| ZKM.TEST | 995 | 10945 | | 294 (0)| 00:00:04 |
|* 4 | 3 | 1 | INDEX RANGE SCAN | ZKM.IDX_Z_CREATE_TIME | 1794 | | | 7 (0)| 00:00:01 |
| 5 | 0 | 6 | SORT GROUP BY | | 132K| 1032K| 2104K| 780 (2)| 00:00:10 |
|* 6 | 5 | 5 | INDEX FAST FULL SCAN | ZKM.IDX_Z_CREATE_TIME | 133K| 1040K| | 293 (2)| 00:00:04 |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ADD_MONTHS(:B1,1)>:B2)
3 - filter("V"."RESULT_FLAG"='11')
4 - access("V"."CREATE_TIME">=:B1 AND "V"."CREATE_TIME"<ADD_MONTHS(:B2,1))
6 - filter("T"."CREATE_TIME">=TO_DATE(' 2024-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
About
------
- XPlan v1.3 by Adrian Billington (http://www.oracle-developer.net)
21 rows selected.
Ord列显示了执行顺序，看来 Adrian Billington 截至目前的脚本在这种情况下也是错误的。
再简单看看另外个案例，都是标量子查询。
16:46:53 SYS@test(40)> desc a; 16:46:55 SYS@test(40)> desc b;
Name Null? Type Name Null? Type
----------------------------------------- -------- ---------------------------- ----------------------------------------- -------- ----------------------------
ID NUMBER(38) ID NUMBER(38)
NAME VARCHAR2(10) NAME VARCHAR2(10)
16:47:16 SYS@test(40)> select a.id,(select count(*) from b where a.id=b.id) from a;
...省略部分内容...
Plan hash value: 3049668959
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 |00:00:00.01 | 3 |
| 1 | SORT AGGREGATE | | 3 | 1 | 3 |00:00:00.01 | 6 |
|* 2 | TABLE ACCESS FULL| B | 3 | 1 | 2 |00:00:00.01 | 6 |
| 3 | TABLE ACCESS FULL | A | 1 | 3 | 3 |00:00:00.01 | 3 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("B"."ID"=:B1)
也有这个问题，看来以后留意标量子查询SQL的执行计划的执行顺序问题了。
参考文档：
DBMS_XPLAN : Display Execution Plans：https://oracle-base.com/articles/9i/dbms_xplan#extending_dbms_xplan
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
