OceanBase执行计划突变

青年数据库学习互助会 2025-11-26

“ OceanBase执行计划突变”

上文说道OceanBase优化器选择的三步走，文末提到了其中一种规避方式是在谓词条件中指定分区(不推荐)，下文就说下为什么不推荐这种方式，当指定了分区照样走错执行计划的案例。

OB优化器的三步走

—

案例演示

1、案例SQL和慢的执行计划

SELECT *
  from EP_SALE s, EP_SALE_MINGXI d
 where s.id = '12345678912356'
   and s.region = 643
   and s.kink_id = 'xxxxx'
   and s.bank_id = d.bank_id
   and d.kink_id = 'xxxxx'
   and d.region = 643;


1 row in set (1 min 28.65 sec)
*************************** 1. row ***************************
Query Plan: =================================================================
|ID|OPERATOR                        |NAME        |EST. ROWS|COST|
-----------------------------------------------------------------
|0 |PX COORDINATOR                  |            |1        |60  |
|1 | EXCHANGE OUT DISTR             |:EX10001    |1        |60  |
|2 |  NESTED-LOOP JOIN              |            |1        |60  |
|3 |   EXCHANGE IN DISTR            |            |1        |37  |
|4 |    EXCHANGE OUT DISTR (BC2HOST)|:EX10000    |1        |37  |
|5 |     PX PARTITION ITERATOR      |            |1        |37  |
|6 |      TABLE SCAN                |D           |1        |37  |
|7 |   PX PARTITION ITERATOR        |            |1        |90  |
|8 |    TABLE SCAN                  |S(PK_BKSALE)|1        |90  |
=================================================================
Outputs & filters: 
-------------------------------------
中间部分省略...
  6 - partitions(p[137-142], p150, p244, p262, p280, p298, p316, p334), 
      range_key([D.REGION(0xffd62a65fdc0)], [D.STATUS_DATE(0xffd62a6600b0)], [D.__pk_increment(0xffd62a6c4620)]), range(643,MIN,MIN ; 643,MAX,MAX), 
      range_cond([D.REGION(0xffd62a65fdc0) = 643(0xffd62a66e810)])


省略...


OptBKization Info:
-------------------------------------
D:table_rows:949371, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:0, est_method:remote_storage, optBKization_method=cost_based 
S:table_rows:482914, physical_range_rows:1, logical_range_rows:1, index_back_rows:1, output_rows:0, est_method:remote_storage, optBKization_method=cost_based


-------------------------------------

2、分别查看两个表的数据量，可以确定S表数据最少，S表应该作为驱动表，但优化器选择D表作为驱动表导致计划跑偏。执行计划OptBKization Info部分，D表的估行是1行实际返回26万行，我指定了D表分区键为什么优化器对D表的估行还是错误的呢？

--S表：
select count(*)
  from EP_SALE s
 where s.id = '12345678912356'
   and s.region = 643
   and s.kink_id = 'xxxxx';
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.03 sec)


--D表
select count(*)
from EP_SALE_MINGXI d
where d.kink_id = 'xxxxx'
   and d.region = 643;
+----------+
| COUNT(*) |
+----------+
|   261100 |
+----------+
1 row in set (0.55 sec)

—

按照“OB优化器三步走”的方法慢慢分析

引用官网说明：

physical_range_rows：存储层对表估行估出的物理行数，优化器算 cost 参考的行数，当多分区表全表扫描时，优化器会选择数据量最大的分区统计信息行数*扫描的分区数来估算。

2.1 查看 D 表涉及的分区 row_count 信息

D表分区扫描partitions(p[137-142], p150, p244, p262, p280, p298, p316, p334

select a.svr_ip, b.part_id, b.part_name, a.row_count
  from __all_virtual_partition_table a 
       left join
       __all_virtual_partition_item b 
       on a.partition_id = b.partition_id and a.table_Id = b.table_id
 where a.table_id = 1114904790658523
  and a.role=1 
  order by part_id;
   
+----------------+---------+--------------+-----------+
| svr_ip         | part_id | part_name    | row_count |
+----------------+---------+--------------+-----------+
| 192.168.80.109 |     137 | P_643_202408 |    131393 |
| 192.168.80.110 |     138 | P_643_202409 |    116057 |
| 192.168.80.110 |     139 | P_643_202410 |    108696 |
| 192.168.80.109 |     140 | P_643_202411 |    103128 |
| 192.168.80.109 |     141 | P_643_202412 |    129823 |
| 192.168.80.109 |     244 | P_643_202501 |    121747 |
| 192.168.80.109 |     262 | P_643_202502 |     91022 |
| 192.168.80.109 |     280 | P_643_202503 |         0 |
| 192.168.80.109 |     298 | P_643_202504 |         0 |
| 192.168.80.109 |     316 | P_643_202505 |         0 |
| 192.168.80.109 |     334 | P_643_202506 |         0 |
| 192.168.80.109 |     142 | P_643_MAX    |         0 |
| 192.168.80.110 |     150 | P_646_202408 |    137580 | --统计信息行数最多的分区
+----------------+---------+--------------+-----------+

2.2 扫描涉及的13个分区中row_count最大的是 P_646_202408 分区，该分区内没有 region=643 的数据，优化器就会估为1行。

select count(*) from BK.EP_SALE_MINGXI partition (P_646_202408) where region=643;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+

2.3 小结：

EP_SALE_MINGXI.region = 643 存储层估行，依据region = 643扫描分区 (P_643_前缀分区+P_646_202408)里 row_count 最大的分区(P_646_202408) 数据进行估行，该分区内如果没有region=643的数据，就会估出1行。

相信很多人在这有个疑问，为何会扫描到P_646_202408分区？

执行计划中D表的算子详情range_key部分：range_key([D.REGION(0xffd62a65fdc0)], [D.STATUS_DATE(0xffd62a6600b0)], [D.__pk_increment(0xffd62a6c4620)])，可以得知D表的分区键是多列的，但查询条件只有region=643，所以要满足这个查询范围range(643,MIN,MIN ; 643,MAX,MAX)，P_643_MAX(643,'2037-01-01 00:00:00') < 643,MAX < P_646_202408(646,'2024-09-01 00:00:00')，所以P_646_202408也在读取范围内。

—

规避方案

3.1 OUTLINE 绑定计划，防止性能回退。

3.2 在P_635_MAX和P_636_202407中间新增一个MAXVALUE空分区后查看执行计划

--新增分区
obclient> alter table EP_SALE_MINGXI add partition P_635_MAX_NEW values less than (635,MAXVALUE);
obclient> desc select bank_id from EP_SALE_MINGXI where region=635\G
*************************** 1. row ***************************
Query Plan: =============================================================
|ID|OPERATOR               |NAME          |EST. ROWS|COST   |
-------------------------------------------------------------
|0 |PX COORDINATOR         |              |6906945  |4925200|
|1 | EXCHANGE OUT DISTR    |:EX10000      |6906945  |2636859|
|2 |  PX PARTITION ITERATOR|              |6906945  |2636859|
|3 |   TABLE SCAN          |EP_SALE_MINGXI|6906945  |2636859|
=============================================================
Outputs & filters: 
-------------------------------------
  0 - output([EP_SALE_MINGXI.bank_id]), filter(nil)
  1 - output([EP_SALE_MINGXI.bank_id]), filter(nil), dop=1
  2 - output([EP_SALE_MINGXI.bank_id]), filter(nil)
  3 - output([EP_SALE_MINGXI.bank_id]), filter(nil), 
      access([EP_SALE_MINGXI.bank_id]), partitions(p[71-77], p239, p257, p275, p293, p311, p329, p344)


--分区信息
+---------------+---------+--------------+-----------+
| svr_ip        | part_id | part_name    | row_count |
+---------------+---------+--------------+-----------+
| 192.168.110.2 |      71 | P_635_202407 |    364344 |
| 192.168.110.2 |      72 | P_635_202408 |    461278 |--统计信息行数最多的分区
| 192.168.110.2 |      73 | P_635_202409 |    272766 |
| 192.168.110.2 |      74 | P_635_202410 |    229560 |
| 192.168.110.2 |      75 | P_635_202411 |    229452 |
| 192.168.110.2 |      76 | P_635_202412 |    285960 |
| 192.168.110.2 |     239 | P_635_202501 |    169491 |
| 192.168.110.2 |     257 | P_635_202502 |         0 |
| 192.168.110.2 |     275 | P_635_202503 |         0 |
| 192.168.110.2 |     293 | P_635_202504 |         0 |
| 192.168.110.2 |     311 | P_635_202505 |         0 |
| 192.168.110.2 |     329 | P_635_202506 |         0 |
| 192.168.110.2 |      77 | P_635_MAX    |         0 |
| 192.168.110.2 |     344 | P_635_MAX_NEW|         0 |
+---------------+---------+--------------+-----------+

--计算预估行数
select bank_id from EP_SALE_MINGXI partition(P_635_202408) where region=635;
+----------+
| COUNT(*) |
+----------+
|   493375 |
+----------+
493375*14(扫描分区数)=6907250 约等于 EST. ROWS

可以发现新增空分区后，优化器估行正确了。

—

验证为什么会扫描超过 P_MAX 的分区

引用官网介绍：

RANGE分区

每个 Range 分区都包含一个 VALUES LESS THAN 子句，该子句用于指定分区的非包容性上限，等于或高于此文本的分区键的任何值都将添加到下一个更高的分区。除了第一个分区之外，所有分区都有一个由上一个分区的 VALUES LESS THAN 子句指定的隐式下限。

4.1 创建测试表

create table test_table (
"REGION" NUMBER(6) NOT NULL,
"STATUS_DATE" DATE DEFAULT sysdate NOT NULL)
partition by range("REGION", "STATUS_DATE")
(partition P_643_202408 values less than (643,TO_DATE('2024-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P_643_202409 values less than (643,TO_DATE('2024-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P_643_202410 values less than (643,TO_DATE('2024-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P_643_202411 values less than (643,TO_DATE('2024-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P_643_202412 values less than (643,TO_DATE('2025-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P_643_202501 values less than (643,TO_DATE('2025-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P_643_202502 values less than (643,TO_DATE('2025-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P_643_202503 values less than (643,TO_DATE('2025-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P_643_202504 values less than (643,TO_DATE('2025-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P_643_202505 values less than (643,TO_DATE('2025-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P_643_202506 values less than (643,TO_DATE('2025-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P_643_MAX values less than (643,TO_DATE('2037-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P_646_202408 values less than (646,TO_DATE('2024-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P_646_202409 values less than (646,TO_DATE('2024-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P_646_202410 values less than (646,TO_DATE('2024-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P_646_202411 values less than (646,TO_DATE('2024-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P_646_202412 values less than (646,TO_DATE('2025-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P_646_202501 values less than (646,TO_DATE('2025-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P_646_202502 values less than (646,TO_DATE('2025-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P_646_202503 values less than (646,TO_DATE('2025-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P_646_202504 values less than (646,TO_DATE('2025-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P_646_202505 values less than (646,TO_DATE('2025-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P_646_202506 values less than (646,TO_DATE('2025-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P_646_MAX values less than (646,TO_DATE('2037-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
);


--分区信息
+---------------+---------+--------------+-----------+
| svr_ip        | part_id | part_name    | row_count |
+---------------+---------+--------------+-----------+
| 192.168.110.2 |       0 | P_643_202408 |         0 |
| 192.168.110.2 |       1 | P_643_202409 |         0 |
| 192.168.110.2 |       2 | P_643_202410 |         0 |
| 192.168.110.2 |       3 | P_643_202411 |         0 |
| 192.168.110.2 |       4 | P_643_202412 |         0 |
| 192.168.110.2 |       5 | P_643_202501 |         0 |
| 192.168.110.2 |       6 | P_643_202502 |         0 |
| 192.168.110.2 |       7 | P_643_202503 |         0 |
| 192.168.110.2 |       8 | P_643_202504 |         0 |
| 192.168.110.2 |       9 | P_643_202505 |         0 |
| 192.168.110.2 |      10 | P_643_202506 |         0 |
| 192.168.110.2 |      11 | P_643_MAX    |         0 |
| 192.168.110.2 |      12 | P_646_202408 |         0 |
| 192.168.110.2 |      13 | P_646_202409 |         0 |
| 192.168.110.2 |      14 | P_646_202410 |         0 |
| 192.168.110.2 |      15 | P_646_202411 |         0 |
| 192.168.110.2 |      16 | P_646_202412 |         0 |
| 192.168.110.2 |      17 | P_646_202501 |         0 |
| 192.168.110.2 |      18 | P_646_202502 |         0 |
| 192.168.110.2 |      19 | P_646_202503 |         0 |
| 192.168.110.2 |      20 | P_646_202504 |         0 |
| 192.168.110.2 |      21 | P_646_202505 |         0 |
| 192.168.110.2 |      22 | P_646_202506 |         0 |
| 192.168.110.2 |      23 | P_646_MAX    |         0 |
+---------------+---------+--------------+-----------+

4.2 插入越界数据（超过分区定义时间范围）

obclient> insert into test_table values (643,TO_DATE('2037-01-01 02:00:00', 'YYYY-MM-DD HH24:MI:SS'));
obclient> commit;
obclient> select * from test_table;
+--------+---------------------+
| REGION | STATUS_DATE         |
+--------+---------------------+
|    643 | 2037-01-01 02:00:00 |
+--------+---------------------+

4.3 可以发现插入的越界数据落在了 P_646_202408 分区上

obclient> select * from test_table partition (P_646_202408);
+--------+---------------------+
| REGION | STATUS_DATE         |
+--------+---------------------+
|    643 | 2037-01-01 02:00:00 |
+--------+---------------------+

4.4 查看region=643的执行计划，分区扫描了 P12=P_646_202408 分区

select * from test_table where region=643;
Query Plan: =======================================================
|ID|OPERATOR               |NAME      |EST. ROWS|COST |
-------------------------------------------------------
|0 |PX COORDINATOR         |          |12870    |10104|
|1 | EXCHANGE OUT DISTR    |:EX10000  |12870    |8276 |
|2 |  PX PARTITION ITERATOR|          |12870    |8276 |
|3 |   TABLE SCAN          |TEST_TABLE|12870    |8276 |
=======================================================
Outputs & filters: 
-------------------------------------
      access([TEST_TABLE.REGION], [TEST_TABLE.STATUS_DATE]), partitions(p[0-12])

4.5 可以确定分区扫描时，扫描超过P_MAX的分区是为了查看一级分区的数据是否落在下一个分区。

进一步验证了官网的RANGE分区说法：所有分区都有一个由上一个分区的 VALUES LESS THAN 子句指定的隐式下限。

—

总结

1、优化器的bug

3.x版本存储层采样不够灵活，错误的按照数据量最大的分区统计信息进行估行，会导致优化器选择失误，出现执行计划突变的风险。

2、oracle和ob最大分区逻辑

oracle split partition执行时会检查待split的分区中的数据是否要在新分区之间移动来满足分区边界约束，OB add partition单纯的做新增分区的操作，并不会移动存量数据，会有分区越界的风险，而ob为了规避分区越界反而产生了新的问题。

文章转载自青年数据库学习互助会，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

OceanBase执行计划突变

评论