执行计划突变的验证

2024年 5月 7日 88.0k 0

背景:

因为前期遇到过执行计划突变导致的集群问题,我们做了一些监控及手段,我对于这些现象和原理简单验证和分享一下。

扩展分享:

1. ob在4.0增加了cursor_sharing参数

该参数主要是为了用于判断处理 SQL 时是否进行参数化。OB在前期版本默认把一些常量会做参数化处理,这样多个不同参数的sql可以复用执行计划,减少解析的代价,但是对于数据倾斜的情况会出现走错执行计划的情况,暂时这篇分享不详细探究了。也与白鳝老师探讨请教了下,可以参考白鳝老师的公众号。链接:https://mp.weixin.qq.com/s/z4fk7PmA7uam2Jpj-A4PXw

2. ob在4.x提升对于pl的一些优化

我做了个很简单的测试,因为本篇不是主要探究这个事情,简单介绍下让大家知道,在ob的3.x版本可以理解是有两套sql引擎,一套火山模型一套向量模型(4.x只有一套代码使用向量引擎,在3.x做过度),所以大部分pl在3.x还是走的老引擎,sql有些触发bug的情况下也会走到老引擎(这个问题很少见在3.x前期版本遇到过),对于匿名块可以看到sql aduit是没有sqlid的,plan cache里也查不到,是不缓存的,也就是匿名块每次都是硬解析。当然我在4.2.1版本测试plan cache里也是没有缓存的,但是已经有sqlid了,应该后续产品会把这块完善。

obclient [ACCOUNT]> DECLARE    query_2 VARCHAR2(200) := 'SELECT * from OB_PLAN_TEST1 where DDID = :x';    a number :=1;   BEGIN     while a=1 LOOP execute immediate query_2 USING  '370623.122717135116785630990292275519114'; a:=a+1; DBMS_OUTPUT.PUT_LINE(a||'完成');     end loop; END;/
Query OK, 1 row affected (0.058 sec)

2完成
obclient [ACCOUNT]> DECLARE    query_2 VARCHAR2(200) := 'SELECT * from OB_PLAN_TEST1 where DDID = :x';    a number :=1;   BEGIN     while a=1 LOOP execute immediate query_2 USING  '370623.122717135116785630990292275519114'; a:=a+1; DBMS_OUTPUT.PUT_LINE(a||'完成');     end loop; END;/
Query OK, 1 row affected (0.053 sec)

2完成



MySQL [oceanbase]> select /*+parallel(32)*/ sql_id,usec_to_time(request_time),svr_ip,plan_id,params_value,query_sql,plan_type,ELAPSED_TIME from gv$ob_sql_audit where query_sql like '%DECLARE%' order by request_time limit 50;
+----------------------------------+----------------------------+----------------+---------+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+--------------+
| sql_id                           | usec_to_time(request_time) | svr_ip         | plan_id | params_value | query_sql                                                                                                                                                                                                                                                                 | plan_type | ELAPSED_TIME |
+----------------------------------+----------------------------+----------------+---------+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+--------------+
| D62BAB1A05D83614534EF5260991A487 | 2023-12-04 14:44:01.209444 | 172.16.100.137 |       0 |              | DECLARE    query_2 VARCHAR2(200) := 'SELECT * from OB_PLAN_TEST1 where DDID = :x';    a number :=1;   BEGIN     while a=1 LOOP execute immediate query_2 USING  '370623.122717135116785630990292275519114'; a:=a+1; DBMS_OUTPUT.PUT_LINE(a||'完成');     end loop; END;   |         0 |        56042 |
| D62BAB1A05D83614534EF5260991A487 | 2023-12-04 14:44:02.073641 | 172.16.100.137 |       0 |              | DECLARE    query_2 VARCHAR2(200) := 'SELECT * from OB_PLAN_TEST1 where DDID = :x';    a number :=1;   BEGIN     while a=1 LOOP execute immediate query_2 USING  '370623.122717135116785630990292275519114'; a:=a+1; DBMS_OUTPUT.PUT_LINE(a||'完成');     end loop; END;   |         0 |        51807 |
+----------------------------------+----------------------------+----------------+---------+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+--------------+
2 rows in set (0.01 sec)

obclient [ACCOUNT]> select  * from  gv$ob_plan_cache_plan_stat where query_id ='D62BAB1A05D83614534EF5260991A487';
Empty set (0.057 sec)

单表的执行计划突变及验证过程:

一般单表计划改变,都是因为plan_cache被刷掉后,sql重新解析,因为各种原因(如统计信息失效或者对于这条sql代价比较低)生成了差的执行计划,后续sql会复用该计划导致负载异常。ps:ob每晚会定时合并,会收集存储引擎级别的统计信息,如果手工收集了统计信息,优先级会高于自动收集的统计信息。

--生成测试数据
CREATE TABLE "OB_PLAN_TEST1" (
   "DDID" VARCHAR2(40)  NOT NULL ENABLE,
   "DDNAME" VARCHAR2(128),
   "CCCID" VARCHAR2(32)  NOT NULL ENABLE,
   "FFFF" NUMBER(3),
   "DDDDD" NUMBER(1),
   "STATUSDATE" DATE,
   "FS" VARCHAR2(1024)
 );
 
 
declare
jid number :=0;
begin
loop  
    jid := jid+1;
    insert into OB_PLAN_TEST1 values( dbms_random.value(1,500000),'cd','DD'||jid,2,1,sysdate,'sdas');
    --commit;
   if  jid >2000000 then
   commit;
   exit;
   end if;
  end loop;
end;
/

commit;

call dbms_stats.gather_table_stats('ACCOUNT','OB_PLAN_TEST1',granularity=>'ALL',method_opt=>'FOR ALL COLUMNS SIZE 128');

create index idx_OB_PLAN_TEST1 on OB_PLAN_TEST1(DDID);

update OB_PLAN_TEST1 set DDID=1 where rownum < 500000;
commit;

--数据库版本(经测试在4.2.1版本仍然存在该问题)
observer (OceanBase 3.2.3.3)

REVISION: 109010012023072721-72e06032a278e9939718a67c2512da1e9f94775a
BUILD_BRANCH: HEAD
BUILD_TIME: Jul 27 2023 22:10:46
BUILD_FLAGS: RelWithDebInfo
BUILD_INFO: 

Copyright (c) 2011-2020 Alipay Inc.
--验证过程
obclient> select  *  from OB_PLAN_TEST1 where DDID<> 1 and rownum=1;
+------------------------------------------+--------+---------+------+-------+---------------------+------+
| DDID                                     | DDNAME | CCCID   | FFFF | DDDDD | STATUSDATE          | FS   |
+------------------------------------------+--------+---------+------+-------+---------------------+------+
| 153477.033350996877667355771590526403007 | cd     | DD50000 |    2 |     1 | 2023-11-29 17:15:28 | sdas |
+------------------------------------------+--------+---------+------+-------+---------------------+------+
1 row in set (0.06 sec)
select count(*) from OB_PLAN_TEST1 ;
select count(*) from OB_PLAN_TEST1 where DDID='1';
select count(*) from OB_PLAN_TEST1 where DDID='153477.033350996877667355771590526403007';
explain select * from OB_PLAN_TEST1 where DDID='1'\G
explain select * from OB_PLAN_TEST1 where DDID='153477.033350996877667355771590526403007'\G




obclient> select count(*) from OB_PLAN_TEST1 ;
+----------+
| COUNT(*) |
+----------+
|   200001 |
+----------+
1 row in set (0.11 sec)
--DDID='1'为大账号
obclient> select count(*) from OB_PLAN_TEST1 where DDID='1';
+----------+
| COUNT(*) |
+----------+
|    49999 |
+----------+
1 row in set (0.04 sec)
--DDID='153477.033350996877667355771590526403007'为小账号
obclient> select count(*) from OB_PLAN_TEST1 where DDID='153477.033350996877667355771590526403007';
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

--可以看到explain执行计划是对的,大账号走全表扫描,小账号走索引扫描
obclient> explain select * from OB_PLAN_TEST1 where DDID='1'\G
*************************** 1. row ***************************
Query Plan: =============================================
|ID|OPERATOR  |NAME         |EST. ROWS|COST |
---------------------------------------------
|0 |TABLE SCAN|OB_PLAN_TEST1|59629    |78575|
=============================================

Outputs & filters: 
-------------------------------------
  0 - output([OB_PLAN_TEST1.DDID], [OB_PLAN_TEST1.DDNAME], [OB_PLAN_TEST1.CCCID], [OB_PLAN_TEST1.FFFF], [OB_PLAN_TEST1.DDDDD], [OB_PLAN_TEST1.STATUSDATE], [OB_PLAN_TEST1.FS]), filter([OB_PLAN_TEST1.DDID = ?]), 
      access([OB_PLAN_TEST1.DDID], [OB_PLAN_TEST1.DDNAME], [OB_PLAN_TEST1.CCCID], [OB_PLAN_TEST1.FFFF], [OB_PLAN_TEST1.DDDDD], [OB_PLAN_TEST1.STATUSDATE], [OB_PLAN_TEST1.FS]), partitions(p0)

1 row in set (0.01 sec)

obclient> explain select * from OB_PLAN_TEST1 where DDID='153477.033350996877667355771590526403007'\G
*************************** 1. row ***************************
Query Plan: ===============================================================
|ID|OPERATOR  |NAME                            |EST. ROWS|COST|
---------------------------------------------------------------
|0 |TABLE SCAN|OB_PLAN_TEST1(IDX_OB_PLAN_TEST1)|1        |4   |
===============================================================

Outputs & filters: 
-------------------------------------
  0 - output([OB_PLAN_TEST1.DDID], [OB_PLAN_TEST1.DDNAME], [OB_PLAN_TEST1.CCCID], [OB_PLAN_TEST1.FFFF], [OB_PLAN_TEST1.DDDDD], [OB_PLAN_TEST1.STATUSDATE], [OB_PLAN_TEST1.FS]), filter(nil), 
      access([OB_PLAN_TEST1.DDID], [OB_PLAN_TEST1.DDNAME], [OB_PLAN_TEST1.CCCID], [OB_PLAN_TEST1.FFFF], [OB_PLAN_TEST1.DDDDD], [OB_PLAN_TEST1.STATUSDATE], [OB_PLAN_TEST1.FS]), partitions(p0)

1 row in set (0.00 sec)

--清空plancache和sqlaudit,使接下来sql重新解析,方便查看
MySQL [oceanbase]> alter system flush plan cache global;
Query OK, 0 rows affected (0.10 sec)

MySQL [oceanbase]> alter system flush sql audit global;
Query OK, 0 rows affected (0.20 sec)


--分别执行大小账号的查询
obclient> select * from OB_PLAN_TEST1 where DDID='1';
.
.
49999 rows in set (0.29 sec)



obclient> select * from OB_PLAN_TEST1 where DDID='153477.033350996877667355771590526403007';
+------------------------------------------+--------+---------+------+-------+---------------------+------+
| DDID                                     | DDNAME | CCCID   | FFFF | DDDDD | STATUSDATE          | FS   |
+------------------------------------------+--------+---------+------+-------+---------------------+------+
| 153477.033350996877667355771590526403007 | cd     | DD50000 |    2 |     1 | 2023-11-29 17:15:28 | sdas |
+------------------------------------------+--------+---------+------+-------+---------------------+------+
1 row in set (0.14 sec)


MySQL [oceanbase]> select  sql_id,query_sql from  gv$sql_audit  where query_sql like '%select * from OB_PLAN_TEST1 where DDID%' and  query_sql not like '%query_sql%' and query_sql not like '%explain%'\G
*************************** 1. row ***************************
   sql_id: CBD4BDA406ED0B73D47864A5739C44CE
query_sql: select * from OB_PLAN_TEST1 where DDID='1'
*************************** 2. row ***************************
   sql_id: CBD4BDA406ED0B73D47864A5739C44CE
query_sql: select * from OB_PLAN_TEST1 where DDID='153477.033350996877667355771590526403007'
2 rows in set (2.61 sec)


MySQL [oceanbase]> select /*+parallel(32)*/ sql_id,usec_to_time(request_time),svr_ip,plan_id,params_value,query_sql,plan_type from gv$sql_audit where sql_id='CBD4BDA406ED0B73D47864A5739C44CE' order by request_time limit 10;
+----------------------------------+----------------------------+-------------+---------+--------------+-----------------------------------------------------------------------------------+-----------+
| sql_id                           | usec_to_time(request_time) | svr_ip      | plan_id | params_value | query_sql                                                                         | plan_type |
+----------------------------------+----------------------------+-------------+---------+--------------+-----------------------------------------------------------------------------------+-----------+
| CBD4BDA406ED0B73D47864A5739C44CE | 2023-11-29 17:22:01.244336 | 111.11.11.1 | 3081629 |              | select * from OB_PLAN_TEST1 where DDID='1'                                        |         1 |
| CBD4BDA406ED0B73D47864A5739C44CE | 2023-11-29 17:22:28.842721 | 111.11.11.1 | 3081629 |              | select * from OB_PLAN_TEST1 where DDID='153477.033350996877667355771590526403007' |         1 |
+----------------------------------+----------------------------+-------------+---------+--------------+-----------------------------------------------------------------------------------+-----------+
2 rows in set (0.87 sec)
--可以看到最终小账号也复用了全表扫描的执行计划
MySQL [oceanbase]> select plan_id, operator, name, rows, cost from gv$plan_cache_plan_explain where tenant_id=1005 and ip='111.11.11.1' and port=2882 and plan_id=3081629;
+---------+----------------+---------------+-------+-------+
| plan_id | operator       | name          | rows  | cost  |
+---------+----------------+---------------+-------+-------+
| 3081629 | PHY_TABLE_SCAN | OB_PLAN_TEST1 | 59629 | 78574 |
+---------+----------------+---------------+-------+-------+
1 row in set (0.00 sec)

该问题其实可以使用ACS解决,但是ob的acs在实验室测试阶段引入了很多问题,现在还没有上线

分区表的执行计划突变及验证过程:

分区表相关的计划突变其实与单表类似,但是后来ob对这块做了相关优化,所以我们单独做个测试。

--生成测试数据
CREATE TABLE "OB_PLAN_TEST1" (
   "DDID" VARCHAR2(40)  NOT NULL ENABLE,
   "DDNAME" VARCHAR2(128),
   "CCCID" VARCHAR2(32)  NOT NULL ENABLE,
   "FFFF" NUMBER(3),
   "DDDDD" NUMBER(1),
   "STATUSDATE" DATE,
   "FS" VARCHAR2(1024),
   CITY NUMBER(5) NOT NULL ENABLE
 )partition by range("CITY")
(partition P_100 values less than (200),
partition P_200 values less than (300),
partition P_300 values less than (400),
partition P_400 values less than (500));
 

declare
jid number :=0;
begin
loop  
    jid := jid+1;
    insert into OB_PLAN_TEST1 values( dbms_random.value(1,500000),'cd','DD'||jid,2,1,sysdate,'sdas',400);
    --commit;
   if  jid >200000 then
   commit;
   exit;
   end if;
  end loop;
end;
/
insert into OB_PLAN_TEST1 values( dbms_random.value(1,500000),'cd','DD'||'www',2,1,sysdate,'sdas',300);
commit;

select  CITY,count(*) from  OB_PLAN_TEST1 group by CITY;
create index idx_OB_PLAN_TEST1 on OB_PLAN_TEST1(DDID) local;
call dbms_stats.gather_table_stats('ACCOUNT','OB_PLAN_TEST1',granularity=>'ALL',method_opt=>'FOR ALL COLUMNS SIZE 128');


--3.2.3的前期版本
observer (OceanBase 3.2.3.3)

REVISION: 109010012023072721-72e06032a278e9939718a67c2512da1e9f94775a
BUILD_BRANCH: HEAD
BUILD_TIME: Jul 27 2023 22:10:46
BUILD_FLAGS: RelWithDebInfo
BUILD_INFO: 

Copyright (c) 2011-2020 Alipay Inc.

--测试过程,分区300为小分区,分区400为大分区
 obclient> select  CITY,count(*) from  OB_PLAN_TEST1 group by CITY;
+------+----------+
| CITY | COUNT(*) |
+------+----------+
|  300 |        1 |
|  400 |   200001 |
+------+----------+
2 rows in set (0.20 sec)
obclient> select  *  from OB_PLAN_TEST1 where CITY=300;
+------------------------------------------+--------+-------+------+-------+---------------------+------+------+
| DDID                                     | DDNAME | CCCID | FFFF | DDDDD | STATUSDATE          | FS   | CITY |
+------------------------------------------+--------+-------+------+-------+---------------------+------+------+
| 438916.090508550075909523829593371519369 | cd     | DDwww |    2 |     1 | 2023-12-01 16:41:23 | sdas |  300 |
+------------------------------------------+--------+-------+------+-------+---------------------+------+------+
1 row in set (0.03 sec)

obclient> select  *  from OB_PLAN_TEST1 where CITY=400 and rownum=1;
+------------------------------------------+--------+-------+------+-------+---------------------+------+------+
| DDID                                     | DDNAME | CCCID | FFFF | DDDDD | STATUSDATE          | FS   | CITY |
+------------------------------------------+--------+-------+------+-------+---------------------+------+------+
| 226565.232611556997487602769478763788588 | cd     | DD1   |    2 |     1 | 2023-12-01 16:38:54 | sdas |  400 |
+------------------------------------------+--------+-------+------+-------+---------------------+------+------+
1 row in set (0.02 sec)



explain select * from OB_PLAN_TEST1 where DDID='438916.090508550075909523829593371519369' and  CITY=300\G
explain select * from OB_PLAN_TEST1 where DDID='226565.232611556997487602769478763788588' and  CITY=400\G


--可以看到explain执行计划是对的,小分区走全表扫描,大分区走索引扫描
obclient> explain select * from OB_PLAN_TEST1 where DDID='438916.090508550075909523829593371519369' and  CITY=300\G
*************************** 1. row ***************************
Query Plan: ============================================
|ID|OPERATOR  |NAME         |EST. ROWS|COST|
--------------------------------------------
|0 |TABLE SCAN|OB_PLAN_TEST1|1        |1   |
============================================

Outputs & filters: 
-------------------------------------
  0 - output([OB_PLAN_TEST1.DDID], [OB_PLAN_TEST1.DDNAME], [OB_PLAN_TEST1.CCCID], [OB_PLAN_TEST1.FFFF], [OB_PLAN_TEST1.DDDDD], [OB_PLAN_TEST1.STATUSDATE], [OB_PLAN_TEST1.FS], [OB_PLAN_TEST1.CITY]), filter([OB_PLAN_TEST1.DDID = ?]), 
      access([OB_PLAN_TEST1.CITY], [OB_PLAN_TEST1.DDID], [OB_PLAN_TEST1.DDNAME], [OB_PLAN_TEST1.CCCID], [OB_PLAN_TEST1.FFFF], [OB_PLAN_TEST1.DDDDD], [OB_PLAN_TEST1.STATUSDATE], [OB_PLAN_TEST1.FS]), partitions(p2)

1 row in set (0.00 sec)

obclient> explain select * from OB_PLAN_TEST1 where DDID='226565.232611556997487602769478763788588' and  CITY=400\G
*************************** 1. row ***************************
Query Plan: ===============================================================
|ID|OPERATOR  |NAME                            |EST. ROWS|COST|
---------------------------------------------------------------
|0 |TABLE SCAN|OB_PLAN_TEST1(IDX_OB_PLAN_TEST1)|1        |4   |
===============================================================

Outputs & filters: 
-------------------------------------
  0 - output([OB_PLAN_TEST1.DDID], [OB_PLAN_TEST1.DDNAME], [OB_PLAN_TEST1.CCCID], [OB_PLAN_TEST1.FFFF], [OB_PLAN_TEST1.DDDDD], [OB_PLAN_TEST1.STATUSDATE], [OB_PLAN_TEST1.FS], [OB_PLAN_TEST1.CITY]), filter(nil), 
      access([OB_PLAN_TEST1.CITY], [OB_PLAN_TEST1.DDID], [OB_PLAN_TEST1.DDNAME], [OB_PLAN_TEST1.CCCID], [OB_PLAN_TEST1.FFFF], [OB_PLAN_TEST1.DDDDD], [OB_PLAN_TEST1.STATUSDATE], [OB_PLAN_TEST1.FS]), partitions(p3)

1 row in set (0.00 sec)

--清空plancache和sqlaudit
alter system flush plan cache global;
alter system flush sql audit global;



MySQL [oceanbase]> select  sql_id,query_sql from  gv$sql_audit  where query_sql like '%select * from OB_PLAN_TEST1 where DDID=%' and  query_sql not like '%query_sql%' and query_sql not like '%explain%'\G
*************************** 1. row ***************************
   sql_id: 2FA089055677A69C67B9E4F816A8D9EF
query_sql: select * from OB_PLAN_TEST1 where DDID='438916.090508550075909523829593371519369' and  CITY=300
*************************** 2. row ***************************
   sql_id: 2FA089055677A69C67B9E4F816A8D9EF
query_sql: select * from OB_PLAN_TEST1 where DDID='226565.232611556997487602769478763788588' and  CITY=400
2 rows in set (0.17 sec)

MySQL [oceanbase]> select /*+parallel(32)*/ sql_id,usec_to_time(request_time),svr_ip,plan_id,params_value,query_sql,plan_type,ELAPSED_TIME from gv$sql_audit where sql_id='2FA089055677A69C67B9E4F816A8D9EF' order by request_time limit 50;
+----------------------------------+----------------------------+-------------+---------+--------------+-------------------------------------------------------------------------------------------------+-----------+--------------+
| sql_id                           | usec_to_time(request_time) | svr_ip      | plan_id | params_value | query_sql                                                                                       | plan_type | ELAPSED_TIME |
+----------------------------------+----------------------------+-------------+---------+--------------+-------------------------------------------------------------------------------------------------+-----------+--------------+
| 2FA089055677A69C67B9E4F816A8D9EF | 2023-12-01 16:48:50.012069 | 134.84.16.2 |    7364 |              | select * from OB_PLAN_TEST1 where DDID='438916.090508550075909523829593371519369' and  CITY=300 |         1 |         1706 |
| 2FA089055677A69C67B9E4F816A8D9EF | 2023-12-01 16:48:59.847533 | 134.84.16.2 |    7364 |              | select * from OB_PLAN_TEST1 where DDID='226565.232611556997487602769478763788588' and  CITY=400 |         1 |       144013 |
+----------------------------------+----------------------------+-------------+---------+--------------+-------------------------------------------------------------------------------------------------+-----------+--------------+
2 rows in set (0.07 sec)
--可以看到大分区账号复用了小分区账号的执行计划,走了错误的执行计划
MySQL [oceanbase]> select plan_id, operator, name, rows, cost from gv$plan_cache_plan_explain where tenant_id=1005 and ip='134.84.16.2' and port=2882 and plan_id=7364;
+---------+----------------+---------------+------+------+
| plan_id | operator       | name          | rows | cost |
+---------+----------------+---------------+------+------+
|    7364 | PHY_TABLE_SCAN | OB_PLAN_TEST1 |    1 |    0 |
+---------+----------------+---------------+------+------+
1 row in set (0.01 sec)



--3.2.3后期版本引入了两个参数
observer (OceanBase 3.2.3.3)

REVISION: 110020012023110315-55cf8deebb891498d8c87ab742cde4809c018219
BUILD_BRANCH: HEAD
BUILD_TIME: Nov  3 2023 15:27:14
BUILD_FLAGS: RelWithDebInfo
BUILD_INFO: 

Copyright (c) 2011-2020 Alipay Inc.

<><><><><><><>
##############################
--根据分区分裂执行计划
MySQL [oceanbase]> alter system set _enable_partition_level_acs = True tenant='cc1cs';
Query OK, 0 rows affected (0.03 sec)
--索引代价评估(该参数在这个实验中先不介绍了)
MySQL [oceanbase]>  alter system set optimizer_index_cost_adj =1 tenant='cc1cs';
Query OK, 0 rows affected (0.02 sec)

MySQL [oceanbase]> select  * from __all_virtual_tenant_parameter_info  where name like '%enable_partition_level_acs%';
+-----------+-------+----------+-------------+----------+-----------------------------+-----------+-------+-------------------------------+---------+--------+---------+-------------------+
| tenant_id | zone  | svr_type | svr_ip      | svr_port | name                        | data_type | value | info                          | section | scope  | source  | edit_level        |
+-----------+-------+----------+-------------+----------+-----------------------------+-----------+-------+-------------------------------+---------+--------+---------+-------------------+
|         1 | zone1 | observer | 122.22.22.8 |     2882 | _enable_partition_level_acs | NULL      | True  | enable acs in partition level | TENANT  | TENANT | DEFAULT | DYNAMIC_EFFECTIVE |
|      1003 | zone1 | observer | 122.22.22.9 |     2882 | _enable_partition_level_acs | NULL      | True  | enable acs in partition level | TENANT  | TENANT | DEFAULT | DYNAMIC_EFFECTIVE |
|         1 | zone1 | observer | 122.22.22.8 |     2882 | _enable_partition_level_acs | NULL      | True  | enable acs in partition level | TENANT  | TENANT | DEFAULT | DYNAMIC_EFFECTIVE |
|      1003 | zone1 | observer | 122.22.22.8 |     2882 | _enable_partition_level_acs | NULL      | True  | enable acs in partition level | TENANT  | TENANT | DEFAULT | DYNAMIC_EFFECTIVE |
+-----------+-------+----------+-------------+----------+-----------------------------+-----------+-------+-------------------------------+---------+--------+---------+-------------------+
4 rows in set (0.06 sec)
MySQL [oceanbase]> select  * from __all_virtual_tenant_parameter_info  where name like '%optimizer_index_cost_adj%';
+-----------+-------+----------+-------------+----------+--------------------------+-----------+-------+------------------------------+---------+--------+---------+-------------------+
| tenant_id | zone  | svr_type | svr_ip      | svr_port | name                     | data_type | value | info                         | section | scope  | source  | edit_level        |
+-----------+-------+----------+-------------+----------+--------------------------+-----------+-------+------------------------------+---------+--------+---------+-------------------+
|         1 | zone1 | observer | 122.22.22.8 |     2882 | optimizer_index_cost_adj | NULL      | 0     | adjust costing of index scan | TENANT  | TENANT | DEFAULT | DYNAMIC_EFFECTIVE |
|      1003 | zone1 | observer | 122.22.22.8 |     2882 | optimizer_index_cost_adj | NULL      | 1     | adjust costing of index scan | TENANT  | TENANT | DEFAULT | DYNAMIC_EFFECTIVE |
|         1 | zone1 | observer | 122.22.22.9 |     2882 | optimizer_index_cost_adj | NULL      | 0     | adjust costing of index scan | TENANT  | TENANT | DEFAULT | DYNAMIC_EFFECTIVE |
|      1003 | zone1 | observer | 122.22.22.9 |     2882 | optimizer_index_cost_adj | NULL      | 1     | adjust costing of index scan | TENANT  | TENANT | DEFAULT | DYNAMIC_EFFECTIVE |
+-----------+-------+----------+-------------+----------+--------------------------+-----------+-------+------------------------------+---------+--------+---------+-------------------+
4 rows in set (0.06 sec)

##########################
--验证过程与上面一致
obclient> select  CITY,count(*) from  OB_PLAN_TEST1 group by CITY;
+------+----------+
| CITY | COUNT(*) |
+------+----------+
|  300 |        1 |
|  400 |   200001 |
+------+----------+
2 rows in set (0.20 sec)

obclient> select  *  from OB_PLAN_TEST1 where CITY=300;
+------------------------------------------+--------+-------+------+-------+---------------------+------+------+
| DDID                                     | DDNAME | CCCID | FFFF | DDDDD | STATUSDATE          | FS   | CITY |
+------------------------------------------+--------+-------+------+-------+---------------------+------+------+
| 344423.097813308868309580547313255124084 | cd     | DDwww |    2 |     1 | 2023-12-01 16:52:32 | sdas |  300 |
+------------------------------------------+--------+-------+------+-------+---------------------+------+------+
1 row in set (0.04 sec)

obclient> select  *  from OB_PLAN_TEST1 where CITY=400 and rownum=1;
+------------------------------------------+--------+-------+------+-------+---------------------+------+------+
| DDID                                     | DDNAME | CCCID | FFFF | DDDDD | STATUSDATE          | FS   | CITY |
+------------------------------------------+--------+-------+------+-------+---------------------+------+------+
| 119548.132022013705188211994065427849317 | cd     | DD1   |    2 |     1 | 2023-12-01 16:39:50 | sdas |  400 |
+------------------------------------------+--------+-------+------+-------+---------------------+------+------+
1 row in set (0.06 sec)

explain select * from OB_PLAN_TEST1 where DDID='344423.097813308868309580547313255124084' and  CITY=300\G
explain select * from OB_PLAN_TEST1 where DDID='119548.132022013705188211994065427849317' and  CITY=400\G


obclient> explain select * from OB_PLAN_TEST1 where DDID='344423.097813308868309580547313255124084' and  CITY=300\G
*************************** 1. row ***************************
Query Plan: ============================================
|ID|OPERATOR  |NAME         |EST. ROWS|COST|
--------------------------------------------
|0 |TABLE SCAN|OB_PLAN_TEST1|1        |1   |
============================================

Outputs & filters: 
-------------------------------------
  0 - output([OB_PLAN_TEST1.DDID], [OB_PLAN_TEST1.DDNAME], [OB_PLAN_TEST1.CCCID], [OB_PLAN_TEST1.FFFF], [OB_PLAN_TEST1.DDDDD], [OB_PLAN_TEST1.STATUSDATE], [OB_PLAN_TEST1.FS], [OB_PLAN_TEST1.CITY]), filter([OB_PLAN_TEST1.DDID = ?]), 
      access([OB_PLAN_TEST1.CITY], [OB_PLAN_TEST1.DDID], [OB_PLAN_TEST1.DDNAME], [OB_PLAN_TEST1.CCCID], [OB_PLAN_TEST1.FFFF], [OB_PLAN_TEST1.DDDDD], [OB_PLAN_TEST1.STATUSDATE], [OB_PLAN_TEST1.FS]), partitions(p2)

1 row in set (0.01 sec)

obclient> explain select * from OB_PLAN_TEST1 where DDID='119548.132022013705188211994065427849317' and  CITY=400\G
*************************** 1. row ***************************
Query Plan: =========================================================================
|ID|OPERATOR            |NAME                            |EST. ROWS|COST|
-------------------------------------------------------------------------
|0 |EXCHANGE IN REMOTE  |                                |1        |6   |
|1 | EXCHANGE OUT REMOTE|                                |1        |4   |
|2 |  TABLE SCAN        |OB_PLAN_TEST1(IDX_OB_PLAN_TEST1)|1        |4   |
=========================================================================

Outputs & filters: 
-------------------------------------
  0 - output([OB_PLAN_TEST1.DDID], [OB_PLAN_TEST1.DDNAME], [OB_PLAN_TEST1.CCCID], [OB_PLAN_TEST1.FFFF], [OB_PLAN_TEST1.DDDDD], [OB_PLAN_TEST1.STATUSDATE], [OB_PLAN_TEST1.FS], [OB_PLAN_TEST1.CITY]), filter(nil)
  1 - output([OB_PLAN_TEST1.DDID], [OB_PLAN_TEST1.DDNAME], [OB_PLAN_TEST1.CCCID], [OB_PLAN_TEST1.FFFF], [OB_PLAN_TEST1.DDDDD], [OB_PLAN_TEST1.STATUSDATE], [OB_PLAN_TEST1.FS], [OB_PLAN_TEST1.CITY]), filter(nil)
  2 - output([OB_PLAN_TEST1.CITY], [OB_PLAN_TEST1.DDID], [OB_PLAN_TEST1.DDNAME], [OB_PLAN_TEST1.CCCID], [OB_PLAN_TEST1.FFFF], [OB_PLAN_TEST1.DDDDD], [OB_PLAN_TEST1.STATUSDATE], [OB_PLAN_TEST1.FS]), filter(nil), 
      access([OB_PLAN_TEST1.CITY], [OB_PLAN_TEST1.DDID], [OB_PLAN_TEST1.DDNAME], [OB_PLAN_TEST1.CCCID], [OB_PLAN_TEST1.FFFF], [OB_PLAN_TEST1.DDDDD], [OB_PLAN_TEST1.STATUSDATE], [OB_PLAN_TEST1.FS]), partitions(p3)

1 row in set (0.01 sec)



MySQL [oceanbase]> select  sql_id,query_sql from  gv$sql_audit  where query_sql like '%select * from OB_PLAN_TEST1 where DDID=%' and  query_sql not like '%query_sql%' and query_sql not like '%explain%'\G
*************************** 1. row ***************************
   sql_id: 2FA089055677A69C67B9E4F816A8D9EF
query_sql: select * from OB_PLAN_TEST1 where DDID='344423.097813308868309580547313255124084' and  CITY=300
*************************** 2. row ***************************
   sql_id: 2FA089055677A69C67B9E4F816A8D9EF
query_sql: select * from OB_PLAN_TEST1 where DDID='119548.132022013705188211994065427849317' and  CITY=400
2 rows in set (0.04 sec)

MySQL [oceanbase]> select /*+parallel(32)*/ sql_id,usec_to_time(request_time),svr_ip,plan_id,params_value,query_sql,plan_type,ELAPSED_TIME from gv$sql_audit where sql_id='2FA089055677A69C67B9E4F816A8D9EF' order by request_time limit 50;
+----------------------------------+----------------------------+-------------+---------+--------------+-------------------------------------------------------------------------------------------------+-----------+--------------+
| sql_id                           | usec_to_time(request_time) | svr_ip      | plan_id | params_value | query_sql                                                                                       | plan_type | ELAPSED_TIME |
+----------------------------------+----------------------------+-------------+---------+--------------+-------------------------------------------------------------------------------------------------+-----------+--------------+
| 2FA089055677A69C67B9E4F816A8D9EF | 2023-12-01 16:55:27.879506 | 122.22.22.9 |  110392 |              | select * from OB_PLAN_TEST1 where DDID='344423.097813308868309580547313255124084' and  CITY=300 |         1 |         2915 |
| 2FA089055677A69C67B9E4F816A8D9EF | 2023-12-01 16:55:38.931865 | 122.22.22.9 |  110398 |              | select * from OB_PLAN_TEST1 where DDID='119548.132022013705188211994065427849317' and  CITY=400 |         2 |         9502 |
+----------------------------------+----------------------------+-------------+---------+--------------+-------------------------------------------------------------------------------------------------+-----------+--------------+
2 rows in set (0.05 sec)

--可以看到开启分区分裂计划的参数后生成了两个计划,两个sql都走了正确的计划
MySQL [oceanbase]> select plan_id, operator, name, rows, cost from gv$plan_cache_plan_explain where tenant_id=1003 and ip='122.22.22.9' and port=2882 and plan_id=110392;
+---------+----------------+---------------+------+------+
| plan_id | operator       | name          | rows | cost |
+---------+----------------+---------------+------+------+
|  110392 | PHY_TABLE_SCAN | OB_PLAN_TEST1 |    1 |    0 |
+---------+----------------+---------------+------+------+
1 row in set (0.01 sec)

MySQL [oceanbase]> select plan_id, operator, name, rows, cost from gv$plan_cache_plan_explain where tenant_id=1003 and ip='122.22.22.9' and port=2882 and plan_id=110398;
+---------+----------------------+----------------------------------+------+------+
| plan_id | operator             | name                             | rows | cost |
+---------+----------------------+----------------------------------+------+------+
|  110398 | PHY_DIRECT_RECEIVE   | NULL                             |    1 |    5 |
|  110398 |  PHY_DIRECT_TRANSMIT | NULL                             |    1 |    3 |
|  110398 |   PHY_TABLE_SCAN     | OB_PLAN_TEST1(IDX_OB_PLAN_TEST1) |    1 |    3 |
+---------+----------------------+----------------------------------+------+------+
3 rows in set (0.00 sec)

因为我在4.2.1版本上没有找到该参数,那验证下实际表现如何

--版本信息
observer (OceanBase 4.2.1.1)

REVISION: 101010022023110921-5d0b2ec58d7661e382f65d3753b65f3a914f89ec
BUILD_BRANCH: HEAD
BUILD_TIME: Nov  9 2023 21:27:01
BUILD_FLAGS: RelWithDebInfo
BUILD_INFO: 

Copyright (c) 2011-present OceanBase Inc.

--数据分布,与上面实验一样,大小分区
obclient [ACCOUNT]> select  CITY,count(*) from  OB_PLAN_TEST1 group by CITY;
+------+----------+
| CITY | COUNT(*) |
+------+----------+
|  300 |        1 |
|  400 |   200001 |
+------+----------+
2 rows in set (0.305 sec)
obclient [ACCOUNT]> select  *  from OB_PLAN_TEST1 where CITY=300;
+------------------------------------------+--------+-------+------+-------+------------+------+------+
| DDID                                     | DDNAME | CCCID | FFFF | DDDDD | STATUSDATE | FS   | CITY |
+------------------------------------------+--------+-------+------+-------+------------+------+------+
| 298225.370594538611501792855979394021071 | cd     | DDwww |    2 |     1 | 01-DEC-23  | sdas |  300 |
+------------------------------------------+--------+-------+------+-------+------------+------+------+
1 row in set (0.083 sec)

obclient [ACCOUNT]> select  *  from OB_PLAN_TEST1 where CITY=400 and rownum=1;
+------------------------------------------+--------+-------+------+-------+------------+------+------+
| DDID                                     | DDNAME | CCCID | FFFF | DDDDD | STATUSDATE | FS   | CITY |
+------------------------------------------+--------+-------+------+-------+------------+------+------+
| 269826.523111291697766623468575040129842 | cd     | DD1   |    2 |     1 | 01-DEC-23  | sdas |  400 |
+------------------------------------------+--------+-------+------+-------+------------+------+------+
1 row in set (0.017 sec)

explain select * from OB_PLAN_TEST1 where DDID='298225.370594538611501792855979394021071' and  CITY=300;
explain select * from OB_PLAN_TEST1 where DDID='269826.523111291697766623468575040129842' and  CITY=400;

--这里不同的是,小分区也走了索引,因为代价不大,所以这个计划没有问题
obclient [ACCOUNT]> explain select * from OB_PLAN_TEST1 where DDID='298225.370594538611501792855979394021071' and  CITY=300;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ============================================================================                                                                                          |
| |ID|OPERATOR        |NAME                            |EST.ROWS|EST.TIME(us)|                                                                                          |
| ----------------------------------------------------------------------------                                                                                          |
| |0 |TABLE RANGE SCAN|OB_PLAN_TEST1(IDX_OB_PLAN_TEST1)|1       |7           |                                                                                          |
| ============================================================================                                                                                          |
| Outputs & filters:                                                                                                                                                    |
| -------------------------------------                                                                                                                                 |
|   0 - output([OB_PLAN_TEST1.DDID], [OB_PLAN_TEST1.DDNAME], [OB_PLAN_TEST1.CCCID], [OB_PLAN_TEST1.FFFF], [OB_PLAN_TEST1.DDDDD], [OB_PLAN_TEST1.STATUSDATE],            |
|        [OB_PLAN_TEST1.FS], [OB_PLAN_TEST1.CITY]), filter([OB_PLAN_TEST1.CITY = 300]), rowset=16                                                                       |
|       access([OB_PLAN_TEST1.__pk_increment], [OB_PLAN_TEST1.CITY], [OB_PLAN_TEST1.DDID], [OB_PLAN_TEST1.DDNAME], [OB_PLAN_TEST1.CCCID], [OB_PLAN_TEST1.FFFF],         |
|        [OB_PLAN_TEST1.DDDDD], [OB_PLAN_TEST1.STATUSDATE], [OB_PLAN_TEST1.FS]), partitions(p2)                                                                         |
|       is_index_back=true, is_global_index=false, filter_before_indexback[false],                                                                                      |
|       range_key([OB_PLAN_TEST1.DDID], [OB_PLAN_TEST1.__pk_increment]), range(298225.370594538611501792855979394021071,MIN ; 298225.370594538611501792855979394021071, |
|       MAX),                                                                                                                                                           |
|       range_cond([OB_PLAN_TEST1.DDID = cast('298225.370594538611501792855979394021071', VARCHAR2(1048576 ))])                                                         |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
15 rows in set (0.004 sec)

obclient [ACCOUNT]> explain select * from OB_PLAN_TEST1 where DDID='269826.523111291697766623468575040129842' and  CITY=400;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ============================================================================                                                                                          |
| |ID|OPERATOR        |NAME                            |EST.ROWS|EST.TIME(us)|                                                                                          |
| ----------------------------------------------------------------------------                                                                                          |
| |0 |TABLE RANGE SCAN|OB_PLAN_TEST1(IDX_OB_PLAN_TEST1)|1       |7           |                                                                                          |
| ============================================================================                                                                                          |
| Outputs & filters:                                                                                                                                                    |
| -------------------------------------                                                                                                                                 |
|   0 - output([OB_PLAN_TEST1.DDID], [OB_PLAN_TEST1.DDNAME], [OB_PLAN_TEST1.CCCID], [OB_PLAN_TEST1.FFFF], [OB_PLAN_TEST1.DDDDD], [OB_PLAN_TEST1.STATUSDATE],            |
|        [OB_PLAN_TEST1.FS], [OB_PLAN_TEST1.CITY]), filter([OB_PLAN_TEST1.CITY = 400]), rowset=16                                                                       |
|       access([OB_PLAN_TEST1.__pk_increment], [OB_PLAN_TEST1.CITY], [OB_PLAN_TEST1.DDID], [OB_PLAN_TEST1.DDNAME], [OB_PLAN_TEST1.CCCID], [OB_PLAN_TEST1.FFFF],         |
|        [OB_PLAN_TEST1.DDDDD], [OB_PLAN_TEST1.STATUSDATE], [OB_PLAN_TEST1.FS]), partitions(p3)                                                                         |
|       is_index_back=true, is_global_index=false, filter_before_indexback[false],                                                                                      |
|       range_key([OB_PLAN_TEST1.DDID], [OB_PLAN_TEST1.__pk_increment]), range(269826.523111291697766623468575040129842,MIN ; 269826.523111291697766623468575040129842, |
|       MAX),                                                                                                                                                           |
|       range_cond([OB_PLAN_TEST1.DDID = cast('269826.523111291697766623468575040129842', VARCHAR2(1048576 ))])                                                         |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
15 rows in set (0.017 sec)

--这里我验证了个特殊sql,传参为空,我们很多场景遇到过传参为空导致计划突变引发的性能抖动
obclient [ACCOUNT]> explain select * from OB_PLAN_TEST1 where DDID='' and  CITY=200;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                                       |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ==================================================================                                                                                                               |
| |ID|OPERATOR                 |NAME         |EST.ROWS|EST.TIME(us)|                                                                                                               |
| ------------------------------------------------------------------                                                                                                               |
| |0 |PX COORDINATOR           |             |200002  |1140164     |                                                                                                               |
| |1 |└─EXCHANGE OUT DISTR     |:EX10000     |200002  |797399      |                                                                                                               |
| |2 |  └─PX PARTITION ITERATOR|             |200002  |26293       |                                                                                                               |
| |3 |    └─TABLE FULL SCAN    |OB_PLAN_TEST1|200002  |26293       |                                                                                                               |
| ==================================================================                                                                                                               |
| Outputs & filters:                                                                                                                                                               |
| -------------------------------------                                                                                                                                            |
|   0 - output([INTERNAL_FUNCTION(OB_PLAN_TEST1.DDID, OB_PLAN_TEST1.DDNAME, OB_PLAN_TEST1.CCCID, OB_PLAN_TEST1.FFFF, OB_PLAN_TEST1.DDDDD, OB_PLAN_TEST1.STATUSDATE,                |
|        OB_PLAN_TEST1.FS, OB_PLAN_TEST1.CITY)]), filter(nil), startup_filter([0]), rowset=256                                                                                     |
|   1 - output([INTERNAL_FUNCTION(OB_PLAN_TEST1.DDID, OB_PLAN_TEST1.DDNAME, OB_PLAN_TEST1.CCCID, OB_PLAN_TEST1.FFFF, OB_PLAN_TEST1.DDDDD, OB_PLAN_TEST1.STATUSDATE,                |
|        OB_PLAN_TEST1.FS, OB_PLAN_TEST1.CITY)]), filter(nil), rowset=256                                                                                                          |
|       dop=1                                                                                                                                                                      |
|   2 - output([OB_PLAN_TEST1.CITY], [OB_PLAN_TEST1.DDID], [OB_PLAN_TEST1.DDNAME], [OB_PLAN_TEST1.CCCID], [OB_PLAN_TEST1.FFFF], [OB_PLAN_TEST1.DDDDD], [OB_PLAN_TEST1.STATUSDATE], |
|        [OB_PLAN_TEST1.FS]), filter(nil), rowset=256                                                                                                                              |
|       force partition granule                                                                                                                                                    |
|   3 - output([OB_PLAN_TEST1.CITY], [OB_PLAN_TEST1.DDID], [OB_PLAN_TEST1.DDNAME], [OB_PLAN_TEST1.CCCID], [OB_PLAN_TEST1.FFFF], [OB_PLAN_TEST1.DDDDD], [OB_PLAN_TEST1.STATUSDATE], |
|        [OB_PLAN_TEST1.FS]), filter(nil), rowset=256                                                                                                                              |
|       access([OB_PLAN_TEST1.CITY], [OB_PLAN_TEST1.DDID], [OB_PLAN_TEST1.DDNAME], [OB_PLAN_TEST1.CCCID], [OB_PLAN_TEST1.FFFF], [OB_PLAN_TEST1.DDDDD], [OB_PLAN_TEST1.STATUSDATE], |
|        [OB_PLAN_TEST1.FS]), partitions(p[0-3])                                                                                                                                   |
|       is_index_back=false, is_global_index=false,                                                                                                                                |
|       range_key([OB_PLAN_TEST1.__pk_increment]), range(MIN ; MAX)always true                                                                                                     |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
24 rows in set (0.019 sec)


alter system flush plan cache global;
alter system flush sql audit global;


MySQL [oceanbase]> select  sql_id,query_sql from  gv$ob_sql_audit  where query_sql like '%select * from OB_PLAN_TEST1 where DDID=%' and  query_sql not like '%query_sql%' and query_sql not like '%explain%'\G
*************************** 1. row ***************************
   sql_id: 2FA089055677A69C67B9E4F816A8D9EF
query_sql: select * from OB_PLAN_TEST1 where DDID='' and  CITY=200
*************************** 2. row ***************************
   sql_id: 2FA089055677A69C67B9E4F816A8D9EF
query_sql: select * from OB_PLAN_TEST1 where DDID='298225.370594538611501792855979394021071' and  CITY=300
*************************** 3. row ***************************
   sql_id: 2FA089055677A69C67B9E4F816A8D9EF
query_sql: select * from OB_PLAN_TEST1 where DDID='269826.523111291697766623468575040129842' and  CITY=400
3 rows in set (0.03 sec)

--可以看到生成的是同一个sqlid,生成的计划不同
MySQL [oceanbase]> select /*+parallel(32)*/ sql_id,usec_to_time(request_time),svr_ip,plan_id,params_value,query_sql,plan_type,ELAPSED_TIME from gv$ob_sql_audit where sql_id='2FA089055677A69C67B9E4F816A8D9EF' order by request_time limit 50;
+----------------------------------+----------------------------+----------------+---------+--------------+-------------------------------------------------------------------------------------------------+-----------+--------------+
| sql_id                           | usec_to_time(request_time) | svr_ip         | plan_id | params_value | query_sql                                                                                       | plan_type | ELAPSED_TIME |
+----------------------------------+----------------------------+----------------+---------+--------------+-------------------------------------------------------------------------------------------------+-----------+--------------+
| 2FA089055677A69C67B9E4F816A8D9EF | 2023-12-01 17:24:20.618319 | 172.16.100.137 |    2185 |              | select * from OB_PLAN_TEST1 where DDID='' and  CITY=200                                         |         3 |         5143 |
| 2FA089055677A69C67B9E4F816A8D9EF | 2023-12-01 17:24:33.558938 | 172.16.100.137 |    2188 |              | select * from OB_PLAN_TEST1 where DDID='298225.370594538611501792855979394021071' and  CITY=300 |         1 |         7472 |
| 2FA089055677A69C67B9E4F816A8D9EF | 2023-12-01 17:24:40.374061 | 172.16.100.137 |    2188 |              | select * from OB_PLAN_TEST1 where DDID='269826.523111291697766623468575040129842' and  CITY=400 |         1 |       167090 |
+----------------------------------+----------------------------+----------------+---------+--------------+-------------------------------------------------------------------------------------------------+-----------+--------------+
3 rows in set (0.02 sec)

--检查可以发现计划是对的,后来sql没有复用差的计划
MySQL [oceanbase]>  select plan_id, operator, name, rows, cost from gv$ob_plan_cache_plan_explain where tenant_id=1002 and SVR_IP='172.16.100.137' and SVR_PORT=2882 and plan_id=2185;
+---------+-------------------------+---------------+--------+---------+
| plan_id | operator                | name          | rows   | cost    |
+---------+-------------------------+---------------+--------+---------+
|    2185 | PHY_PX_FIFO_COORD       | NULL          | 200002 | 1140163 |
|    2185 |  PHY_PX_REDUCE_TRANSMIT | NULL          | 200002 |  797398 |
|    2185 |   PHY_GRANULE_ITERATOR  | NULL          | 200002 |   26292 |
|    2185 |    PHY_TABLE_SCAN       | OB_PLAN_TEST1 | 200002 |   26292 |
+---------+-------------------------+---------------+--------+---------+
4 rows in set (0.01 sec)

MySQL [oceanbase]>  select plan_id, operator, name, rows, cost from gv$ob_plan_cache_plan_explain where tenant_id=1002 and SVR_IP='172.16.100.137' and SVR_PORT=2882 and plan_id=2188;
+---------+----------------+----------------------------------+------+------+
| plan_id | operator       | name                             | rows | cost |
+---------+----------------+----------------------------------+------+------+
|    2188 | PHY_TABLE_SCAN | OB_PLAN_TEST1(IDX_OB_PLAN_TEST1) |    1 |    6 |
+---------+----------------+----------------------------------+------+------+
1 row in set (0.00 sec)

结论:

1.对于这种因为复用计划导致的sql执行效率降低的情况,把plan cache中该sql的计划刷掉,重新解析下就可以。

2.4.0之后有了curso_sharing参数,可以根据业务情况决定是否使用(能否接受该类sql的硬解析),也可以解决相应的情况。

3.绑定outline固定计划,这个当然也会有一定负面影响,比如大账号走回表的代价是否可以接受。tip:对于一些多表关联的hint绑定要经过一些判断,比如a表left join b表,正常a数据量少,b数据量大,绑定走索引和nest loop的hint没有问题,但是如果a表传入大账号的时候,a表数据量大了之后效率会急剧下降,这时候可以只绑定索引的hint,让优化器自己去选择连接方式。

可以从版本的递进和发展我们也能看到ob在不断的完善,虽然仍然还存在问题,需要不断的打磨、进步,但是数据库的发展不是一蹴而就的,ob的快速迭代优化,是肉眼可以看得见的,希望ob越来越成熟、稳定吧。

行之所向,莫问远方。

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论