OceanBase执行计划突变

OceanBase执行计划突变”

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

—

案例演示

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 dwhere 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])