从OB3.1.5迁移到4.3.5后查询慢

[AntTech_UCVIYQ]

我们公司最近从OB3.1.5迁移到了4.3.5，用的是OMS做的迁移，迁移完毕过了两天后，同一个查询在2个OB速度差异巨大，能麻烦给看看怎么优化新版本OB吗，新版本OB用的是HTAP。
老的OB执行计划：

|ID|OPERATOR |NAME |EST. ROWS|COST |

|0 |LIMIT | |1000 |14372|
|1 | PX COORDINATOR MERGE SORT | |1000 |14234|
|2 | EXCHANGE OUT DISTR |:EX10000 |1000 |11973|
|3 | LIMIT | |1000 |11973|
|4 | TOP-N SORT | |1000 |11835|
|5 | PX PARTITION ITERATOR | |1000 |1402 |
|6 | TABLE SCAN |gic_emission_mobile|1000 |1402 |

Outputs & filters:

0 - output([gic_emission_mobile.id(0x7f6b2d399dd0)], [gic_emission_mobile.mobile_sha256(0x7f6b2d398180)], [gic_emission_mobile.event_id(0x7f6b2d39c910)], [gic_emission_mobile.remain_emission(0x7f6b2d39be10)], [gic_emission_mobile.region(0x7f6b2d39aee0)], [gic_emission_mobile.behavior_id(0x7f6b2d39d530)], [gic_emission_mobile.event_time(0x7f6b2d39dab0)]), filter(nil), limit(1000), offset(nil)
1 - output([gic_emission_mobile.id(0x7f6b2d399dd0)], [gic_emission_mobile.mobile_sha256(0x7f6b2d398180)], [gic_emission_mobile.event_id(0x7f6b2d39c910)], [gic_emission_mobile.remain_emission(0x7f6b2d39be10)], [gic_emission_mobile.region(0x7f6b2d39aee0)], [gic_emission_mobile.behavior_id(0x7f6b2d39d530)], [gic_emission_mobile.event_time(0x7f6b2d39dab0)]), filter(nil), sort_keys([gic_emission_mobile.id(0x7f6b2d399dd0), ASC])
2 - output([gic_emission_mobile.id(0x7f6b2d399dd0)], [gic_emission_mobile.mobile_sha256(0x7f6b2d398180)], [gic_emission_mobile.event_id(0x7f6b2d39c910)], [gic_emission_mobile.remain_emission(0x7f6b2d39be10)], [gic_emission_mobile.region(0x7f6b2d39aee0)], [gic_emission_mobile.behavior_id(0x7f6b2d39d530)], [gic_emission_mobile.event_time(0x7f6b2d39dab0)]), filter(nil), dop=1
3 - output([gic_emission_mobile.id(0x7f6b2d399dd0)], [gic_emission_mobile.mobile_sha256(0x7f6b2d398180)], [gic_emission_mobile.event_id(0x7f6b2d39c910)], [gic_emission_mobile.remain_emission(0x7f6b2d39be10)], [gic_emission_mobile.region(0x7f6b2d39aee0)], [gic_emission_mobile.behavior_id(0x7f6b2d39d530)], [gic_emission_mobile.event_time(0x7f6b2d39dab0)]), filter(nil), limit(1000), offset(nil)
4 - output([gic_emission_mobile.id(0x7f6b2d399dd0)], [gic_emission_mobile.mobile_sha256(0x7f6b2d398180)], [gic_emission_mobile.event_id(0x7f6b2d39c910)], [gic_emission_mobile.remain_emission(0x7f6b2d39be10)], [gic_emission_mobile.region(0x7f6b2d39aee0)], [gic_emission_mobile.behavior_id(0x7f6b2d39d530)], [gic_emission_mobile.event_time(0x7f6b2d39dab0)]), filter(nil), sort_keys([gic_emission_mobile.id(0x7f6b2d399dd0), ASC]), topn(1000), local merge sort
5 - output([gic_emission_mobile.mobile_sha256(0x7f6b2d398180)], [gic_emission_mobile.id(0x7f6b2d399dd0)], [gic_emission_mobile.region(0x7f6b2d39aee0)], [gic_emission_mobile.remain_emission(0x7f6b2d39be10)], [gic_emission_mobile.event_id(0x7f6b2d39c910)], [gic_emission_mobile.behavior_id(0x7f6b2d39d530)], [gic_emission_mobile.event_time(0x7f6b2d39dab0)]), filter(nil),
force partition granule, asc.
6 - output([gic_emission_mobile.mobile_sha256(0x7f6b2d398180)], [gic_emission_mobile.id(0x7f6b2d399dd0)], [gic_emission_mobile.region(0x7f6b2d39aee0)], [gic_emission_mobile.remain_emission(0x7f6b2d39be10)], [gic_emission_mobile.event_id(0x7f6b2d39c910)], [gic_emission_mobile.behavior_id(0x7f6b2d39d530)], [gic_emission_mobile.event_time(0x7f6b2d39dab0)]), filter([(T_OP_LIKE, gic_emission_mobile.region(0x7f6b2d39aee0), ‘11%’, ‘\’)(0x7f6b2d39a090)], [gic_emission_mobile.tenant_id(0x7f6b87723630) = 14(0x7f6b2d398970)], [gic_emission_mobile.remain_emission(0x7f6b2d39be10) > ?(0x7f6b2d39b760)]),
access([gic_emission_mobile.tenant_id(0x7f6b87723630)], [gic_emission_mobile.mobile_sha256(0x7f6b2d398180)], [gic_emission_mobile.id(0x7f6b2d399dd0)], [gic_emission_mobile.region(0x7f6b2d39aee0)], [gic_emission_mobile.remain_emission(0x7f6b2d39be10)], [gic_emission_mobile.event_id(0x7f6b2d39c910)], [gic_emission_mobile.behavior_id(0x7f6b2d39d530)], [gic_emission_mobile.event_time(0x7f6b2d39dab0)]), partitions(p5sp[0-31]),
limit(1000), offset(nil),
is_index_back=false, filter_before_indexback[false,false,false],
range_key([gic_emission_mobile.id(0x7f6b2d399dd0)], [gic_emission_mobile.tenant_id(0x7f6b87723630)], [gic_emission_mobile.mobile_sha256(0x7f6b2d398180)]), range(5323472,MAX,MAX ; MAX,14,MAX),
range_cond([gic_emission_mobile.id(0x7f6b2d399dd0) > 5323472(0x7f6b2d399720)])

Used Hint:

/*+
*/

Outline Data:

/*+
BEGIN_OUTLINE_DATA
FULL(@“SEL$1” “gic_trade.gic_emission_mobile”@“SEL$1”)
END_OUTLINE_DATA
*/

Plan Type:

DISTRIBUTED

Optimization Info:

gic_emission_mobile:table_rows:1958788910, physical_range_rows:1968168864, logical_range_rows:1968168864, index_back_rows:0, output_rows:681189055, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_tenant_id_region_remain_emission_event_time,gic_emission_mobile], pruned_index_name[uk_tenant_id_mobile_event_id,idx_mobile_sha256,idx_tenant_id_enent_time,idx_event_month,idx_tenant_id_source_name], estimation info[table_id:1100611139453952, (table_type:1, version:0-1744567210372305-1744567210372305, logical_rc:61477104, physical_rc:61477104), (table_type:7, version:1744567201254857-1744567210372305-1744567232798522, logical_rc:0, physical_rc:0), (table_type:7, version:1744567232798522-1744607482491064-1744607482491064, logical_rc:0, physical_rc:0), (table_type:7, version:1744607482491064-1744608326700710-1744608326700710, logical_rc:28173, physical_rc:28173), (table_type:5, version:1744607482491064-1744608326700710-1744608326700710, logical_rc:0, physical_rc:0), (table_type:0, version:1744608326700710-1744608326700710-9223372036854775807, logical_rc:0, physical_rc:0)]
Parameters

{obj:{“DECIMAL”:“0”}, accuracy:{length:-1, precision:1, scale:0}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“DECIMAL”, collation:“binary”, coercibility:“NUMERIC”}}

新版本OB执行计划：

|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|

|0 |LIMIT | |1000 |28428 |
|1 |└─PX COORDINATOR MERGE SORT | |1000 |28425 |
|2 | └─EXCHANGE OUT DISTR |:EX10000 |1000 |26381 |
|3 | └─TOP-N SORT | |1000 |21780 |
|4 | └─PX PARTITION ITERATOR | |32000 |5190 |
|5 | └─COLUMN TABLE RANGE SCAN|gic_emission_mobile|32000 |5190 |

Outputs & filters:

0 - output([gic_emission_mobile.id(0x7f0414c2e7e0)], [gic_emission_mobile.mobile_sha256(0x7f0414c27800)], [gic_emission_mobile.event_id(0x7f0414c2fc00)],
[gic_emission_mobile.remain_emission(0x7f0414c2c050)], [gic_emission_mobile.region(0x7f0414c2a750)], [gic_emission_mobile.behavior_id(0x7f0414c30dd0)],
[gic_emission_mobile.event_time(0x7f0414c316a0)]), filter(nil), rowset=256
limit(1000(0x7f0414c31f80)), offset(nil)
1 - output([gic_emission_mobile.id(0x7f0414c2e7e0)], [gic_emission_mobile.mobile_sha256(0x7f0414c27800)], [gic_emission_mobile.event_id(0x7f0414c2fc00)],
[gic_emission_mobile.remain_emission(0x7f0414c2c050)], [gic_emission_mobile.region(0x7f0414c2a750)], [gic_emission_mobile.behavior_id(0x7f0414c30dd0)],
[gic_emission_mobile.event_time(0x7f0414c316a0)]), filter(nil), rowset=256
sort_keys([gic_emission_mobile.id(0x7f0414c2e7e0), ASC])
2 - output([gic_emission_mobile.id(0x7f0414c2e7e0)], [gic_emission_mobile.mobile_sha256(0x7f0414c27800)], [gic_emission_mobile.event_id(0x7f0414c2fc00)],
[gic_emission_mobile.remain_emission(0x7f0414c2c050)], [gic_emission_mobile.region(0x7f0414c2a750)], [gic_emission_mobile.behavior_id(0x7f0414c30dd0)],
[gic_emission_mobile.event_time(0x7f0414c316a0)]), filter(nil), rowset=256
dop=1
3 - output([gic_emission_mobile.id(0x7f0414c2e7e0)], [gic_emission_mobile.mobile_sha256(0x7f0414c27800)], [gic_emission_mobile.event_id(0x7f0414c2fc00)],
[gic_emission_mobile.remain_emission(0x7f0414c2c050)], [gic_emission_mobile.region(0x7f0414c2a750)], [gic_emission_mobile.behavior_id(0x7f0414c30dd0)],
[gic_emission_mobile.event_time(0x7f0414c316a0)]), filter(nil), rowset=256
sort_keys([gic_emission_mobile.id(0x7f0414c2e7e0), ASC]), topn(1000(0x7f0414c31f80)), local merge sort
4 - output([gic_emission_mobile.id(0x7f0414c2e7e0)], [gic_emission_mobile.mobile_sha256(0x7f0414c27800)], [gic_emission_mobile.region(0x7f0414c2a750)],
[gic_emission_mobile.remain_emission(0x7f0414c2c050)], [gic_emission_mobile.event_id(0x7f0414c2fc00)], [gic_emission_mobile.behavior_id(0x7f0414c30dd0)],
[gic_emission_mobile.event_time(0x7f0414c316a0)]), filter(nil), rowset=256
force partition granule
5 - output([gic_emission_mobile.id(0x7f0414c2e7e0)], [gic_emission_mobile.mobile_sha256(0x7f0414c27800)], [gic_emission_mobile.region(0x7f0414c2a750)],
[gic_emission_mobile.remain_emission(0x7f0414c2c050)], [gic_emission_mobile.event_id(0x7f0414c2fc00)], [gic_emission_mobile.behavior_id(0x7f0414c30dd0)],
[gic_emission_mobile.event_time(0x7f0414c316a0)]), filter([(T_OP_LIKE, gic_emission_mobile.region(0x7f0414c2a750), ‘11%’(0x7f0414c29dd0), ‘\’)(0x7f0414c290d0)],
[gic_emission_mobile.remain_emission(0x7f0414c2c050) > cast(0(0x7f0414c2b2f0), DECIMAL_INT(20, 2))(0x7f0414c2c6f0)(0x7f0414c2b780)], [gic_emission_mobile.tenant_id(0x7f0414c25b20)
= 14(0x7f0414c28100)(0x7f0414c28590)], [TOPN_FILTER(gic_emission_mobile.id(0x7f0414c2e7e0))(0x7f12f616ed50)]), rowset=256
access([gic_emission_mobile.id(0x7f0414c2e7e0)], [gic_emission_mobile.tenant_id(0x7f0414c25b20)], [gic_emission_mobile.mobile_sha256(0x7f0414c27800)],
[gic_emission_mobile.region(0x7f0414c2a750)], [gic_emission_mobile.remain_emission(0x7f0414c2c050)], [gic_emission_mobile.event_id(0x7f0414c2fc00)], [gic_emission_mobile.behavior_id(0x7f0414c30dd0)],
[gic_emission_mobile.event_time(0x7f0414c316a0)]), partitions(p5sp[0-31])
limit(1000(0x7f0414c31f80)), offset(nil), is_index_back=false, is_global_index=false, filter_before_indexback[false,false,false,false],
range_key([gic_emission_mobile.id(0x7f0414c2e7e0)], [gic_emission_mobile.tenant_id(0x7f0414c25b20)], [gic_emission_mobile.mobile_sha256(0x7f0414c27800)]),
range(5323472,MAX,MAX ; MAX,MAX,MAX),
range_cond([gic_emission_mobile.id(0x7f0414c2e7e0) > 5323472(0x7f12f617c8c0)(0x7f12f617bea0)])
Used Hint:

/*+

*/
Qb name trace:

stmt_id:0, stmt_type:T_EXPLAIN
stmt_id:1, SEL$1
Outline Data:

/*+
BEGIN_OUTLINE_DATA
FULL(@“SEL$1” “gic_trade”.“gic_emission_mobile”@“SEL$1”)
USE_COLUMN_TABLE(@“SEL$1” “gic_trade”.“gic_emission_mobile”@“SEL$1”)
OPTIMIZER_FEATURES_ENABLE(‘4.3.5.0’)
END_OUTLINE_DATA
*/
Optimization Info:

gic_emission_mobile:
table_rows:1876976959
physical_range_rows:1959934710
logical_range_rows:1959934710
index_back_rows:0
output_rows:1850555618
table_dop:1
dop_method:Table DOP
avaiable_index_name:[uk_tenant_id_mobile_event_id, idx_event_month, idx_mobile_sha256, idx_tenant_id_enent_time, idx_tenant_id_region_remain_emission_event_time, idx_tenant_id_source_name, gic_emission_mobile]
pruned_index_name:[uk_tenant_id_mobile_event_id, idx_event_month, idx_tenant_id_enent_time, idx_tenant_id_source_name]
stats info:[version=2025-04-08 22:15:04.667663, is_locked=0, is_expired=0]
dynamic sampling level:0
estimation method:[OPTIMIZER STATISTICS, STORAGE]
Plan Type:
DISTRIBUTED
Parameters:
:0 => 14
:1 => ‘11%’
:2 => 0
:3 => 5323472
:4 => 1000
Note:
Degree of Parallelisim is 1 because of table property

[辞霜]

新老集群的架构都是啥样
麻烦提供一下相关慢sql及执行计划

[AntTech_UCVIYQ]

第一个是老的拓扑图

第二个是新的拓扑图，OB是我们运维部署的，您需要什么参数，我找运维要

[淇铭]

1、查看某段时间内执行时间 TOP N 的请求
select /*+ parallel(15) */ sql_id, elapsed_time, trace_id, query_sql
from oceanbase.gv$ob_sql_audit
where tenant_id = 1
and IS_EXECUTOR_RPC = 0
and request_time > (time_to_usec(now()) - 10000000)
and request_time < time_to_usec(now())
order by elapsed_time desc
limit 10;

2、 可以根据trace_id信息
收集SQL性能问题信息

obdiag gather scene run --scene=observer.perf_sql --env “{db_connect=’-hxx -Pxx -uxx -pxx -Dxx’, trace_id=‘xx’}”
https://www.oceanbase.com/docs/common-obdiag-cn-1000000002488292

[AntTech_UCVIYQ]

您好，我执行您的第一个查询，返回值是空的。
下面这些是我从OCP中查询到得

table_rows:1876976959, physical_range_rows:1959934710, logical_range_rows:1959934710, index_back_rows:0, output_rows:1850555618, avaiable_index_name[uk_tenant_id_mobile_event_id,idx_event_month,idx_mobile_sha256,idx_tenant_id_enent_time,idx_tenant_id_region_remain_emission_event_time,idx_tenant_id_source_name,gic_emission_mobile], pruned_index_name[uk_tenant_id_mobile_event_id,idx_event_month,idx_tenant_id_enent_time,idx_tenant_id_source_name], estimation info[table_id:502633, (table_type:17, version:-1–1–1, logical_rc:61487747, physical_rc:61487747), (table_type:12, version:-1–1–1, logical_rc:6759, physical_rc:6759), (table_type:12, version:-1–1–1, logical_rc:21486, physical_rc:21486), (table_type:17, version:-1–1–1, logical_rc:61217291, physical_rc:61217291), (table_type:12, version:-1–1–1, logical_rc:6714, physical_rc:6714), (table_type:12, version:-1–1–1, logical_rc:21807, physical_rc:21807), (table_type:17, version:-1–1–1, logical_rc:60993103, physical_rc:60993103), (table_type:12, version:-1–1–1, logical_rc:6527, physical_rc:6527), (table_type:12, version:-1–1–1, logical_rc:21214, physical_rc:21214), (table_type:17, version:-1–1–1, logical_rc:61202140, physical_rc:61202140), (table_type:12, version:-1–1–1, logical_rc:6450, physical_rc:6450), (table_type:12, version:-1–1–1, logical_rc:21679, physical_rc:21679), (table_type:17, version:-1–1–1, logical_rc:61198388, physical_rc:61198388), (table_type:12, version:-1–1–1, logical_rc:6711, physical_rc:6711), (table_type:12, version:-1–1–1, logical_rc:21703, physical_rc:21703), (table_type:17, version:-1–1–1, logical_rc:61401722, physical_rc:61401722), (table_type:12, version:-1–1–1, logical_rc:6499, physical_rc:6499), (table_type:12, version:-1–1–1, logical_rc:21652, physical_rc:21652), (table_type:17, version:-1–1–1, logical_rc:61046223, physical_rc:61046223), (table_type:12, version:-1–1–1, logical_rc:6471, physical_rc:6471), (table_type:12, version:-1–1–1, logical_rc:21338, physical_rc:21338), (table_type:17, version:-1–1–1, logical_rc:61230144, physical_rc:61230144), (table_type:12, version:-1–1–1, logical_rc:6578, physical_rc:6578), (table_type:12, version:-1–1–1, logical_rc:21889, physical_rc:21889), (table_type:17, version:-1–1–1, logical_rc:60931660, physical_rc:60931660), (table_type:12, version:-1–1–1, logical_rc:6799, physical_rc:6799), (table_type:12, version:-1–1–1, logical_rc:21266, physical_rc:21266), (table_type:17, version:-1–1–1, logical_rc:61489464, physical_rc:61489464), (table_type:12, version:-1–1–1, logical_rc:6614, physical_rc:6614), (table_type:12, version:-1–1–1, logical_rc:21559, physical_rc:21559)]
上面的是新OCP查询出来的

下面则是老的OCP查询出来的

table_rows:1958788910, physical_range_rows:1968168864, logical_range_rows:1968168864, index_back_rows:0, output_rows:681189055, est_method:local_storage, avaiable_index_name[idx_tenant_id_region_remain_emission_event_time,gic_emission_mobile], pruned_index_name[uk_tenant_id_mobile_event_id,idx_mobile_sha256,idx_tenant_id_enent_time,idx_event_month,idx_tenant_id_source_name], estimation info[table_id:1100611139453952, (table_type:1, version:0-1744567210372305-1744567210372305, logical_rc:61477104, physical_rc:61477104), (table_type:7, version:1744567201189320-1744567210372305-1744567224196010, logical_rc:0, physical_rc:0), (table_type:7, version:1744567224196010-1744607482688522-1744607482688522, logical_rc:0, physical_rc:0), (table_type:7, version:1744607482688522-1744608326677465-1744608326677465, logical_rc:28173, physical_rc:28173), (table_type:5, version:1744607482688522-1744608326677465-1744608326677465, logical_rc:0, physical_rc:0), (table_type:0, version:1744608326677465-1744608326677465-9223372036854775807, logical_rc:0, physical_rc:0)]

其实我们换到4.3.5OB后查询慢的不止这一个SQL，不过感觉应该都是同一个问题。辛苦大佬在给看看吧

[辞霜]

建议先手动收集一下统计信息。
oms迁移后不会自动进行采集。

[淇铭]

从这里 也没有看出来有多慢 你是和原先对比 执行的时间变长了是么？
那你找一个执行的感觉慢的语句 查看一下trace_id 在用obdiag收集一下信息 具体看看
–根据时间和执行语句查询trace_id
select query_sql,svr_ip,TRACE_ID,client_ip,TENANT_NAME,user_name,DB_NAME,ELAPSED_TIME,RET_CODE,FROM_UNIXTIME(ROUND(REQUEST_TIME/1000/1000),’%Y-%m-%d %H:%i:%S’) from GV$OB_SQL_AUDIT
WHERE REQUEST_TIME>=‘2024-04-05 14:34:00’ and lower(query_sql) like ‘%select%’;

根据trace_id收集SQL性能问题信息

obdiag gather scene run --scene=observer.perf_sql --env “{db_connect=’-hxx -Pxx -uxx -pxx -Dxx’, trace_id=‘xx’}”
https://www.oceanbase.com/docs/common-obdiag-cn-1000000002488292

[AntTech_UCVIYQ]

好的，我执行一下您给的查询。
同一个SQL在老版OB查询耗时：0.074 s
新版耗时：15.64 s
不是一次查询，是多次查询几乎都是维持这个耗时

[淇铭]

@淇铭

select /*+ parallel(15) */ sql_id, elapsed_time, trace_id, query_sql
from oceanbase.gv$ob_sql_audit
where tenant_id = 1
and IS_EXECUTOR_RPC = 0
and request_time > (time_to_usec(now()) - 10000000)
and request_time < time_to_usec(now())
order by elapsed_time desc
limit 10;

那不可能这个语句 没有查询出来呀 这个是查询10s以上的执行语句

[AntTech_UCVIYQ]

大佬，确实没查询出来

是我们安装OB的时候少安装了什么吗
对了，上面说的查询变慢的表，我们迁移到新版本后，把表该成了行列共存的结构了

[淇铭]

show parameters like ‘enable_sql_audit’; 这个配置项查询一下

[AntTech_UCVIYQ]

[淇铭]

把租户的id改一下 改成你们的业务租户的id 你没有改 所以查不出来

[AntTech_UCVIYQ]

好嘞，已经找到对应慢SQL的trace_id了，运维在安装obdiag
大佬，我刚刚试了一下，我把SQL中的order by id asc去掉后速度就很快了，查询速度成了22.07 ms
SELECT xx,xx
FROM gic_emission_mobile WHERE tenant_id = 14 and region like ‘11%’
AND id > xx
ORDER BY id ASC LIMIT 1000
加上就很慢，但是业务需要必须要有这个

[淇铭]

show create table gic_emission_mobile\G; 查看一下表结构 id不是主键么？迁移过去以后 有手动收集一下统计信息么？

[AntTech_UCVIYQ]

我们的主键是：

三个字段组合成的，老版本OB也是这样，没有手动统计过信息，手动统计信息，这个怎么执行操作呀大佬

[淇铭]

先obdiag收集以后 在分析看看 什么原因导致的

[AntTech_UCVIYQ]

好嘞，那等运维安装后我再请教您，谢谢大佬了，再次感谢

[淇铭]

select * from oceanbase.DBA_OB_TABLE_STAT_STALE_INFO where DATABASE_NAME=? and
TABLE_NAME=‘gic_emission_mobile’ 根据库查一下这个信息

[AntTech_UCVIYQ]

查询结果.zip (31.0 KB)
因为查询结果有600多条，我打包了
还有就是运维刚刚帮我执行了一下obdiag，文件也打包发您看一下，谢谢了
obdiag_gather_pack_20250414180924.zip (182.2 KB)