背景：
因为前期遇到过执行计划突变导致的集群问题，我们做了一些监控及手段，我对于这些现象和原理简单验证和分享一下。
扩展分享：
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越来越成熟、稳定吧。
行之所向，莫问远方。
