Oracle执行计划阅读不符合"最右最上最先执行"的一例

 

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

posted @   PiscesCanon  阅读(3)  评论(0)  编辑  收藏  举报
相关博文：
· Oracle SQL优化过程一则以及group by少见用法报错点
· Oracle内连接HASH JOIN/半连接HASH JOIN SEMI不处理驱动表连接列null值
· oracle查看执行计划
· Oracle_5_执行计划
· 22.执行计划
阅读排行：
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 单线程的Redis速度为什么快？
点击右上角即可分享
微信分享提示
AI FOR CODE 大赛