问题现象
生产环境执行固定 SQL 报错 4013，排查是 SQL 导致的 SqlExecContext 异常上涨导致的报错，报错 SQL 很长，有 13 个 union all，并且 select 有每个里面都有小 in，减少 union all 能规避。
| OceanBase 数据库版本 | 执行现象 |
|---|---|
| V3.2.3 BP11 Hotfix1（oceanbase-3.2.3.3-111010012024081311） | 执行正常 14s |
| V3.2.3 BP7 Hotfix3（oceanbase-3.2.3.3-107030052023031712） | 执行报错 4013 |
关键诊断信息
触发条件
SQL 中大量 union all 语句 13 以上 + 大量的 not in 条件。
事前巡检
SQL 的问题写法 union/union all 语句 + 大量 not in 条件的形式。
with now_contr as (
select
....
from
....
where
cl..... in (
'000U',
'000W',
'000X',
'000Y',
'000Z',
'0010',
'0011',
'0012',
'0013',
'0821'
)
and ....
),
last_contr as (
select
....
from
....
where
cl.pk_org in (
'000U',
'000W',
'000X',
'000Y',
'000Z',
'0010',
'0011',
'0012',
'0013',
'0821'
)
and ....
),
now_inner_move as (
select
....
from
....
where
.... in (
'000U',
'000W',
'000X',
'000Y',
'000Z',
'0010',
'0011',
'0012',
'0013',
'0821'
)
and im..... not in (
'000U',
'000W',
'000X',
'000Y',
'000Z',
'0010',
'0011',
'0012',
'0013',
'0821'
)
),
out_person as(
select
....
from
....
where
.... in (
'000U',
'000W',
'000X',
'000Y',
'000Z',
'0010',
'0011',
'0012',
'0013',
'0821'
)
union
select
....
from
....
where
....in (
'000U',
'000W',
'000X',
'000Y',
'000Z',
'0010',
'0011',
'0012',
'0013',
'0821'
)
union
select
....
from
....
where
.... in (
'000U',
'000W',
'000X',
'000Y',
'000Z',
'0010',
'0011',
'0012',
'0013',
'0821'
)
union
select
....
from
....
where
.... in (
'000U',
'000W',
'000X',
'000Y',
'000Z',
'0010',
'0011',
'0012',
'0013',
'0821'
)
union
select
....
from
....
where
.... in (
'000U',
'000W',
'000X',
'000Y',
'000Z',
'0010',
'0011',
'0012',
'0013',
'0821'
)
and .... not in (
'000U',
'000W',
'000X',
'000Y',
'000Z',
'0010',
'0011',
'0012',
'0013',
'0821'
)
union
select
....
from
....
where
.... in (
'000U',
'000W',
'000X',
'000Y',
'000Z',
'0010',
'0011',
'0012',
'0013',
'0821'
)
and ....not in (
'000U',
'000W',
'000X',
'000Y',
'000Z',
'0010',
'0011',
'0012',
'0013',
'0821'
)
)
....
事后诊断
通过过滤日志中的一个关键词，命令如下。
grep 'failed to extract query range' observer.log
如果返回值是 ret=-4013 并且 union/union all 语句 + 大量 not in 条件的形式命中此问题。
observer_48.log.20250319125232:[2025-03-19 12:44:17.727301] WARN alloc (ob_allocator_v2.cpp:51) [2946070][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=11] [dc=0] [OOPS] alloc failed reason: tenant memory has reached the upper limit(tenant_id: 1004, tenant_hold: 68719476736, tenant_limit: 68719476736, alloc_size: 2097152)
observer_48.log.20250319125232:[2025-03-19 12:44:17.727309] WARN alloc (ob_allocator_v2.cpp:56) [2946070][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=7] [dc=0] oops, alloc failed, tenant_id=1004, ctx_id=5, ctx_name=SQL_EXEC_CTX_ID, ctx_hold=61027123200, ctx_limit=9223372036854775807, tenant_hold=68719476736, tenant_limit=68719476736
observer_48.log.20250319125232:[2025-03-19 12:44:17.727342] WARN alloc_new_page (page_arena.h:331) [2946070][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=10] [dc=0] cannot allocate memory.sz=2096128, pages_=29086,total_=60967979008
observer_48.log.20250319125232:[2025-03-19 12:44:17.727350] WARN extend_page (page_arena.h:352) [2946070][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=8] [dc=0] extend_page sz =2096128 cannot alloc new page
observer_48.log.20250319125232:[2025-03-19 12:44:17.727357] ERROR [SQL.REWRITE] deep_copy_key_part_and_items (ob_query_range.cpp:3542) [2946070][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=5] [dc=0] alloc ObKeyPart failed(ret=-4013) BACKTRACE:0xf6dfb6f 0x52cc23f 0x530d7f4 0x52c81df 0x530d668 0x530d288 0xd03cabe 0xd03cdf8 0xd03eb4d 0xd03ddb2 0xd015bea 0xd0141a2 0xe0cf000 0xe0ce522 0xe0ddfce 0xe0df520 0xe0dfc48 0xe0ffc59 0xe0ff037 0xe0fd119 0xc144d99 0xc1abb5d 0xe32df79 0xe32d8b6 0xc2e8230 0xc2e72b2 0xe4da1e2 0x5238138 0x513bce5 0x50e5e81 0x50af61b 0x50ad8ef 0x50aba6e 0xbab3c61 0x50aac90 0xbab1711 0x50a68f6 0xbab1d17 0xf5bef13 0xf5bed6f 0xf85e03f
observer_48.log.20250319125232:[2025-03-19 12:44:17.727377] WARN [SQL.REWRITE] do_gt_and (ob_query_range.cpp:3600) [2946070][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=19] [dc=0] right copy key part and items failed(ret=-4013)
observer_48.log.20250319125232:[2025-03-19 12:44:17.727385] WARN [SQL.REWRITE] do_in_key_and (ob_query_range.cpp:3876) [2946070][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=6] [dc=0] failed to and in key(ret=-4013)
observer_48.log.20250319125232:[2025-03-19 12:44:17.727392] WARN [SQL.REWRITE] and_single_gt_head_graphs (ob_query_range.cpp:3715) [2946070][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=6] [dc=0] failed to do in key and next(ret=-4013)
observer_48.log.20250319125232:[2025-03-19 12:44:17.727399] WARN [SQL.REWRITE] and_range_graph (ob_query_range.cpp:4035) [2946070][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=5] [dc=0] And single general term head graphs failed(ret=-4013)
observer_48.log.20250319125232:[2025-03-19 12:44:17.727405] WARN [SQL.REWRITE] preliminary_extract_query_range (ob_query_range.cpp:382) [2946070][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=5] [dc=0] And query range failed(ret=-4013)
observer_48.log.20250319125232:[2025-03-19 12:44:17.727424] WARN [SQL.JO] extract_preliminary_query_range (ob_join_order.cpp:2880) [2946070][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=6] [dc=0] failed to preliminary extract query range(ret=-4013)
observer_48.log.20250319125232:[2025-03-19 12:44:38.219854] WARN [SQL.JO] get_query_range_info (ob_join_order.cpp:617) [2946070][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=10] [dc=0] failed to extract query range(ret=-4013, index_id=1103909674353920)
observer_48.log.20250319125232:[2025-03-19 12:44:38.219891] WARN [SQL.JO] fill_index_info_entry (ob_join_order.cpp:1828) [2946070][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=34] [dc=0] failed to get query range(ret=-4013)
observer_48.log.20250319125232:[2025-03-19 12:44:38.219918] WARN [SQL.JO] fill_index_info_cache (ob_join_order.cpp:1858) [2946070][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=8] [dc=0] fill_index_info_entry failed(ret=-4013)
observer_48.log.20250319125232:[2025-03-19 12:44:38.219931] WARN [SQL.JO] add_table (ob_join_order.cpp:1905) [2946070][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=11] [dc=0] failed to fill index info cache(ret=-4013)
observer_48.log.20250319125232:[2025-03-19 12:44:38.219964] WARN [SQL.JO] generate_access_paths (ob_join_order.cpp:4616) [2946070][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=6] [dc=0] failed to add table to join order(single)(ret=-4013)
问题原因
主要是 CTE 优化生成了复杂的索引条件，复杂的索引条件需要去抽取复杂条件的 Query Range，如果客户使用的 OBserver 版本没有IN优化会消耗大量内存，造成 SQL 执行期间内存膨胀。
问题的风险及影响
SQL 执行报错内存不足报错 -4013 No memory or reach tenant memory limit。
适用版本
OceanBase 数据库 V3.2.3 BP7(oceanbase-3.2.3.3-107000092023011911)、V3.2.3 BP8（oceanbase-3.2.3.3-108000062023041511）版本。
解决方法及规避方式
OceanBase 数据库 V3.2.3（oceanbase-3.2.3.0-20220418212020）~ BP7（oceanbase-3.2.3.3-107000092023011911）因为没
_xsolapi_generate_with_clause配置项，可以在相关的 sql select 后加/*+inline*/可以控制 CTE 抽取及展开能避免 CTE 抽取也能规避。OceanBase 数据库 V3.2.3 BP8（oceanbase-3.2.3.3-108000062023041511）~ BP10（oceanbase-3.2.3.3-110000092023091219）可以关闭 CTE 抽取优化避免，复杂的索引条件需要去抽取复杂条件的
Query Range，使用配置项ALTER SYSTEM SET "_xsolapi_generate_with_clause"=0;。OceanBase 数据库 V3.2.3 BP11（oceanbase-3.2.3.3-111000032024070822）及之后版本，默认不用加任何配置项或者 Hint 预期能解决此问题（检查
_enable_in_range_optimization是否打开）。
