慢sql绑定执行计划问题

OceanBase 技术问题
#1

【 使用环境 】生产环境
【 OB or 其他组件 】OB
【 使用版本 】4.3.5.5
【问题描述】手动绑定执行计划没用

4 个赞
#3

OceanBase 社区已接收您的帖子，正在跟进中。

#4

SQL 文本的绑定变量， 隐式类型转换这方面都检查看看

#5

慢 SQL 绑定执行计划通常是为了解决 SQL 查询性能问题，手动绑定执行计划在某些数据库系统中可能不直接支持，咨询一下官方。

#6

1、obclient [BAOFU_CBA]> SELECT * FROM oceanbase.DBA_OB_OUTLINES where sql_id = ‘5C18EAF2CED257F268570AC32BA38B44’;
±---------------------------±---------------------------±----------±------------±-----------±--------------±------------------±------------------±---------±---------------±------------±---------------------------------±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE_TIME | MODIFY_TIME | TENANT_ID | DATABASE_ID | OUTLINE_ID | DATABASE_NAME | OUTLINE_NAME | VISIBLE_SIGNATURE | SQL_TEXT | OUTLINE_TARGET | OUTLINE_SQL | SQL_ID | OUTLINE_CONTENT |
±---------------------------±---------------------------±----------±------------±-----------±--------------±------------------±------------------±---------±---------------±------------±---------------------------------±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 2026-05-27 11:55:01.768821 | 2026-05-27 11:55:01.768821 | 1006 | 500004 | 507444 | BAOFU_CBA | force_index_dba_3 | | | | | 5C18EAF2CED257F268570AC32BA38B44 | /+BEGIN_OUTLINE_DATA NO_PARALLEL LEADING(@“SEL$618C9326” ((“TAB”@“SEL$1” “TABD”@“SEL$1”) “TR”@“SEL$1”)) USE_NL(@“SEL$618C9326” “TR”@“SEL$1”) PQ_DISTRIBUTE(@“SEL$618C9326” “TR”@“SEL$1” BC2HOST NONE) USE_NL(@“SEL$618C9326” “TABD”@“SEL$1”) PQ_DISTRIBUTE(@“SEL$618C9326” “TABD”@“SEL$1” LOCAL LOCAL) INDEX(@“SEL$618C9326” “TAB”@“SEL$1” “UDX_ACCOUNT_ENTITY_NO”) INDEX(@“SEL$618C9326” “TABD”@“SEL$1” “IDX_ACCOUNT_NO”) INDEX(@“SEL$618C9326” “TR”@“SEL$1” “IDX_RECEIPT_NO”) SIMPLIFY_GROUP_BY(@“SEL$1”) REPLACE_CONST(@“SEL$EA493D33”) OUTER_TO_INNER(@“SEL$44092447”) PRED_DEDUCE(@“SEL$79C92EA1”) OPTIMIZER_FEATURES_ENABLE(‘4.3.5.2’) DYNAMIC_SAMPLING(1) END_OUTLINE_DATA/ |
±---------------------------±---------------------------±----------±------------±-----------±--------------±------------------±------------------±---------±---------------±------------±---------------------------------±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
obclient [BAOFU_CBA]> select sql_id, plan_id, OUTLINE_ID, OUTLINE_DATA, HINTS_INFO from oceanbase.gv$ob_plan_cache_plan_stat where sql_id = ‘5C18EAF2CED257F268570AC32BA38B44’\G
*************************** 1. row ***************************
sql_id: 5C18EAF2CED257F268570AC32BA38B44
plan_id: 175821
OUTLINE_ID: 507444
OUTLINE_DATA: /+BEGIN_OUTLINE_DATA LEADING(@“SEL$618C9326” ((“TAB”@“SEL$1” “TABD”@“SEL$1”) “TR”@“SEL$1”)) USE_NL(@“SEL$618C9326” “TR”@“SEL$1”) PQ_DISTRIBUTE(@“SEL$618C9326” “TR”@“SEL$1” BC2HOST NONE) USE_NL(@“SEL$618C9326” “TABD”@“SEL$1”) PQ_DISTRIBUTE(@“SEL$618C9326” “TABD”@“SEL$1” LOCAL LOCAL) INDEX(@“SEL$618C9326” “TAB”@“SEL$1” “UDX_ACCOUNT_ENTITY_NO”) INDEX(@“SEL$618C9326” “TABD”@“SEL$1” “IDX_ACCOUNT_NO”) INDEX(@“SEL$618C9326” “TR”@“SEL$1” “IDX_RECEIPT_NO”) SIMPLIFY_GROUP_BY(@“SEL$1”) REPLACE_CONST(@“SEL$EA493D33”) OUTER_TO_INNER(@“SEL$44092447”) PRED_DEDUCE(@“SEL$79C92EA1”) OPTIMIZER_FEATURES_ENABLE(‘4.3.5.2’) DYNAMIC_SAMPLING(1) END_OUTLINE_DATA/
HINTS_INFO: /+ LEADING(@“SEL$618C9326” ((“TAB”@“SEL$1” “TABD”@“SEL$1”) “TR”@“SEL$1”)) USE_NL(@“SEL$618C9326” “TR”@“SEL$1”) PQ_DISTRIBUTE(@“SEL$618C9326” “TR”@“SEL$1” BC2HOST NONE) USE_NL(@“SEL$618C9326” “TABD”@“SEL$1”) PQ_DISTRIBUTE(@“SEL$618C9326” “TABD”@“SEL$1” LOCAL LOCAL) INDEX(@“SEL$618C9326” “TAB”@“SEL$1” “UDX_ACCOUNT_ENTITY_NO”) INDEX(@“SEL$618C9326” “TABD”@“SEL$1” “IDX_ACCOUNT_NO”) INDEX(@“SEL$618C9326” “TR”@“SEL$1” “IDX_RECEIPT_NO”) SIMPLIFY_GROUP_BY(@“SEL$1”) REPLACE_CONST(@“SEL$EA493D33”) OUTER_TO_INNER(@“SEL$44092447”) PRED_DEDUCE(@“SEL$79C92EA1”) PARALLEL(1) OPTIMIZER_FEATURES_ENABLE(‘4.3.5.2’) DYNAMIC_SAMPLING(1) /
*************************** 2. row ***************************
sql_id: 5C18EAF2CED257F268570AC32BA38B44
plan_id: 176003
OUTLINE_ID: -1
OUTLINE_DATA: /+BEGIN_OUTLINE_DATA LEADING(@“SEL$618C9326” ((“TAB”@“SEL$1” “TABD”@“SEL$1”) “TR”@“SEL$1”)) USE_NL(@“SEL$618C9326” “TR”@“SEL$1”) PQ_DISTRIBUTE(@“SEL$618C9326” “TR”@“SEL$1” BC2HOST NONE) USE_HASH(@“SEL$618C9326” “TABD”@“SEL$1”) PQ_DISTRIBUTE(@“SEL$618C9326” “TABD”@“SEL$1” LOCAL LOCAL) INDEX(@“SEL$618C9326” “TAB”@“SEL$1” “UDX_ACCOUNT_ENTITY_NO”) INDEX(@“SEL$618C9326” “TABD”@“SEL$1” “IDX_CREATED_AT”) INDEX(@“SEL$618C9326” “TR”@“SEL$1” “IDX_RECEIPT_NO”) SIMPLIFY_GROUP_BY(@“SEL$1”) REPLACE_CONST(@“SEL$EA493D33”) OUTER_TO_INNER(@“SEL$44092447”) PRED_DEDUCE(@“SEL$79C92EA1”) OPTIMIZER_FEATURES_ENABLE(‘4.3.5.2’) DYNAMIC_SAMPLING(1) END_OUTLINE_DATA/
HINTS_INFO: /*+ PARALLEL(1) DYNAMIC_SAMPLING(1) */
2 rows in set (0.013 sec)

业务端执行的代码中加上了/*+ PARALLEL(1) DYNAMIC_SAMPLING(1) */
结论：业务代码中使用了hint和数据库端的outline有冲突导致的绑定有问题
2、使用这个NO_PARALLEL hint默认的并发是一 不是0

image
image753×143 14.2 KB

#7

这不应该吧