--- title: SQL 执行占用内存过大-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于 SQL 执行占用内存过大相关的常见问题和使用技巧，帮助您快速解决 SQL 执行占用内存过大的难题。 --- 切换语言 - 简体中文 - English 划线反馈 # SQL 执行占用内存过大 更新时间：2025-05-07 12:31 适用版本： V2.1.x、V2.2.x、V3.1.x、V3.2.x 内容类型：Troubleshoot ## 问题描述 在 OceanBase 数据库中执行以下 SQL 语句。 ```shell select a.* from jxzs.vw_ac43 a where 1=1 and exists(select 1 from jxzs.ab07 k where k.aaz061=a.aaz061 and k.aaz625='3000000222067265'); ``` 在执行期间，发现 SQL_EXEC_CTX_ID 占用的内存约为 300 GB。 SQL 语句的执行计划如下。 ```shell |ID|OPERATOR |NAME |EST. ROWS|COST | --------------------------------------------------------------------------------------- |0 |HASH RIGHT SEMI JOIN | |2 |4129906795| |1 | PX COORDINATOR | |1 |46 | |2 | EXCHANGE OUT DISTR |:EX10000 |1 |46 | |3 | SUBPLAN SCAN |VIEW7 |1 |46 | |4 | PX PARTITION ITERATOR | |1 |46 | |5 | TABLE SCAN |K(IDX_AB07_AAZ625) |1 |46 | |6 | PX COORDINATOR | |48779191 |4109451647| |7 | EXCHANGE OUT DISTR |:EX20000 |48779191 |3897938817| |8 | SUBPLAN SCAN |VW_AC43 |48779191 |3897938817| |9 | PX PARTITION ITERATOR | |48779191 |3897205679| |10| UNION ALL | |48779191 |3897205679| |11| HASH JOIN | |10036448 |272316630 | |12| TABLE SCAN |B |10036448 |28245596 | |13| TABLE SCAN |A |29056783 |213880438 | |14| NESTED-LOOP ANTI JOIN | |0 |2216907709| |15| NESTED-LOOP JOIN | |0 |2216907709| |16| HASH RIGHT ANTI JOIN| |0 |2216907709| |17| SUBPLAN SCAN |VIEW1 |778695443|737634818 | |18| TABLE SCAN |B(IDX_AC08_AAC001_M1) |778695443|725931238 | |19| TABLE SCAN |A |519130295|725931238 | |20| TABLE GET |B |1 |36 | |21| SUBPLAN SCAN |VIEW2 |1 |92 | |22| TABLE SCAN |B(IDX_AC43_AAZ686) |1 |92 | |23| TABLE SCAN |A |38742743 |213880388 | |24| NESTED-LOOP ANTI JOIN | |0 |783830823 | |25| HASH RIGHT ANTI JOIN | |1594755 |709046552 | |26| SUBPLAN SCAN |VIEW4 |203399400|219801120 | |27| TABLE SCAN |B |203399400|216744082 | |28| TABLE SCAN |A |204994155|245704890 | |29| SUBPLAN SCAN |VIEW3 |1 |46 | |30| TABLE SCAN |B(IDX_AC96_AAC001_AAZ686_AAE792)|1 |46 | |31| NESTED-LOOP ANTI JOIN | |0 |409536995 | |32| HASH RIGHT ANTI JOIN | |0 |409536995 | |33| SUBPLAN SCAN |VIEW5 |143851091|136265820 | |34| TABLE SCAN |B |143851091|134103777 | |35| TABLE SCAN |A |95900727 |134103777 | |36| SUBPLAN SCAN |VIEW6 |1 |92 | |37| TABLE SCAN |B(IDX_AC43_AAZ686) |1 |92 | ``` 通过执行计划可知 SQL 语句满足三个触发条件。 - 命中索引。 - 抽出的 range 个数大于 2。 - 需要 rescan 的场景，例如存在 NESTED-LOOP JOIN。 Memleak 信息如下所示。 ```shell oceanbase::common::lbt(char*, int) oceanbase::common::ObMemLeakChecker::on_alloc(oceanbase::lib::AObject&) oceanbase::common::ObAllocator::alloc(long, oceanbase::lib::ObMemAttr const&) oceanbase::common::PageArena::extend_page(long) at 0_cxx.cxx:? oceanbase::common::PageArena::alloc_aligned(long, long) at 0_cxx.cxx:? oceanbase::common::ObSEArrayImpl::internal_malloc_(long) oceanbase::common::ObSEArrayImpl::reserve(long) oceanbase::sql::ObQueryRange::store_range(oceanbase::common::ObNewRange*, bool, oceanbase::sql::ObQueryRange::ObSearchState&, oceanbase::common::ObSEArray&, oceanbase::common::ObSEArray&) oceanbase::sql::ObQueryRange::generate_cur_range(oceanbase::sql::ObQueryRange::ObSearchState&, long, bool, oceanbase::common::ObSEArray&, oceanbase::common::ObSEArray&) oceanbase::sql::ObQueryRange::and_first_in_key(oceanbase::sql::ObQueryRange::ObSearchState&, oceanbase::sql::ObKeyPart*, oceanbase::common::ObSEArray&, oceanbase::common::ObSEArray&, oceanbase::common::ObDataTypeCastParams const&) oceanbase::sql::ObQueryRange::and_first_search(oceanbase::sql::ObQueryRange::ObSearchState&, oceanbase::sql::ObKeyPart*, oceanbase::common::ObSEArray&, oceanbase::common::ObSEArray&, oceanbase::common::ObDataTypeCastParams const&) oceanbase::sql::ObQueryRange::and_first_search(oceanbase::sql::ObQueryRange::ObSearchState&, oceanbase::sql::ObKeyPart*, oceanbase::common::ObSEArray&, oceanbase::common::ObSEArray&, oceanbase::common::ObDataTypeCastParams const&) oceanbase::sql::ObQueryRange::and_first_search(oceanbase::sql::ObQueryRange::ObSearchState&, oceanbase::sql::ObKeyPart*, oceanbase::common::ObSEArray&, oceanbase::common::ObSEArray&, oceanbase::common::ObDataTypeCastParams const&) oceanbase::sql::ObQueryRange::get_tablet_ranges(oceanbase::common::ObSEArray&, oceanbase::common::ObSEArray&, oceanbase::common::ObDataTypeCastParams const&) oceanbase::sql::ObSQLUtils::extract_pre_query_range(oceanbase::sql::ObQueryRange const&, oceanbase::common::ObIAllocator&, oceanbase::common::Ob2DArray > const&, oceanbase::common::ObSEArray&, oceanbase::common::ObSEArray, oceanbase::common::ObDataTypeCastParams const&) oceanbase::sql::ObTableScanOp::prepare_single_scan_range() at ??:? oceanbase::sql::ObTableScanOp::local_iter_rescan() oceanbase::sql::ObTableScanOp::rescan() oceanbase::sql::ObOperator::rescan() oceanbase::sql::ObNestedLoopJoinOp::rescan_right_operator() oceanbase::sql::ObNestedLoopJoinOp::join_row_with_semi_join() oceanbase::sql::ObNestedLoopJoinOp::inner_get_next_row() oceanbase::sql::ObOperator::get_next_row() oceanbase::sql::ObMergeUnionOp::all_get_next_row() oceanbase::sql::ObMergeUnionOp::inner_get_next_row() oceanbase::sql::ObOperator::get_next_row() oceanbase::sql::ObGranuleIteratorOp::try_get_rows(long) oceanbase::sql::ObOperator::get_next_row() oceanbase::sql::ObSubPlanScanOp::inner_get_next_row() oceanbase::sql::ObOperator::get_next_row() oceanbase::sql::ObPxTransmitOp::inner_get_next_row() oceanbase::sql::ObOperator::get_next_row() oceanbase::sql::ObPxTransmitOp::next_row() oceanbase::sql::ObPxTransmitOp::send_rows_one_by_one(oceanbase::sql::ObSliceIdxCalc&) oceanbase::sql::ObPxReduceTransmitOp::do_transmit() oceanbase::sql::ObPxTaskProcess::execute(oceanbase::sql::ObOpSpec&) oceanbase::sql::ObPxTaskProcess::do_process() oceanbase::sql::ObPxTaskProcess::process() oceanbase::sql::ObPxLocalWorker::run(oceanbase::sql::ObPxRpcInitTaskArgs&) oceanbase::sql::ObPxSubCoord::dispatch_task_to_local_thread(oceanbase::sql::ObPxRpcInitSqcArgs&, oceanbase::sql::ObSqcCtx&, oceanbase::sql::ObPxSqcMeta&) oceanbase::sql::ObInitFastSqcP::startup_normal_sqc(oceanbase::sql::ObPxSqcHandler&) oceanbase::sql::ObInitFastSqcP::process() oceanbase::obrpc::ObRpcProcessorBase::run() oceanbase::omt::ObWorkerProcessor::process_one(oceanbase::rpc::ObRequest&) oceanbase::omt::ObWorkerProcessor::process(oceanbase::rpc::ObRequest&) oceanbase::omt::ObThWorker::process_request(oceanbase::rpc::ObRequest&) oceanbase::omt::ObThWorker::worker(long&, long&, int&) non-virtual thunk to oceanbase::omt::ObThWorker::run(long) at ??:? std::_Function_handler::_M_invoke(std::_Any_data const&) eeentry(boost::context::detail::transfer_t) finish at object_set.cpp:? ``` ## 适用版本 OceanBase 数据库 V2.2.77 版本、V3.1.2 BP11 (oceanbase-3.1.2-111000052023010412) 之前版本、V3.2.3 BP9 (oceanbase-3.2.3.3-109000182023071410) 之前版本、V3.2.4 BP4 Hotfix5 (oceanbase-3.2.4.4-104050012023073116) 之前版本。 ## 问题原因 OceanBase 数据库 Bug 引起的内存动态泄漏。 ## 解决方法 ### 解决方法 升级到问题已修复的版本。 目前已修复的版本包括 OceanBase 数据库企业版 V3.1.2 BP11 (oceanbase-3.1.2-111000052023010412) 及后续版本、V3.2.3 BP9 (oceanbase-3.2.3.3-109000182023071410) 及之后版本、V3.2.4 BP4 Hotfix5 (oceanbase-3.2.4.4-104050012023073116) 及之后版本。 ### 规避方法 添加 hint，走主表：在 SQL 语句中添加 hint，强制查询走主表，可以减少 SQL_EXEC_CTX_ID 的内存占用。 ### 应急方法 重启节点。 Previous [SQL 中存在大量 union 语句在生成执行计划时占用内存过大](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000234601) Next [分布式计划执行时中间结果占用内存过大](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000426769) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线